Sql-server – SQL Server Connection Problem

connectivitysql server

I'm completely stuck on a connection problem that we're experiencing for users connecting to our SQL Server instance from a particular domain using a particular application. The error that we're seeing is (from Windows Event log):

2020-11-30 00:11:22.33 Logon       Error: 17806, Severity: 20, State: 14.
2020-11-30 00:11:22.33 Logon       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. The logon attempt failed   [CLIENT: xxx.xxx.xxx.xxx]
2020-11-30 00:11:22.33 Logon       Error: 18452, Severity: 14, State: 1.
2020-11-30 00:11:22.33 Logon       Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: xxx.xxx.xxx.xxx]

Users are able to connect to the database over the same session from an old legacy app that uses the old ADO database driver. But our newer .NET Framework app is unable to connect due to this error. Also, it works when I run the app from my sysadmin account. As a hapless developer with a limited understanding of SQL Server security I'm a bit out on thin ice when it comes to further diagnosing the problem here. Does anyone have any ideas what could be causing this problem?

Best Answer

You have already stated, respectively listed what the issue could be:

 Login failed. The login is from an untrusted domain and cannot be used 
 with Windows authentication. [CLIENT: xxx.xxx.xxx.xxx]

If your other domain doesn't have a trust with the main domain, then you can receive the above error message.

However, you also list the first part of the error message which states:

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. 
The logon attempt failed   [CLIENT: xxx.xxx.xxx.xxx]

So what is SSPI? Microsoft describes it as:

In conjunction with its operating systems, Microsoft offers the Security Support Provider Interface (SSPI). The SSPI provides a universal, industry-standard interface for secure distributed applications.

Reference: Security Support Provider Interface (SSPI) (Microsoft Docs)

Basically it's how an application authenticates itself, the user or other components against an endpoint asking for authentication.

In the Architectural Overview there is the description that ...

A variety of SSPs and packages are available. Windows ships with the NTLM security package and the Microsoft Kerberos protocol security package. In addition, you may choose to install the Secure Socket Layer (SSL) security package, or any other SSPI-compatible SSP.

Reference: SSPI Architectural Overview (Microsoft Docs)

NTLM

When your old application starts in the other domain it will pass on the old NTLM SID value and compare it with what is stored in the SQL Server instance.

If there is a match [Domain/User] with a SQL Server Login [Domain/User] then SQL Server will let the user login to the relevant databases. SQL Server will do the same for a [Domain/User] that is assigned to a SQL Server Windows Group Login.

Kerberos

Kerberor relies on the ability of a user or service account being able to generate a token (of a request) and to compare this with a central provider to determine if a user is who he/she/them claim to be.

emphasis mine

Kerberos V5 is based on the Kerberos authentication system developed at MIT. Under Kerberos, a client (generally either a user or a service) sends a request for a ticket to the Key Distribution Center (KDC). The KDC creates a ticket-granting ticket (TGT) for the client, encrypts it using the client's password as the key, and sends the encrypted TGT back to the client. The client then attempts to decrypt the TGT, using its password. If the client successfully decrypts the TGT (i.e., if the client gave the correct password), it keeps the decrypted TGT, which indicates proof of the client's identity.

Reference 1.1 What is Kerberos and How Does it Work? (MIT Kerberos V 5.0)

Explanation Summary

So your service (SQL Server), the application host and/or the user trying to start the application and accessing the database have to be able to generate Kerberos tokens.

To do this they should have a Service Principal Name (SPN). Which is described as:

A service principal name (SPN) is a unique identifier of a service instance. SPNs are used by Kerberos authentication to associate a service instance with a service logon account. This allows a client application to request that the service authenticate an account even if the client does not have the account name.

And a bit further down the possible root cause:

If you install multiple instances of a service on computers throughout a forest, each instance must have its own SPN. A given service instance can have multiple SPNs if there are multiple names that clients might use for authentication. For example, an SPN always includes the name of the host computer on which the service instance is running, so a service instance might register an SPN for each name or alias of its host. For more information about SPN format and composing a unique SPN, see Name Formats for Unique SPNs.

Reference Service Principal Names (Microsoft Docs)

To Cut a Long Story Short

If either your application (host) and/or your SQL Server Instance are unable to register a Service Principal Name then your application will be unable to authenticate users via Kerberos and your application will fail.

Check the ERRORLOG of your SQL Server instance and verify that the SQL Server instance was able to register its own SPN. There will be an entry during the instance startup procedure.

The SQL Server Service Account can do this if it is a Domain Service Account and some other conditions are met. Some being:

The client and server computers must be part of the same Windows domain, or in trusted domains.

and

A Service Principal Name (SPN) must be registered with Active Directory, which assumes the role of the Key Distribution Center in a Windows domain. The SPN, after it's registered, maps to the Windows account that started the SQL Server instance service. If the SPN registration hasn't been performed or fails, the Windows security layer can't determine the account associated with the SPN, and Kerberos authentication isn't used.

Reference: Register a Service Principal Name for Kerberos Connections (Microsoft Docs)

Output of successful SPN registration

2020-12-01 11:14:18.87 Server      
SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. 
Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. 
This is an informational message. No user action is required.
...
2020-12-01 11:14:18.94 Server      
The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) 
[ MSSQLSvc/SERVERNAME.TG.CH ] for the SQL Server service.

If the service can't register the SPN, then:

Note

If the server can't automatically register the SPN, the SPN must be registered manually. See Manual SPN Registration.

Output of Unsuccessful SPN registration

2020-11-27 15:41:56.24 Server      
SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. 
Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. 
This is an informational message. No user action is required.
...
2020-11-27 15:41:56.43 Server      
The SQL Server Network Interface library could not register the Service Principal Name (SPN) 
[ MSSQLSvc/SERVERNAME:INSTANCENAME ] for the SQL Server service. 
Windows return code: 0x200b, state: 15.
...
2020-11-27 15:41:56.43 Server      
The SQL Server Network Interface library could not register the Service Principal Name (SPN) 
[ MSSQLSvc/SERVERNAME:INSTANCEPORT ] for the SQL Server service. 
Windows return code: 0x200b, state: 15.

Once your SQL Server instance has its SPN registered, then you should be up and running.

Manual SPN Registration

Well you have to be a Domain Administrator or allowed to change Computer Account objects on the OU of the domain. Then you can run SETSPN with the relevant parameters:

setspn -A MSSQLSvc/host.domain.tld:1433 domain\accountname

Reference: Manual SPN Registration (Microsoft Docs)

Once an SPN has been configured for a service account, then you should be able to start the application and access the database.

There is the additional hurdle of having to delegate permissions to application servers and intermediate (system) hops sometimes, but that would extend this brief introduction somewhat.

SPN registration requires a restart of the service.

Related Question