none
Running Sql scirpts through a custom actions in MSI (integrated security)

    Question

  • Hi All,

    In last few years my application set (msi) runs a custom action that execute a few sql script against sql server 2005 and 2008,

    I'm using the following connection string: Data Source=<SERVER_NAME>;Integrated Security=True;Pooling=True

    Everything is goin well until I install my software againts Sql Server 2012. I'm able to connect to the database, but get errors when "custom action" executes 

    scripts. for instance, when CREATE  DATABASE MyDbName I get: CREATE DATABASE permission denied in database 'master'. 

    Needless to say I manage to create this database from management studio with the same logged in user. I have also created a tester "console application" that execute the same command with the same connection string and it is also working.

    Need your help..... Thanks.


    Wednesday, February 13, 2013 11:49 AM

Answers

  • My user has been granted with sysadmin and public server roles. -> Which I'm certain its enough for creating database.

    Does ADO.NET for sql server 2008 behaves diffrenet for 2012??? Does it got to do with my sql drivers?

    Yes, sysadmin role membership is more that enough to create a database.  I don't believe there are any ADO.NET differences related to this issue. 

    I ran a quick custom action test on my Windows 8 box against SQL 2012 with the SELECT SYSTEM_USER query Erland suggested.  It returned "NT AUTHORITY\SYSTEM".  I suspect this account was not provisioned as a sysadmim role member during SQL Server installation on your system.  You can add the account as a sysadmin role member with the following script:

    CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS;
    ALTER SERVER ROLE sysadmin
    	ADD MEMBER [NT AUTHORITY\SYSTEM];


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by TalEran Thursday, February 14, 2013 3:11 PM
    Thursday, February 14, 2013 12:42 PM
    Moderator

All replies

  • Can you install with an elevated security context (execute MSI with "Run as Administrator")?


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Wednesday, February 13, 2013 12:08 PM
    Moderator
  • Hi Dan,

    Yes, I have already try that + My App setup requires  admin privileges to run it.

    Wednesday, February 13, 2013 12:24 PM
  • The "same logged in user"? Doesn't MSI installs runs through a service? In that case, the user would be the service account for msiexec. Can you add "SELECT SYSTEM_USER" to your script and log that somewhere?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, February 13, 2013 9:45 PM
  • Yes, I have already try that + My App setup requires  admin privileges to run it.

    Are you certain you are connecting from SSMS using the same account as when installing the application?  Perhaps different (higher privileged) credentials were saved in the SSMS server registration.  The error from the install indicates you are able connect but the security context doesn't have the needed CREATE DATABASE permissions.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Thursday, February 14, 2013 12:49 AM
    Moderator
  • Hi dan,

    Thanks for your replaying.

    My user has been granted with sysadmin and public server roles. -> Which I'm certain its enough for creating database.

    Does ADO.NET for sql server 2008 behaves diffrenet for 2012??? Does it got to do with my sql drivers?

    Thursday, February 14, 2013 8:43 AM
  • My user has been granted with sysadmin and public server roles. -> Which I'm certain its enough for creating database.

    Does ADO.NET for sql server 2008 behaves diffrenet for 2012??? Does it got to do with my sql drivers?

    Yes, sysadmin role membership is more that enough to create a database.  I don't believe there are any ADO.NET differences related to this issue. 

    I ran a quick custom action test on my Windows 8 box against SQL 2012 with the SELECT SYSTEM_USER query Erland suggested.  It returned "NT AUTHORITY\SYSTEM".  I suspect this account was not provisioned as a sysadmim role member during SQL Server installation on your system.  You can add the account as a sysadmin role member with the following script:

    CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS;
    ALTER SERVER ROLE sysadmin
    	ADD MEMBER [NT AUTHORITY\SYSTEM];


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by TalEran Thursday, February 14, 2013 3:11 PM
    Thursday, February 14, 2013 12:42 PM
    Moderator
  • Erland and Dan,

    Thank you very much,

    That helped a lot.

    MSI runs as 'NT AUTHORITY\SYSTEM' user and now I will need to impersonate the current logged in user.

    Thank you very much for your time and tips.

    Thursday, February 14, 2013 3:11 PM
  • Hi Dan,

    Is there a build in solution to make msi runs as the logged in user, without using impersonation in my code nor defining the microsoft installer service to run as ... any user

    ?????

    Thursday, February 14, 2013 4:03 PM
  • Is there a build in solution to make msi runs as the logged in user, without using impersonation in my code nor defining the microsoft installer service to run as ... any user

    That sounds like a question for a forum dedicated to the MSI installer.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, February 14, 2013 10:44 PM
  • Is there a build in solution to make msi runs as the logged in user, without using impersonation in my code nor defining the microsoft installer service to run as ... any user

    I agree with Erland that you might get a better answer in another forum.  I'm more than a little rusty with my knowledge of the Windows installer but maybe there is another option.  In a previous life, I created a custom action to create the SQL Server 2000 database objects needed by our commercial application.  We had accompanying installation document that described the prerequisite SQL Server configuration and the onus was on the DBA to take care of that before installation.  Maybe that's an option for you too.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Friday, February 15, 2013 1:35 PM
    Moderator
  • Hi Erland and Dan.

    Thanks for your tips & tricks, unfortunately, prerequisite database configuration is not an option. The only prerequiste my application demends is that the logged in user installing ,y software, will have sysadmin role in the target database, and I have to keep it like this.

    I will  though, try to ask in a MSI forum, and in the meanwhile, Ill try to impersonate the logged in user in my custom action code.

    Thanks again.

    Sunday, February 17, 2013 7:10 AM