Jefferson Elias

How to link two SQL Server instances with Kerberos

July 5, 2018 by

Introduction

Intended audience

This document is intended for application developers, database administrators and system administrator who plan to create linked servers between instances of Microsoft SQL Server using Windows Authentication.

Context

Let’s say we have two separate SQL Server boxes and their service account are Active Directory accounts. We will refer to one of these boxes as instance A and instance B. Assuming we are willing to be able to be connected on instance A and run a T-SQL query which uses data from instance B without explicitly opening two connections in an application.

This is exactly the purpose of Linked Server objects! Most of the time, such an object is created using credentials, but it’s not the only way to do it. As we will discuss in one of following sections, there are four authentication options when we create a Linked Server object. One of them is the “identity forwarding”, which means that the identity of an authenticated user U1 connected to an instance A is used by this instance A in order to connect to an instance B and create a link between instances A and B as U1.

The explanation above is summarized below:

If SQL Server authentication is used for U1 on instance A, it means that a SQL Login U1 must be created on Instance B, with the exact same password.

If Windows authentication is used, it’s not so straight forward: there are a few configuration tasks to be performed and we will discuss them in following sections.

In this article, we will first review the different steps to follow in order to create a linked server to another SQL Server instance using SQL Server Management Studio. We will then test the linked server connection using the “identity forwarding” settings and see the error message that should come up. After that, we will use a tool that will help us in the configuration of such a linked server object.

Creating a linked server using SSMS

First, open SQL Server Management Studio and connect to the instance of your choice. Then, go down to “Server Objects” and right-click on “Linked Server” node.

A contextual menu should appear. Click on “New Linked Server…”.

This will open the Linked Server Creation Dialog:

Here you have two options:

  1. Select “SQL Server” as Server Type and provide a network name for the target SQL Server instance
  2. Provide the name you want for the Linked Server object and specify a network name for target SQL Server instance in Data source text box.

No matter the option you chose, go to the Security Page of the dialog.

This dialog is divided into two parts. The first one is a list of local to remote logins mapping. The second part tells to SQL Server how to use the first part:

  • The first option can be summarized as: if a local login attempts to use the linked server and it’s not kind of whitelisted in the first part, then this local login cannot use the linked server.
  • The second option tells SQL Server to connect anonymously to target SQL Server instance for logins that are not listed in first part of the dialog.
  • The third option tells SQL Server to use the security context of the authenticated login to contact remote instance. It’s the one we will use for the aim of this article.
  • The last (and less secure) option is to define credentials for any login that is not listed in the local to remote mapping list.

So, for next step, we will let the first part of the dialog empty and select the third option.

If we hit the “OK” button, we may get following error message:

The “Login failed for user NT AUTHORITY\ANONYMOUS LOGON” is a pretty common error message and you can find a lot of requests for help on the internet. Unfortunately, during my researches, I did not find a single thread with the whole solution to my problem. That’s also the reason why I wrote this article.

Now, let’s try to get it working!

There are mainly two configuration issues: the first one is the Server Principal Name and the second one is the Identity Delegation permission. There is a tool that will check these two aspects and it’s the subject of next section.

Useful tool: Kerberos Configuration Manager for SQL Server (KCM)

Kerberos Configuration Manager for SQL Server is a tool designed by Microsoft and available under the following link.

Installation

Once you downloaded tool installer, run it and you will get following dialog:

Click on “Next” button.

Set the installation location, if you want one that is different from default. Then, click on “Next” button.

You’ll get on a confirmation panel. Click on “Next” button.

Then, a license agreement appears. Accept it and click on “Next” button.

And the installer begins and completes very fastly. The only step that remains is to click on “Close” button.

Solving linked server connectivity issue with KCM

By default, the KCM tool is located in the folder:

C:\Program Files\Microsoft\Kerberos Configuration Manager for SQL Server

There are three files in this folder:

Double-click on KerberosConfigMgr.exe.

Here is the default view for Kerberos Configuration Manager tool:

Click on “Connect” menu. It will open following dialog:

Specify the information required to connect and click on “Connect” button.

As soon as we click on the “Connect” button, the tool will start collecting data using WMI providers and when its collection completes, we get a summary view, which starts by a server and user summary:

Then, there are the two tabs of interest for our problem: SPN (for Server Principal Name) and delegation. Let’s first have a look at SPN tab.

As you can see in the image above, I can see directly that the required Server Principal Names are missing. But you can also see that there are two possibilities for us: either generate the script for fixing the problem or actually fix it.

Here is the content of a generated script:

It’s very handy in big organizations because you can provide those scripts to your system/domain administrators and ask him to run it.

The last tab in the tab pane is “Delegation”. In short, for Kerberos authentication and some other authentication protocols, the SQL Server service account should be trusted and allowed to act on behalf of another user (which is the already authenticated user in our case).

Personally and as an example, I got following results, saying that no delegation is not configured.

To resolve this, we will need to connect to the Active Directory management console, find the AD user corresponding to the SQL Server service account for the instance from which connection will be established. Once you got it, open its properties.

There is a “Delegation tab” and you can either choose to trust the account no matter the service or to trust the user for a list of specified services, as you can see below:

Note

  • Always prefer security by default, so you should select “Trust this user for delegation to specified services only” and add the SQL Server service account.
  • This part could be done by a system/domain administrator instead of a DBA.

If your Active Directory is composed of multiple nodes, it will take a while to replicate this setting. The easier way to check is to restart SQL Server.

As soon as our settings are done for delegation and for SPN, we should rerun KCM tool and find this as a result for SPN:


And delegation tab should be showing something else than “None”.

If everything has been done correctly, we should now be able to right-click on the linked server we created and test connection:

And we should get:

References



Jefferson Elias
Latest posts by Jefferson Elias (see all)

Related posts:

  1. Azure SQL Server auto-failover groups
  2. SQL Server SET Options that Affect the Query Result – SET CONCAT_NULL_YIELDS_NULL, SET NUMERIC_ROUNDABORT, SET QUOTED_IDENTIFIER, SET NOCOUNT, SET XACT_ABORT
  3. Top SQL string functions in SQL Server 2017
  4. SQL Server 2017: Columnstore Indexes and Trivial Plan
  5. How to monitor object space growth in SQL Server
Kerberos

About Jefferson Elias

Living in Belgium, I obtained a master degree in Computer Sciences in 2011 at the University of Liege. I'm one of the rare guys out there who started to work as a DBA immediately after his graduation. So, I work at the university hospital of Liege since 2011. Initially involved in Oracle Database administration (which are still under my charge), I had the opportunity to learn and manage SQL Server instances in 2013. Since 2013, I've learned a lot about SQL Server in administration and development. I like the job of DBA because you need to have a general knowledge in every field of IT. That's the reason why I won't stop learning (and share) the products of my learnings. View all posts by Jefferson Elias

168 Views