SQL Trusted Connections with ASP.NET

Hard coding passwords into your application or your web site is a bad thing. Microsoft SQL has the ability to use "trusted connections" to authenticate your database connection against your login name, so no passwords are ever sent to SQL server, just your login name and an authentication token. But once you come to use this feature in asp.net you run into problems, because of how asp.net works and the user it runs as. In a default configuration asp.net runs as (or rather, in the context of) the ASPNET user on the local machine. If your SQL server is on the same machine as your asp.net pages then trusted connections are easy, simply grant access and appropriate permissions to the ASPNET user within SQL and change your connection string to use Integrated Security=SSPI or Trusted_Connection=true depending on your connection string style.

The problem arises when your SQL server and your IIS/asp.net server are on separate machines, which is a typical setup. As the ASPNET user does not exist on the SQL server you can't grant access to it.

There are 4 main ways to overcome this problem

• Use IIS6 in native application mode
• Mirror the asp.net user on both the IIS box and the SQL server and set a known password
• Use impersonation to change the context your pages run in
• Encrypt a connection string in the registry and forget about trusted connections.
• Switch the asp.net context to be a domain user.

Running any web service as a domain user is ill-advised. A compromise of your web server would mean that any cracker would then have an authenticated session to your domain or active directory and be able to wander outside the web server and through any other machines the user context has access to.

Encrypting and accessing data in the registry is covered by Microsoft Knowledge Base article 329290 and an MSDN article in the Building Secure ASP.NET Applications patterns and practices section.

Both impersonation and mirroring the asp.net user require you to mirror accounts on both the web and SQL server if you are not in a domain/AD environment.