WiX issue with executing SqlScript at the remote DB

Question

Hi there,

Executing SqlScript at the remote DB causes an error:

Failed to connect to SQL database. (-2147467259 myDB1)

The SqlScript is the following:

<sql:SqlString 
Id='UpdateSomething1' 
SqlDb='myDB1' 
ExecuteOnInstall='yes' 
User='SQLUser'
ContinueOnError='no' 
ExecuteOnReinstall='no' 
ExecuteOnUninstall='no' 
Sequence='26'
SQL='[SqlString]'/>

where the Db is:

<sql:SqlDatabase 
Id='myDB1' 
Database='myDB1' 
Server='[DATABASE_SERVER]' 
CreateOnInstall='yes' 
DropOnInstall='no' 
DropOnUninstall='no' 
ContinueOnError='no'/>

and the user is:

<util:User 
Id="SQLUser" 
Name="myUserName1" 
Password="password1"/>

The problem does not occur with the local DB. We extracted more specific error message from the IP traffic (the actual error that the remote MSSQL server throws):

Can not open database "myDb1" requested by the login. The login failed. {remote machine name} Login failed for user {user name}

Thank you for any help and information.

Max

Answer 1

I would need more information to be sure but here are some general observations I've had over the years.

In MSI, you typically run deferred custom actions with no impersonation so that they run as Administrator to support managed/elevated installs where the invoking user doesn't have admin either because they really don't or because UAC hasn't elevated their process.

In InstallShield, and I'm sure WiX is similar, this typically causes a problem for remote database connections. If you have a dialog in the UI sequence to test the connection it will succeed ( when expected to ) because the interactive user has permissions to that database/instance. And if installing locally it will succeed because SYSTEM (typically) has permissions the database/instance. But when installing to a remote instance it will frequently fail because SYSTEM can't authenticate against SQL on the remote machine. Your mileage will improve if using sql authentication ( e.g. SA ).

Personally I have some practices that I follow. If I'm creating a single tier system, I restrict the database to (local). If I'm creating a 2 tier system, I create two installers: one for my database layer which I restrict to (local) and one for my application layer which I then reuse the sqllogin dialog to verify connectivity and write the values out to a web.config or app.config. This allows me to loosely couple the layers and service them independently of each other.

I hope this helps to understand the types of issues that can be encountered. I don't know your exact problem without seeing your environement.

Answer 2

The WiX custom actions are just using standard OLEDB commands to connect to the remote server. If the credentials work locally but not remotely then I'd start by ensuring the credentials are correct. There isn't anything different in the WiX custom actions between local and remote servers.