App using SQLClient(.NET 4.0) will automatically construct an SPN when using shared memory with windows authentication
Consider the following scenario:
1) You have a .NET 4.0 application connecting to SQL Server 2005 or SQL Server 2008 edition (including SQL Server Express Edition) with ADO.NET using SQLClient provider.
2) Only Shared Memory is enabled for protocols for SQL Server Instance
3) All client protocols are enabled for SQL Native Client and SQL Native Client 10.0.
4) The application is running on Windows 7 machine.
In this scenario, the application may fail to connect to SQL Server, with the following error message:
Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: <local machine>]
Additionally, you may also see the following error message in the application event log:
SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. [CLIENT: <local machine>].
At a first look, it may seem that the issue is related to Kerberos, because we see most of the SSPI handshake error messages due to Kerberos failure, which would most likely be related to non-existent SPN or bad SPN for SQL Server. We would expect that, for local connections, we are connecting over NTLM and the SPN is not required for NTLM.
A reference discussion on the error “SSPI handshake failed with error code 0x8009030c” can be found in this forum post: https://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/c46b0257-7304-47dc-a8b5-090001ff70a5
While I will discuss on how we can resolve the “Login failed” issue, the main goal for this blog is to point out that, there is a breaking change in ADO.NET SQLClient provider implementation of .NET 4.0. SQLClient in a .NET 4.0 Application will automatically construct an SPN for SQL Server connections over shared memory when windows authentication is used. In the prior version, .NET 3.5 SP1, this was not the case, for connections, we would use NTLM directly. To comply with NTLM Reflection Protection and to Extended Protection improvements in Windows Security, we now use the Negotiate SSP ( https://msdn.microsoft.com/en-us/library/aa378748(VS.85).aspx ), rather than directly using NTLM, in addition to constructing an SPN for the SQL Server we connect to locally from the ADO.NET Applications. Authentication defaults will be as detailed in the MSDN Article https://msdn.microsoft.com/en-us/library/ms191153.aspx under "Authenticaion Defaults" section.
In the above scenario, the reason of the “Login Failed” error message was due to the fact that, when attempting a local connection over shared memory, SQLClient was constructing an SPN for NTLM authentication, for the reasons explained above. Due to the DNS resolution, the constructed SPN became different (we were getting a wrong suffix) than the SPN that is present in domain controller.
While the correct DNS resolution is required to fix the issue (so we can construct the correct SPN), to work around the issue, we can use one of the following methods:
1) Create a named pipe alias for the SQL Server you are connecting to using SQL Native client 10.
2) Change the registry key as per KB, to include " BackConnectionHostNames ", Method 1 on the KB: https://support.microsoft.com/kb/926642
Method 1 (recommended): Create the Local Security Authority host names that can be referenced in an NTLM authentication request
To do this, follow these steps for all the nodes on the client computer:
1. Click Start, click Run, type regedit, and then click OK.
2. Locate and then click the following registry subkey: HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlLsaMSV1_0
3. Right-click MSV1_0, point to New, and then click Multi-String Value.
4. In the Name column, type BackConnectionHostNames, and then press ENTER.
5. Right-click BackConnectionHostNames, and then click Modify.
6. In the Value data box, type the CNAME or the DNS alias, that is used for the local shares on the computer, and then click OK.
Note Type each host name on a separate line.
Note If the BackConnectionHostNames registry entry exists as a REG_DWORD type, you have to delete the BackConnectionHostNames registry entry.
7. Exit Registry Editor, and then restart the computer.
MORE INFORMATION
Registering a Service Principal Name
https://msdn.microsoft.com/en-us/library/ms191153.aspx
Service Principal Name (SPN) Support in Client Connections
https://msdn.microsoft.com/en-us/library/cc280459(v=SQL.105).aspx
What SPN do I use and how does it get there?
https://blogs.msdn.com/b/psssql/archive/2010/03/09/what-spn-do-i-use-and-how-does-it-get-there.aspx
Error message when you try to access a server locally by using its FQDN or its CNAME alias after you install Windows Server 2003 Service Pack 1: "Access denied" or "No network provider accepted the given network path"
https://support.microsoft.com/kb/926642
Understanding Kerberos and NTLM authentication in SQL Server Connections
Understanding the error message: “Login failed for user ''. The user is not associated with a trusted SQL Server connection.”
Author : Meer(MSFT), SQL Developer Escalation Services , Microsoft
Posted by : Enamul(MSFT), SQL Developer Technical Lead , Microsoft