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.

Impersonation

First let's cover impersonation. Impersonation allows you to tell asp.net to run as a particular user. Create a matching username and password on both machines, making sure that you use a strong password. On the IIS server the new user name must have appropriate permissions to run as an asp.net user, detailed in MSDN. On the SQL server it must have access to the database, stored procedures, views and, if you really must, tables, appropriate to your application.

You must now configure asp.net to run as this user. There are two methods of doing this, you can either hard code the password into web.config (running the risk of password "leakage" should your web.config file become exposed) or by using the IIS Administration tool, in conjunction with an edit to your web.config.

To hard code your new context edit web.config and change the authentication mode settings in system.web to something like

<system.web>
<authentication>
...
<identity impersonate="true"
userName
="yourNewUsername"
password
="yourStrongPassword"
/>
...
</authentication>
</system.web>

This, of course, defeats the object of not hard coding passwords in your application.

If you don't want to hard code the password (and you shouldn't) you can leave out the username and password from web.config and instead configure IIS to provide these details. Edit your web.config so it looks something like

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

Now open up the IIS Administration tool on the web server and right click on the directory your application is running in. Open the Properties window, choose the Directory Security tab click the edit button beside Anonymous access and authentication control and clear the check box beside Allow IIS to Control Password. You can then change the context for this application and enter your new username and password.

Editing the default context

If you are running IIS5 and all that seems like too much effort, or you have multiple web applications and do not wish to edit every single system.web and IIS virtual directory you can edit the default context for asp.net. As I have already said asp.net runs under the ASPNET user. This account already has the correct permissions but the password was set on installation of the framework and we don't know what it is. What we can do is reset the password for this user, (remembering to setup an ASPNET user on the SQL server with the same strong password) then edit machine.config to tell the .net framework to use this new password. Unfortunately you must enter the password in machine.config in plain text, but as this file lives in

c:\windows\microsoft.net\framework\versionNumber\config

the risk of exposure via a hacked FTP account, or a leaking web service is minimal.

To reconfigure the asp.Net process model to use the new password search web.config for the processmodel tag and enter or change the identity parameters to contain your new password

<processmodel
...
userName
="ASPNET" password="ASPNETpassword"
...
/>

Don't forget to restart IIS.

IIS6 in Native mode

Finally, and probably most optimally, we have IIS6 which runs asp.net applications in native mode (you know you wanted to update to Windows 2003, here is your excuse). IIS6 allows you to create Application Pools, a context in which you can run one or more applications under. A pool controls performance, recycling of processes and most importantly for trusted connections the security context an application runs under. Again you must create a mirrored username and password on both the IIS server and the SQL server. To create a new pool start the IIS administration tool, right click on Application Pools and choose New Application Pool. Name your pool and click OK. Right click on your new pool name and choose Properties, then select the identity tab. You can now set the pool to run as your mirrored user. You must then change your application setting to use the new pool. Expand out the Web Sites tree, navigate to your application directory, or the web site containing your application, right click, choose Properties and choose the Home Directory tab. You can then change the Application Pool setting to your new pool and your application will run in the context you specified.

Side effects

There are side effects to using trusted connections. If each connection is opened under a different user account then these connections are not pooled between users. This then increases load on both the IIS server and the SQL server. Trusted connections also take more processing power when authenicating the connection than SQL style logins as the login tokens are now validated outside SQL by the NT authenication process. If you are validating against a domain you may see increased load on your domain controllers as well as the SQL server. Performance testing is pretty much mandatory before you role your web site out.

Summary

There is no real "you do it this way" answer to using trusted connections with asp.net, Microsoft's flexibility gives you multiple choices on how to solve your problem. It is up to you to decide the best way for your application.