How to set-up SQL Server / IIS 7.0 to allow ASP.NET MVC website to access to database?

Question

I have built an ASP.NET MVC 2 website that I am hosting with Rackspace Cloud Server using IIS 7.0.

When I try to test the website under localhost with IIS 7.0 on the server I get an error page. This comes from the SQL logs (I am using SQL Web Edition):

Error: 18456, Severity: 14, State: 11
Login failed for user 'IIS APPPOOL\DefaultAppPool'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]

My website works fine on my home PC so the issue is something to do with connecting with the database.

Having researched the error message code it suggests that I have a valid DB login but server access has failed. I did a stack of reading around this on various forums (incl. stackoverflow / serverfault) and here is what I have tried, none of which have solved the issue:

1. Set up new login using SQL Server Authentication. I created a new login in SQL and gave correct access rights to my DB. I used following connection string in web config:

   Data Source=myServer;Initial Catalog=myDB;User Id=myUsername;Password=myPassword;

2. Create new login as IIS APPPOOL\DefaultAppPool. I created new login in SQL with correct access rights and used windows authentication. Connection string as follows:

   Server=myServer;Database=myDataBase;Trusted_Connection=True;

3. Use existing network login NT AUTHORITY\NETWORK SERVICE. I mapped this user to my database with correct access rights. Connection string as follows:

   Server=myServer;Database=myDataBase;Trusted_Connection=True;

These appeard to be the main options as I read through material online. Finally, here as some other things that may be helpful:

• Setting SQL to run as administrator did not solve the issue
• Turning off UAC did not help
• My application pool is using ApplicationPoolIdentity (best for security reasons)

In short, my website won't start because it cannot get an object from the DB so throws an error. The issue is that access rights to the server are not set correctly. I cannot work out what configuration of SQL login / connection string / domain access rights I need.

This has vexed me for 3 weeks - can you spare 5 mins to help me please?

Answer 1

Since your servers (web and SQL) are not part of a domain, you can't use Windows Authentication (Integrated Security) to connect to SQL Server.

In IIS, when having Integrated Security=SSPI in a connection string, the actual user used to connect to SQL Server is the application pool identity.

You should go with your option 1, meaning Set up new login using SQL Server Authentication.

So, the steps would be:

1. Create your myUsername SQL Server user.
2. In the user's properties dialog, go to the Securables section and make sure Public is checked.
3. Next, move to the User mapping section (in the left side). Look for your database(s) in the list and check it. Down below, in the Database role membership list, make sure you check public, db_datareader and db_datawriter.

4. Now give your user rights to execute stored procedures. As sa, in Management Studio, execute:

   GRANT EXECUTE TO myUserName;

5. You're done.

As for security, you can later refine the rights of myUserName to not be able, for example to drop tables or other objects.

In your application you should use now the connection string at your point 1.

If you have any other problems, then please post the error message.