Linked Server – Part 2

Introduction

Some days back I’ve written “Linked server part 1” where we have discussed about configuring linked server. In this article I’m going to cover authentication protocols between linked server and the possible errors.

Before we start let’s have a brief description about connection protocols & authentication mechanism

NTLM

Challenge | Response Mechanism

• When a user tries to login, client will send the username to the server
• Upon receiving the username server will generate a challenge and sends back
• After receiving the challenge client will encrypt the challenge using user’s password and sends back to server
• If the user account is local account then server will check the same on Security Account manager
• If the user account is a domain account, then server will redirect this to the domain controller for validating and retrieving group policy and user account, once done it provide access token and establish the connection

Kerberos

Trust third party scheme

• Provides a mechanism for mutual authentication
• Has three main heads a.) KDC (Key distribution center),KDC is installed as part of domain controller installation b.) client user c.) server with desired service to access
• KDC performs to operations, a.) Authentication service (AS) & b.) Ticket granting service (TGS)
• When a user logs on, it requests for TGT (Ticket granting ticket) from AS (Authentication service) in the domain
• If the user wants to access network, it will use the TGT to authenticator with valid SPN on the target server and contact TGS to grant a session
• Upon validating the authenticator it allows access
• The key factor that makes Kerberos authentication successful is the valid DNS functionality on the network.

What is SPN?

Service Principal Name(SPNs) are unique identifiers for services running on servers. Each service that will use Kerberos authentication needs to have an SPN set for it so that clients can identify the service on the network. It is registered in Active Directory under either a computer account or a user account.

An SPN for SQL Server is composed of the following elements:

• ServiceClass: This identifies the general class of service. This is always MSSQLSvc for SQL Server.
• Host: This is the fully qualified domain name DNS of the computer that is running SQL Server.
• Port: This is the port number that the service is listening on.eg: MSSQLSvc/sagarsys.home.mshome.net:1433

SQL Server would automatically register SPN during start up if:

• Your sql server running under LocalSystem/Network Service/Domain admin user account.TCP/IP protocol is enabled.
• Otherwise, you need to manually register SPN if forcing Kerberos authentication.

To check SPN for SQL Server, you can download the below setspn.exe utility from MS

http://www.microsoft.com/downloads/details.aspx?FamilyID=5fd831fd-ab77-46a3-9cfe-ff01d29e5c46&DisplayLang=en

use “setspn -L ” or “setspn -L ” command to find out the SPN, if you couldn’t find a valid SPN the use “setspn -A ” command to set a valid SPN for sql server

In which condition does Kerberos or NTLM authentication is chosen?

If you are making TCPIP connection, SQL driver on the client tries to resolve the fully qulified DNS name of the server that is running SQL, and then format the SQL specific SPN, present it to SPNEGO (SP Negotiate), later SPNEGO would choose NTLM/Kerberos depends on whether it can validate the SPN in KDC, the behavior is different from OS to OS. If you are making NP connection, SQL driver generate blank SPN and force NTLM authentication.

DOUBLE HOP & Linked Server

You can check the below table for linked server configuration and at what case it fails. You can see condition 6 fails since both of the connections are through NTLM  authentication and you will receive the error below

“Login Failed for user ‘NT AuthorityANONYMOUS’ LOGON”

In this scenario, client make tcp connection, and it is most likely running under LocalSystem account, and there is no SPN registered for SQL instance, hence, NTLM is used, however, LocalSystem account inherits from System Context instead of a true user-based context, thus, failed as ‘ANONYMOUS LOGON’.

Con-dition	SQL Authen-tication Mode	Query	Linked Server Config	Hop	Authen-tication Protocol	Will linked server work?	Diagram
1	SQL Windows	Local	NA	Single	NTLM Kerberos	NA
2	SQL	Distributed	SQL (Be made using this security context)	NA	NA	Yes
3	Windows	Distributed	SQL (Be made using this security context)	Single	NTLM Kerberos	Yes
4	SQL	Distributed	Windows (Other security option except the one shown above)	Single	NTLM Kerberos	Yes
5	Windows	Distributed	Windows (Other security option except the one shown above)	Double	Kerberos	Yes
6	Windows	Distributed	Windows (Other security option except the one shown above)	Double	NTLM	No

Why condition 6 fails?

Condition 6 fails because both the connection are using NTLM authentication. As we already discussed above when a client machine use NTLM authentication it goes on challenge – response mechanism and hence server needs to send the challenge to client.

Check the step by step process for condition 6

• Client connects to remote server (single hop) using NTLM and it succeeded, because the remote server will send the challenge to the client and client responds
• The problem comes here, when you execute a distributed query the client needs to connect to remote server and then to linked server and then the linked server needs to send the challenge to client, but the linked server will send the challenge to remote server (single hop) and the challenge is not travelled to client and hence you will receiving the above error and this is called double hop.

At what condition does it take NTLM authentication?

In this case NTLM authentication is picked up either of the following reason

• Server listening on TCPIP & SPN not set properly for SQL Service
• Server listening on NP

How can we overcome this condition?

• Set SPN properly for SQL Service hence it will take Kerberos authentication, else
• Allow TCP IP protocol for communication, else
• Change the security option to SQL for linked server hence only one hop will be there

Conclusion:

NTLM authentication protocol is designed for NT Win2k machines and this won’t support double hop. Thus a SPN setting is also an important check while configuring linked server to avoid this problem.