How to use Trusted Connection when SQL server and web Server are on two separate machines.

(Ref: http://weblogs.asp.net/achang/archive/2004/04/15/113866.aspx)

Recently, in our consideration to make the production server environment more secure, one of the things we looked into was securing the database connection information between the web server and SQL server.  I received much help from attending DevDays 2004 and also from doing some research on the Internet on this matter.  There are some options when it comes to securing the connection string between a database server and a web server. I'll just list a few here:

1 Use a trusted connection with integrated authentication.

2 Use the Aspnet_setreg.exe utility to encrypt the connection string and store it in the registry.  You can refer to these two articles for detailed instructions on how to do this:  http://support.microsoft.com/default.aspx?scid=kb;EN-US;329290 and http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/SecNetHT11.asp

3 Using DPAPI (Data Protection application programming interface) to secure the connection string.  You can refer to these articles: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/SecNetHT08.asp and http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsecure/html/windataprotection-dpapi.asp and http://www.codeproject.com/system/protected_data.asp

In this blog, I'll focus more on option 1 - using a trusted connection.  You see, it's very straightforward to configure a trusted connection when the SQL server and web server are on the same machine.  For Windows 2000 Server or XP Pro machines, you just need to make sure the local “ASPNET” account (under which ASP.NET application runs) has appropriate access to the database that your application will be using. For Windows Server 2003, you need to make sure that the “Network Services” account (or “IIS_WPG” group – of which the “Network Services” is a member) has the appropriate access to the database. You can follow a step-by-step procedure to configure this here: http://www.asp.net/faq/AspNetAndIIS6.aspx#4 (under the section titled “Supporting integrated authentication with SQL Server”).

The problem arises, however, when the database server and the web server are on two separate machines (assuming they are on the same network). The issue lies in the fact that the local accounts/credentials (“ASPNET” in Windows 2000 Server and Windows XP Pro., “Network Services” in Windows Server 2003) under which ASP.NET worker process runs are local accounts (local to the web server). Therefore the database server on a separate machine will not be able to see/recognize these accounts. So if you try using the same steps mentioned above to configure a trusted connection between the web server and the SQL server, you will most likely see an error message such as “Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.” , when you try to launch your ASP.NET application.

One solution is to set up a domain account with minimal access (the same level of access as the local “ASPNET” or “Network Services” account on the web server) and grant this account the appropriate access to the database on the SQL server that your web application will be accessing. Then, you can use impersonation to make your ASP.NET application run under the domain account that you just created. (Does anyone know of a way to set up the trusted connection without impersonation? I would very much like to know how we can avoid using impersonation and any overhead associated with it. Some postings mentioned making the local accounts “ASPNET” or “Network Services” on both servers to have the same password, but I don’t think this is a good solution). Here are some tips/steps on using impersonation for trusted connection with a domain account:

1 When creating the domain account, make sure it has the proper level of access to run ASP.NET applications.  You can refer to this page for a list of directories and the permissions that this domain account must have to them.  For security purposes, don't give this domain account any more permission than you have to.

2 Assign the domain account the necessary permission to the database (you can still follow the instructions at http://www.asp.net/faq/AspNetAndIIS6.aspx#4. Just make sure you choose the domain account that you just created).

3 In order to turn on impersonation, there are two options:

a. In the web.config file of your application, add the following line: <identity impersonate="true" userName="domain\user" password="password" /> (put it somewhere between the <system.web></system.web> tags) However this really defeats the purpose of using the trusted connection to avoid having to include the credential and the password in the connection string. If you use this method, you will still need to encrypt the credential and the password in order to keep this information safe.

b. (This is the better method in my opinion) In the web.config file, add the following: <identity impersonate="true" /> (this time without the user name and password). Then, from IIS Manager, right click on the virtual directory assigned to your application and select “Properties”. Click on the “Directory Security” tab, then under “Anonymous access and authentication control” click “Edit”. If you are going to allow anonymous access to the application (not requiring users to login with windows authentication each time they access this web application), make sure “Anonymous access” checkbox IS checked. Then uncheck “Allow IIS to control password”. In the “User Name” field, type in (or you can browse to) the domain account (domain\user) and type in the password for the domain account in the “Password” field (By default IIS uses IUSR_machinename for the anonymous account, you can refer to this page if you would like to find its default password in case you want to change it back later for some reason) . Then under “Authenticated Access”, make sure NONE of the boxes are checked. Press “OK” and “OK” to save the settings and exit IIS.

4 Then in web.config you can use either one of these connection strings:

a. "data source=yourservername;initial catalog=databasename;Integrated Security=SSPI"

b. "server=yourservername; database=databasename;Trusted_Connection=true"

Hopefully, by using the tips and steps above, you should be able to get the trusted connection to work between the SQL server and the web server.

I hope this long-winded blog will be helpful to some. I don’t claim to be an expert in this, so as I mentioned earlier, if anyone has a better solution for configuring the trusted connection, I am all ears. I really would like to know if there is anyway to use trusted connection without impersonation.

Here are also some good references/discussions related to this topic:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbtskaccessingsqlserverusingmappedwindowsdomainuser.asp

http://weblogs.asp.net/cschittko/archive/2004/03/05/85035.aspx

http://support.microsoft.com/default.aspx?kbid=316989

http://dotnetjunkies.com/Newsgroups/microsoft.public.dotnet.framework.aspnet.security/2004/3/22/75451.aspx

http://dotnetjunkies.com/Newsgroups/microsoft.public.dotnet.framework.aspnet.security/2004/3/22/74067.aspx

http://www.winnetmag.com/Article/ArticleID/9002/9002.html

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/SecNetAP05.asp

http://www.kamath.com/tutorials/tut002_iisanon.asp

http://www.microsoft.com/technet/community/columns/insider/iisi0102.mspx#XSLTsection124121120120

Update: Frans Bouma mentioned that running web server on a domain may not be according to Microsoft's best practice for security.  Could someone confirm this?  Is there a better way then to configure a trusted connection when IIS and SQL server are on separate machines without the use of impersonation