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.
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.
<connectionStrings> <add name="MyConnection" connectionString="Data Source=SERVER;Initial Catalog=ITNOTADB;Integrated Security=SSPI" /> </connectionStrings>
<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.
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