SQL Trusted Connection should run as user, not as system

[Oren_T]

SQL Trusted Connection runs with the permissions of the local machine, not those of the user. This isn't very useful when connecting to remote machines. Even if the user is a member of the Domain Administrators group no privileges will be granted by the trusted connection (e.g. to create a database) because it's running with the permissions of the machine account, not the user.

In http://www.advancedinstaller.com/forums ... php?t=1915 an Advanced Installer user complains about an error "Login failed for user 'DOMAIN\USER$" and wonders where the $ is coming from. The $ indicates that this is a machine account, which happens to be identical to the name of the user.

[gigi]

Hi,

When trusted connection is used the LogonUser property is used as "Username".

The $ indicates that this is a machine account, which happens to be identical to the name of the user.

This is not controlled by Advanced Installer, the property is translated by the Windows Installer. The property is translated this way when running on a remote server because the "Installation Type" for the package is "Per-machine only ...". I don't see how to avoid this, maybe hardcoding the username will solve the problem, but this cannot be done yet from the AI SQL connection interface. The next version (5.1) of AI will offer support for custom SQL connection string. Maybe this will give more flexibility in configuring your SQL connection.

Regards,
Gigi
______________
Gheorghe Rada
Advanced Installer Team
http://www.advancedinstaller.com

[Oren_T]

> When trusted connection is used the LogonUser property is used as "Username".

Yes, I realize that is the way it was supposed to work. But it doesn't.


* Step to reproduce:

1. Use a machine with a hostname different from the name of the logged in user.
2. Connect to a remote SQL server with an AI trusted connection.

* Expected result:

Either a successful connection or an error indicating that the user does not have the required permissions:
SQLDriverConnect: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'DOM\NAMEOFUSER'.

* Actual result:

SQLDriverConnect: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'DOM\NAMEOFMACHINE$'.

The message clearly indicates that AI is connecting with the identity of the machine, which typically does not have the required permissions.


You can't fix this by changing the connection string. When Trusted_Connection=yes; is specified the Uid= and Pwd= paramters are not required. If specified in the connection string they will be ignored. The identity of the current process is used instead.

When a process running as LOCAL SYSTEM makes a network connection it appears to the remote machine to be using the identity of the machine. Since msiexec runs as LOCAL SYSTEM the trusted connection to the remote database is actually attempted with the machine permissions instead of the permissions of the user running the installer.

Custom actions, for example, have an impersonation checkbox allowing them to execute with the identity the user. Trusted connections should do this, too. Custom connection strings would be a nice features, but they will not fix this.

[gigi]

Hi,

After further investigating the issues you mentioned we concluded that, in fact, when using a trusted SQL connection the AI database configuration tool (custom action) will attempt an ODBC connection using the identity of the local machine instead of the installed user.

In order to resolve this issue the custom action in question (created by AI) must have set the appropriate flags such that Windows Installer will impersonate the installing user.

We shall implement this functionality (configurable impersonation flag) in the next version of Advanced Installer. Meanwhile, as a work around you can edit the resulted MSI with ORCA (tool that comes with Windows Platform SDK) and set the flag manually.

In the "CustomAction" table locate the following rows:

Code: Select all

AI_SqlConfig   11265 sql.dll OnSqlConfig
AI_SqlRemove   11265 sql.dll OnSqlRemove
AI_SqlRollback 11521 sql.dll OnSqlRollback

And replace they "Type" column values as follows:

Code: Select all

AI_SqlConfig   9217 sql.dll OnSqlConfig
AI_SqlRemove   9217 sql.dll OnSqlRemove
AI_SqlRollback 9473 sql.dll OnSqlRollback

Regards,
Gigi

[Oren_T]

Thanks!

[rprussell]

Has this been fixed?

I ask because...

• (a) My sql server is logging invalid login attempts based on my machine name
  (b) There are no such entries in my .MSI file's "Custom Actions" table
  (c) Adding them to the table had no effect.

This is using a trial version of AI 6.8, just downloaded yesterday. I'm looking for an installer that will let me browse/specify a sql database, of course, and my researches led me here...

Thanks!
Rob

[Cosmin]

Hi Rob,

In the latest version of Advanced Installer you can use the "Impersonate installing user" option from the SQL Scripts page. You can access it by right-clicking on the "Database Server" entry.

I'm looking for an installer that will let me browse/specify a sql database

Please note that the User Guide contains some articles which may help you:
Using SQL Scripts
Browse for SQL servers

Regards,
Cosmin

[rprussell]

Hi, Cosmin -

Thanks for the quick reply!

My problem is arising on the SqlConnectionDialog and the SqlDatabaseDialog that I set up to follow it, so I am not using a predefined connection and script (as specified on the SQL Scripts page). Does the "Impersonate Installing User" option have a system-wide effect?

The SqlConnectionDialog works - browsing for servers populates the SqlServerComboBox (with attached property of SERVER_PROP) will give me the dropdown just fine. I also have set up a "test connection" button which will customize the SqlConnectionString property based on whether or not "Trusted Connection" is checked, and invoke "TestODBCConnection." Finally, the Next button will enable or disable based on this result.

(Ideally, I would like to have the "Database" dropdown populated by "ShowDatabases" on the same screen, after the selection from the Server dropdown, when the SERVER_PROP property changes. But, I'm new enough not to know how to get there - so there is a 2nd page, the SqlDatabaseDialog, which calls ShowDatabases upon entry, and this is where the issue comes up.)

If I have selected "Trusted Connection" on the previous page, I get an empty list of databases.

If I have entered a username/password on the previous page, I get a full list of databases.

I know the trusted connection should work, since I can see (and create and edit and delete and...) the databases using MS Sql Server Management Console.

Any thoughts?

Rob

[Cosmin]

Hi Rob,

Does the "Impersonate Installing User" option have a system-wide effect?

Please note that this option affects only the connections created in the "SQL Scripts" page, it doesn't affect anything else.

Ideally, I would like to have the "Database" dropdown populated by "ShowDatabases" on the same screen, after the selection from the Server dropdown, when the SERVER_PROP property changes.

Unfortunately this is not supported by Windows Installer. However, it could be done by using two identical dialogs which update the information in the databases combo. Please note that the User Guide contains the ComboBox and ListBox controls tutorial which may help you.

If I have selected "Trusted Connection" on the previous page, I get an empty list of databases.
If I have entered a username/password on the previous page, I get a full list of databases.

I'm not sure why you are encountering this behavior. Can you please send us the AIP (project) file you are using to support at advancedinstaller dot com so we can investigate it?

Regards,
Cosmin

[rprussell]

Please note that this option affects only the connections created in the "SQL Scripts" page, it doesn't affect anything else.

Okay, that's kind of what I figured. Thanks for the confirmation.

Unfortunately this is not supported by Windows Installer. However, it could be done by using two identical dialogs which update the information in the databases combo.

Oh, that is very sneaky. I love it!

Can you please send us the AIP (project) file you are using to support at advancedinstaller dot com so we can investigate it?

Done!

Thanks for all your help.

Rob