How do I use my login rather that my machine name with Windows Authentication to a SQL Server under Web Setup?

Question

I've got a Web Setup project installing a web app. In a Custom Action, I'm executing a SQL script to build a database.

Simple, mostly MS sample code. Works fine.

private void ExecuteSql(string serverName, string dbName, string Sql)
{
    System.Data.SqlClient.SqlConnection masterConnection = new System.Data.SqlClient.SqlConnection();
    masterConnection.ConnectionString = "Server=" + serverName + "; Database=" + dbName + ";Integrated Security=SSPI";

    System.Data.SqlClient.SqlCommand Command = new System.Data.SqlClient.SqlCommand(Sql, masterConnection);
    Command.Connection.Open();
    Command.Connection.ChangeDatabase(dbName);
    try
    {
        Command.ExecuteNonQuery();
    }
    finally
    {
        Command.Connection.Close();
    }
}

The problem is that the user that is being sent over to the SQL Server is "DOMAIN\MACHINENAME$", like "AMBER\CHAOS$" rather than "AMBER\corwin".

If I add "AMBER\CHAOS$" as a login on the SQL Server and give the user sufficient rights, everything works fine. That's not possible in the final environment.

So, what do I need to do to make my web setup connect to SQL server as "AMBER\corwin" rather than "AMBER\CHAOS$"?

Answer 1

Simply take your connection string and make it:

masterConnection.ConnectionString = "Server=" + serverName + "; Database=" + dbName + ";Integrated Security=SSPI;UserId=" + userId + ";Password=" + password + ";";

Answer 2

You might try to mess around in the *.config file. You could try using these elements:

<system.web>   
  <authentication mode="Windows" />   
  <identity impersonate="true" /> 
</system.web>

Answer 3

Probably that's your website application pool username; Try add into your web.config file, inside /configuration/system.web:

<identity impersonate="true"/>

Answer 4

You have to tell the custom action to impersonate the user. How are you creating the MSI? With WiX, you would add: Impersonate="yes" to the element.

Answer 5

This is probably because your web site's application pool is running as Network Service instead of a windows user. Change this value to the account you want to connect to the database as (watch you application, this could change your app depending on the architecture) and add rights in SQL.