The Problem:

I have 2 SQL Server names Server A and Server B. I have created a linked server on Server A to Server B which means I would like to run a query on Server A which pulls some data from a database on Server B. I configured the Linked Server security to “Be made using the login’s current security context” which for me my domain account. I have sufficient user permissions on both the servers.

When I do an RDP to Server A and run the query involving Linked Server it works without an issue, however when I either connect to Server A from my machine or do an RDP to our company’s hopbox and connect to Server A and run the same query I get an error.
It says “Login failed for user ‘(null)’. Reason: Not associated with a trusted SQL Server Connection”.If I change the security context of the Linked Server Connection to use a SQL login which is configured on Server B, it works without any issues.

Below picture shows what is happening:

Explanation:

The error above means that the credentials are not forwarded to the target server and hence it does not recognize the user. The target server in our scenario is Server B. To understand the reason we would need to first understand the Microsoft authentication protocols used in the scenario which are Kerberos and NTLM.

Kerberos is an authentication protocol used in networks, Active Directory that is based on the use of encrypted tickets for accessing network resources. For Kerberos authentication to work it relies on Service Principal Name (SPN). NTLM is a default authentication protocol of Windows NT 4.0 systems and earlier which Microsoft continues to support for backward compatibility.

When SQL Server cannot use Kerberos it defaults to NTLM which is a challenge/response-based authentication and the credentials cannot be forwarded. When SQL Service is started, the service account tries to register itself with an SPN and this is shown in the SQL Error Logs as below. If unsuccessful it would revert to NTLM authentication. Below error message is logged in SQL Error Log if SPN registration is unsuccessful

“The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/ServerA.dba-datascience.com ] for the SQL Server service. Windows return code: 0x2098, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.”

In such a scenario double hop connectivity (in our case Linked Server) will not be successful.

Solutio:

There is an automated way of getting this done and a manual process. I will try to discuss both below:

Automated:

  • Make sure both the servers have separate service accounts (even if they are same it might be an issue).
  • Grant the AD rights “Read servicePrincipalName & Write servicePrincipalName” to the SQL Service accounts. This is what allows SQL Server to create and remove SPN’s.
  • Restart SQL Services which causes SPN’s to be created for each instance.
  • Once SQL Services are up and SPN’s are registered successfully, you should now be able to see a “Delegation” tab on the service account properties in Active Directory.
  • In “Delegation” tab enable the option “Trust this user for delegation to any server (Kerberos Only)”

Manual:

  • Find if there are any existing incorrect SPN’s registered for the service account (like incorrect servername or domain name). You can get that by running the below command: 
>setspn -L <domainname>\<ServiceAccount_Name>
  • To register service account for default instance
>setspn -A MSSQLSvc/<FQDN_of_servername>:<port>

OR

>setspn -A MSSQLSvc/<FQDN_of_servername >
  • To register a named instance:
>setspn -A MSSQLSvc/<FQDN_of_servername>:<port>

OR

>setspn -A MSSQLSvc/<FQDN_of_servername >:<instance_name>
  • Repeat the same steps as in automated from 2nd bullet point.

NOTE: Restart the SQL Services once these steps are completed and you should see a message saying SQL Service has successfully registered SPN.

References:

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/register-a-service-principal-name-for-kerberos-connections?view=sql-server-2017

https://support.microsoft.com/en-gb/help/319723/how-to-use-kerberos-authentication-in-sql-server

https://blogs.msdn.microsoft.com/farukcelik/2008/01/02/how-to-set-up-a-kerberos-authentication-scenario-with-sql-server-linked-servers

 


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.