Support

Dear reader, if you got someting out of my blog and if it helped you, it would be great if you supported me by clicking on the Google ads-banner at the right. This will not cost you anything but time but will generate a little bit of revenue for my time invested ;)
I'm as well happy if you LIKE or 1+ it and about comments that help to improve my articles.

Tuesday, June 18, 2013

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