Setting Up Login for ASP.NET Application on SQL Server 2008

Question

I have 2 Windows 2008 Servers. One is a web server (IIS7). The other is a database server (SQL Server 2008). The instance of sql server 2008 is configured to use mixed mode authentication. Neither are joined to an active directory domain.

How should ASP.NET applications on the web server login to the databases on the database server? I have tested with the SA account, but obviously I need to change that.

I am confused about what to use: sql server account, windows accounts configured on each machine, or "Application Roles," which I don't really have a great understanding of.

The account just needs to be able to read and write data, and execute stored procedures. What should I do and how do I set it up?

...

Instead of just a quick answer, please give me instructions on how to set this up. Thanks.

Answer 1

The more secure method uses Windows authentication. This will allow you to write your application without a password included in any of the connection strings or config file.

One each system you will create a user account. This should not be an administrator account, just a regular user account. One both systems create the user with the same name and password.

Now on your IIS system edit the application pool settings. Change the identity to run as the user account that you just created. At this point you will need to type in the password for the user.

On the SQL server create a login account that is associated with the Windows use account that you have created. You can use the normal SQL Server permissions to grant/deny the actions that your application requires.

Now in your application connect to the SQL server using integrated security. The app pool user will be used when making the SQL connection.

No password in your config or app and a secure database connection!

Answer 2

The most common method to use would be a SQL Login. Create a SQL Login with a strong password. Add it as a user to the database your application will be using. Create a database role in the database and make your user a member of this role. Grant the database role access to the objects that it needs access to.

You'll want to only grant the role the rights that it needs.

The reason for the database role is so that you can restore the database to another server and be able to easily grant the exact rights to another user so that you can ensure that everything is working correctly.