When migrating ASP.NET applications from Windows Server 2003 (IIS 6) to 2012 R2 (IIS 8.5), right away, one web application displayed the yellow screen of death with the following error message:
System.Data.SqlClient.SqlException: Login failed for user ‘DOMAIN\SERVERNAME$‘.
If your application is on a private network, depending on your situation, there is a few approaches you can take.
Options
-
Grant DOMAIN\SERVERNAME$ user id read and write to the SQL Server database. Application pool needs to run under the identity of NetworkService.
So now the question is how do you add DOMAIN\SERVERNAME$ username as a new login in SQL Server? This may be obvious to many of you, but it tripped a few people including some DBAs here. The key is just to type the DOMAIN\SERVERNAME$ in the Login name: text box without searching it. Click OK button right away. Then you should be able to do the rest to add the roles, etc to the new login.
-
Change Application Pool Identity to use custom account and have it run under a network service account. Bear in mind, the service account also needs to have access in SQL Server.
-
Create an SQL Server login and make sure the connectionStrings in the application’s web.config use User Id and Password instead of using integrated security.
Before
<connectionStrings> <add name="MyConnection" connectionString="Data Source=SERVER;Initial Catalog=ITNOTADB;Integrated Security=SSPI" /> </connectionStrings>
After
<connectionStrings> <add name="MyConnection" connectionString="Data Source=SERVER;Database=ITNOTADB;User ID=ITNOTA;Password=Pa$$w0rd" /> </connectionStrings>
The keywords Initial Catalog and Database can be used interchangeably.
After some considerations, we opted for the third option with the acknowledgement that while it was not the best option, it was the quickest to do to resolve the issue.
Further Reading
Error message when you specify configuration database settings on the Set Configuration Database Server page in SharePoint Administration: “Error 18456”
Understanding Kerberos and NTLM authentication in SQL Server Connections
Application Pool Identities
Is using integrated security (SSPI) for accessing SQL Server better for web applications?
Professional Microsoft IIS 8
Andrea says
The solution was to add the “NT AUTHORITY\NETWORK SERVICE” to SQL logins, as suggested by jimi. Thank you
Prahalad says
I’m afraid I don’t see any “login” option under “Security->New” for me to add a new SQL Server login. How do I go about it? Thanks!
Jimi says
Hi,
Thanks for a good article, I just want to add my solution.
The solution for me was actually to add the “NT AUTHORITY\NETWORK SERVICE” to SQL logins, since the web application and database are on the same machine it looks like no remote call is made even though the debug error is “Login failed for user ‘DOMAIN\MACHINENAME$”.
Adding the machine name to SQL logins did not help, but adding “NT AUTHORITY\NETWORK SERVICE” did (Note that this is for a single server setup).
//Jimi
platt says
Thanks for mentioning that Jimi.
Srini says
Thank you Buddy, you saved me a ton of work!
Cheers!
Srini
platt says
No problem Srini. I’m glad it worked for you. 🙂