What is the potential problem?
In the real life deployment, security configuration is a little bit different from security configuration on your development machine. I personally (when programming ASP .NET on windows 2003 server as my development environment) add the windows group IIS_WPG (IIS worker process group) to SQL server logins and make it a System admin in SQL Server because I am too lazy to configure security setting on my development machine for each access to SQL Server. You may do the same thing or if you are developing on windows XP, you do the same thing with ASPNET account. Doing this will eliminate any additional security configuration on your development machine and you will just focus on application and business logic. But I doubt if you can ever do it on production environment. On the other side in production environment rarely you have your database server (SQL Server 200X) installed on the web server. Even if so it doesn’t sound logical to have a windows built-in group as your database administrator!!!
The real problem is that ASPNET account or IIS_WPG or even NETWORK SERVICE accounts are not identified outside your web server box. Now how shall we configure it to connect to SQL server on a different machine?

Assumptions
I don’t mean to describe each and every possible scenario and I even don’t mean to describe the remote hosting solutions that the ISP forces you to stick to a certain security configuration. I assume that you have your own company network and you can configure your network setting with help and support from your Network/Database administrator. As a result I will provide the ideal solution for the ASP .NET deployment on production.

Network topology
Here is the most possible scenario that you may have.

• Active directory and your Domain Controller (I call it AD-Domain for this example)
• A web server with IIS 6.0 on windows 2003 server (I call it WEBSVR)
• A database sever machine with windows 2000 or 2003 server and SQL Server 200X (I call the machine DBSVR and I assume that the SQL Server installation is a default instance)

Of course WEBSVR and DBSVR are both members of AD-Domain.

Our deployment plan
 We would like to use IIS 6.0 application pool identity to deploy ASP .NET application. In this way we use windows authentication to login to SQL Server. To have a windows identity to be identified on DBSVR and WEBSVR we will utilize windows active directory.

A quick briefing on IIS 6.0 Application Pool model
 I don't know how familiar you are with IIS 6.0 enhancements, so let's have a quick review.

 If you open IIS Manager in windows 2003 server you will notice a tree view node on the left pane as Application Pools. You may not see all the application pools that I have on my screen shot but for sure you have the DefaultAppPool. By default when you create an ASP .NET application, it will be added to DefaultAppPool. But what are these application pools? IIS 6.0 is not just one process. In IIS 6.0 you can create your own custom working processes as application pools. The very basic benefit is that if you want to restart an ASP .NET application you just restart it's container pool (not the entire IIS) or if one web application crashes it doesn't crash all the web applications on IIS. But that's not the whole thing. As a process each application pool has an identity. In better word you can assign a user account to your desired application pool and all the web applications running under that application pool will have the same account identity. Hey, imagine if that account is an active directory account, it can be identified even out of the web server box on the other machines. This is all we want to do as a walk through in this article.

Roll up your sleeves and start doing it!
 Here are the steps you need to follow for a successful deployment.

1. Create a Domain account in Active Directory: To have a user account that can be identified on multiple computers, you need to create an account in active directory. Assuming that you are a programmer in your company just ask your network administrator to create the domain account for you. I assume the account will be AD-Domain\MyWebAccount. If you are not creating the account yourself make sure you get the password for the account from your network admin!! No additional permission or any domain level role membership is required.
2. Deploy your application database and Introduce the account to your database server: At this level you deploy your web application database to SQL Server (Database deployment is up to you. Do it how ever you enjoy it). After you deploy the database add a new login to your SQL Server (in SQL Server 2000 go to Enterprise manager -> Security -> Logins -> right click and add new login). Your New login Dialog Box will look like this:
   
   Then go to the Database Access tab and provide the user access to your database that you deployed in this step. You may also need to go to the database and grant required permissions for the database objects to this new login account.
3. Introduce the account to your web server: To have a web application running under a certain account in windows 2003, that account should be a member of IIS_WPG group. Assuming that your web server is a member of AD-Domain on the web server (WEBSVR) go to computer management and add the user AD-Domain\MyWebAccount to that group.
   
4. Create a new Application Pool on IIS and assign the new domain account as it's identity: Now it's time to work a little bit with IIS 6.0 specific featurs. Go to IIS Mamager on your web server (WEBSVR) and right-click on the Application Pools and then New -> Application Pool. I call this application pool, MyAppPool and accept the default.
   
   If you press OK the new application pool will be created, but let's examine the new pool and see what more configuration we need.
    In the IIS Manager right-click the new application pool (MyAppPool) and see the properties. Click the Identity tab and you will see the predefined identity is Network Service which is a built-in windows account and of course will not work for us. Make the Identity configurable and set the user name to AD-Domain\MyWebAccount and also provide the password for that account.
    
   Just press OK. Your application pool is ready now and any web application running in this pool will get the identity if AD-Domain\MyWebAccount and will have all the privileges granted to AD-Domain\MyWebAccount.
5. Deploy your ASP .NET application and assign it to it's new application pool: Deploy you ASP .NET how ever you like (Copy web, XCopy, Windows installer or ...). After you deploy the project you should find it under IIS Manager Web Sites. Right-click on the web ASP .NET application and see the properties. In the Virtual Directory tab change the Application Pool to the pool that you created in step 4 (MyAppPool) and click OK button.
   
   Congratulations. Now your web application is running under a domain account.
6. Provide additional permissions to the Domain account as required: By now you are done with your deployment. Just consider some additional permissions that you may need to provide for the new account. For example if your ASP .NET application updates a file on the server you may need to provide NTFS permission for AD-Domain\MyWebAccount to read, write and modify that folder.
7. Your application will live happily ever after: You are done with your application deployment. You have avoided SQL Server authentication and utilized active directory in a very efficient way and you haven't included any user name and password in your application or ConnectionStrings.

Discussion
 Of course the above solution is not the only way but is one of the best ways. ASP .NET impersonation is also a very nice solution but in .NET 2003 many programmers were reluctant about it, because they simply didn't want to have User ID and Password in the web.config file which is a totally readable and unencrypted content. With introduction of .NET 2005 again we can consider impersonation as a nice solution, because we can symply encrypt sections of web.config.
 The last solution (and in my opinion the worst) solution is using SQL authentication and providing User ID and Password as parameters in ConnectionString. This solution with .NET 2005 works a lot better, because again we can encrypt the connection string containing User ID and Password to the database in web.config, but still it can do nothing to protect the ConnectionString in network transmission.
For any comments or clarifications contact me at www.dotnetking.com/contactus.aspx.

By: Alireza Ahmadi Aliabadi
MCSD, MCDBA, MCAD, MCT
MVP as Visual C# Developer