I want to create a trusted connection between an iis web server (6.0 and 7.0) and a SQL Server 2005 running on a different machine.
The questions are: Can this be done without windows domain accounts?
How is this done via account mirroring?
The objective is to make connections from ASP.NET to SQL Server 2005 using connection strings without username and passwords. Right now we are using connection strings with username and password, and for security we are encrypting.
I am trying to use mirroring as described in the link below
http://idunno.org/articles/276.aspx
But this doesn't work; SQL Server always generates the error
Login failed for user ''. The user is not associated with a trusted SQL Server connection. [CLIENT: 10.31.11.53]
It appears that a null is being sent for user login credentials.
Here is what I have done:
WS1 = Windows 2003 Server machine with web server running IIS 6.0. User authentication is Forms authentication
DB1 = Windows 2003 server machine with SQL Server 2005
1. Create a local windows account on WS1 in the IIS_WPG group, "MirrorAccount"
2. In IIS 6.0 on WS1, create a new application pool, "AppPoolMirror". Set the run identity to the "MirrorAccount"
3. In IIS on WS1, set the web site to use application pool "AppPoolMirror"
4. Verify that the web site is using the "MirrorAccount" via the WindowsIdentity.GetCurrent().Name;
This returns "WS1\mirroraccount"
5. Use the following connection string in the web.config of the web site on WS1 as follows, which points to a database on a different machine
<add name="MyAspnetDBSqlServer" connectionString="Server=10.31.11.54\Develop;Initial Catalog=ctseft_ASPNETDB;Integrated Security=SSPI;" providerName="System.Data.SqlClient" />
6. On the Sql Server machine DB1 (10.31.11.54), create a local windows account "MirrorAccount". Then go into SQL Server Management studio and create a database server login for the windows account, which shows up as "DB1\mirroraccount". Assign needed privileges to the DB1\mirroraccount
7. Open a browser on WS1 and go to a web page which connects to the database on DB1. Error results
Login failed for user ''. The user is not associated with a trusted SQL Server connection. [CLIENT: 10.31.11.53]
On the SQL Server I also get the log error
02/18/2008 16:56:42,Logon,Unknown,SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: 10.31.11.53]
The user credential submitted to SQL Server appears to be a blank or null string.
I also verified/tested the following
1. The database connection from WS1 to DB1 works when I use a connection string with
User ID=xx;Password=xx;Trusted_Connection=False;
2. The trusted connection works when I point it to a database on WS1 (ie the database is on the same machine as the web server, and the database has an account for "MirrorAccount")
3. When I switch the web site on WS1 to use the DefaultAppPool, a security credential IS passed to the SQL Server
02/19/2008 11:32:33,Logon,Unknown,Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 10.31.11.53]
So why is a credential being sent when I use the default app pool?