How to Set Up MS SQL Server and IIS 7.5 for ASP.NET 4.5 Application

Background

I am currently developing an ASP.NET 4.5 application with connection to a database on MS SQL Server 2008 R2. I am using some dropdown-box controls on one screen which I configured to connect directly to the DB. To run the application in our company's intranet I deployed it on my local Windows 7 Professional PC, where I was running IIS 7.5 as web server. As development environment I was using MS Visual Studio Express 2012 for Web.

Everything worked fine as long as I was developing in Visual Studio, where I was able to start the application on Chrome and localhost without problems. The trouble started when I deployed my web application to IIS. In principle it worked, I was able to run the application through port 80 after I opened the corresponding port in the fire wall. However, the system crashed as soon I was calling a screen with dropdown-boxes connected to the SQL Server.

The error I got on the dump-screen was the following:

"Server error in application /MyApplication"

"Login Failed for user 'MyDomainName\MyPCName$'"

[...]

"[SQLException (0x80131904): Login failed for user 'MyDomainName\MyPCName$' ....

Attempts

I started to read loads of articles and tried the following things:

• Introduction of a dedicated user for the login on the SQL Server together with Windows-Authentication didn't work - and why should it?
• Switching from Windows Authentication to SQL Server Authentication: I convinced our SQL Server admin to switch the Authentication mode from Windows to SQL Server & Windows. However, this was a non-option in the end as I was not the owner of the server and a re-start of the server was prohibited.
• IIS impersonation: didn't work as this requires SQL Server Authentication (see above)

Solution

In the end I became curious about the fact that the error message which stated something about 'MyDomainName\MyComputerName$'. I created in SQL Server a user 'MyDomainName\MyComputerName' using Windows Authentication mode. This caused the following error message

Create failed for Login 'MyDomainName\MyPCName' [...] Server, Error: 15401

Finally, I found that typing 'MyDomainName\MyPCName$' worked. Furthermore you have to set the following settings:

• The General Settings look like this:

• In the database you want to use create a user with the same name MyDomainName\MyPCName$

• In the Section "User Mapping" of the Login Properties (Section SQL Server Instance --> Security --> Login Properties make the following settings

I hope this article helped you. If not, please send me comments to improve it.

Reference: "Login failed for user <user>"; http://msdn.microsoft.com/en-us/library/ab4e6cky(v=vs.80).aspx