Setting up linked servers using Windows Authentication and how to solve multiple hop problem

This post will cover a topic of connecting linked servers using Windows Authentication. Linked server is a hot topic since longer than I am able to imagine (namely SQL Server 7.0) but it needs a bit of attention to understand it properly. The focus will be on setting up linked server to another SQL Server for use of Windows Authentication.

Generally, a linked server is a server object (on machine A) that allows you to work with information from a remote data source (call it B for the sake of clarity). This scenario assumes that both database servers A and B belong to your domain. Now, in the simplest scenario you might be tempted to set up a SQL login on remote server (B) with access rights to required data and create linked server on A using security option #4 “Be made using this security context” providing newly created login and password.

Done? Done.

Wrong.

First of all, you created an enormous, gigantic security hole allowing all users of server A to use the data from server B based on access rights of SQL login. If the login has write access, you are even able to modify remote data.

Second, you anonimized access to server B – all connections from server A will use your created login. If you need to check or log access, you will lose this part of information.

You think about it and decide to correct it. There are two options for consideration:

  • Not be made – if you do not define login mapping explicitly, connection will fail. See note about login mapping below.
  • Be made using the login’s current security context – you will use current login credentials. This needs to be validated when data on server B is sensitive – you might not want this data to be available on server A through linked server even if the user has access to it on server B.

Regarding login mapping – you may provide local logins which are ONLY representing users. You cannot add groups, but specify one-to-one local-to-remote login mapping. This might require additional work to set up and maintain properly – imagine you need to provide access through linked server to 50 people and you have not only to create 50 local logins, but remember to update linked server mapping definition.

You might have already started to consider Windows Authentication. That’s a good approach, but will require additional points to consider:

  • In case of linked server permission chaining does not occur. You have to set up access to remote resources, including creating logins and mapping them to users.
  • In order for delegation to work, Active Directory service accounts for SQL Server instances must be enabled for delegation, otherwise “hop problem” will occur. It also requires that SPNs (Service Principal Names) are set up properly in Active Directory for SQL Server service account.

Let’s have an example for that. We need three SQL Servers in a domain – I am using SQL Server 2012 SP1 for this demo – but it will work with previous versions as well. My domain is called beton.test and database servers are SQL12T1, SQL12T2 and SQL12T3. Each of the database servers runs under separate domain account:

  • SQL12T1 under BETON\sql12t1srv
  • SQL12T2 under BETON\sql12t2srv
  • SQL12T3 under BETON\sql12t3srv

As a preparation I also created following objects:

  • On SQL12T1:
    • a database named BazaS1
    • a table in database BazaS1 called B1T1
    • a linked server to SQL12T2 without mappings and security settings set to “Be made using the login’s current security context”.
  • On SQL12T2:
    • a database named BazaS2
    • a table in database BazaS2 called B2T1
    • a linked server to SQL12T3 without mappings and security settings set to “Be made using the login’s current security context”.
    • a view in database BazaS2 called S3LinkedView which joins data from SQL12T2 and SQL12T3
  • On SQL12T3:
    • a database named BazaS3
    • a table in database BazaS3 called B3T1
  • I also need a separate Active Directory group that will be a db_owner of each databases (SQL Server Group 1 for SQL12T1, SQL Server Group 2 for SQL12T2 and SQL Server Group 3 for SQL12T3). I do it for the sake of simplicity – in the real world you might assign more strict permission to the groups. As I’m connecting to SQLT2T1 I also have to grant access for Group 1 to databases on SQL12T2 and SQL12T3 as well as for Group 2 to databases on SQL12T3.

The goal is to retrieve data from all three servers by executing a query on SQL12T1 that will join data from table in BazaS1 with view in BazaS2 on SQL12T2 (that will return processed data from both SQL12T2 and SQL12T3). Here are scripts to create databases with some test data (click to expand script source code):

USE [master]
GO

CREATE DATABASE [BazaS1]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'BazaS1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BazaS1.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'BazaS1_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BazaS1_log.ldf' , SIZE = 1536KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [BazaS1] SET COMPATIBILITY_LEVEL = 110
GO

USE [BazaS1]
GO

CREATE USER [BETON\SQL Server Group 1]
GO
ALTER ROLE [db_owner] ADD MEMBER [BETON\SQL Server Group 1]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[B1T1](
	[a] [int] NULL,
	[f] [float] NULL
) ON [PRIMARY]

GO
INSERT [dbo].[B1T1] ([a], [f]) VALUES (1, 1)
GO
INSERT [dbo].[B1T1] ([a], [f]) VALUES (2, 5)
GO
INSERT [dbo].[B1T1] ([a], [f]) VALUES (3, -2.5)
GO

USE [master]
GO

/****** Object:  LinkedServer [SQL12T2]    Script Date: 9/3/2013 6:15:27 AM ******/
EXEC master.dbo.sp_addlinkedserver @server = N'SQL12T2', @srvproduct=N'SQL Server'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SQL12T2',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

GO

EXEC master.dbo.sp_serveroption @server=N'SQL12T2', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SQL12T2', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'SQL12T2', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SQL12T2', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SQL12T2', @optname=N'rpc', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SQL12T2', @optname=N'rpc out', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SQL12T2', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SQL12T2', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'SQL12T2', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'SQL12T2', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SQL12T2', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'SQL12T2', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'SQL12T2', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
USE [master]
GO

CREATE DATABASE [BazaS2]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'BazaS2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BazaS2.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'BazaS2_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BazaS2_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [BazaS2] SET COMPATIBILITY_LEVEL = 110
GO

USE [BazaS2]
GO

CREATE USER [BETON\SQL Server Group 1] FOR LOGIN [BETON\SQL Server Group 1]
GO

CREATE USER [BETON\SQL Server Group 2] FOR LOGIN [BETON\SQL Server Group 2]
GO
ALTER ROLE [db_owner] ADD MEMBER [BETON\SQL Server Group 1]
GO
ALTER ROLE [db_owner] ADD MEMBER [BETON\SQL Server Group 2]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[B2T1](
	[a] [int] NULL,
	[s] [varchar](50) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create view [dbo].[S3LinkedView] as
select t.*, t2.d from B2T1 t inner join [SQL12T3].BazaS3.dbo.B3T1 t2 on t.a = t2.a

GO
INSERT [dbo].[B2T1] ([a], [s]) VALUES (1, N'horror')
GO
INSERT [dbo].[B2T1] ([a], [s]) VALUES (2, N'error')
GO
INSERT [dbo].[B2T1] ([a], [s]) VALUES (4, N'terror')
GO

USE [master]
GO

/****** Object:  LinkedServer [SQL12T3]    Script Date: 9/3/2013 6:20:40 AM ******/
EXEC master.dbo.sp_addlinkedserver @server = N'SQL12T3', @srvproduct=N'SQL Server'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SQL12T3',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

GO

EXEC master.dbo.sp_serveroption @server=N'SQL12T3', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SQL12T3', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'SQL12T3', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SQL12T3', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SQL12T3', @optname=N'rpc', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SQL12T3', @optname=N'rpc out', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SQL12T3', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SQL12T3', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'SQL12T3', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'SQL12T3', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SQL12T3', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'SQL12T3', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'SQL12T3', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
USE [master]
GO

/****** Object:  Database [BazaS3]    Script Date: 9/3/2013 6:22:06 AM ******/
CREATE DATABASE [BazaS3]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'BazaS3', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BazaS3.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'BazaS3_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BazaS3_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [BazaS3] SET COMPATIBILITY_LEVEL = 110
GO

USE [BazaS3]
GO

CREATE USER [BETON\SQL Server Group 1] FOR LOGIN [BETON\SQL Server Group 1]
GO
CREATE USER [BETON\SQL Server Group 3] FOR LOGIN [BETON\SQL Server Group 3]
GO

ALTER ROLE [db_owner] ADD MEMBER [BETON\SQL Server Group 1]
GO
ALTER ROLE [db_owner] ADD MEMBER [BETON\SQL Server Group 3]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[B3T1](
	[a] [int] NULL,
	[d] [smalldatetime] NULL
) ON [PRIMARY]

GO
INSERT [dbo].[B3T1] ([a], [d]) VALUES (1, CAST(0xA1D40561 AS SmallDateTime))
GO
INSERT [dbo].[B3T1] ([a], [d]) VALUES (2, CAST(0xA1D30561 AS SmallDateTime))
GO
INSERT [dbo].[B3T1] ([a], [d]) VALUES (5, CAST(0xE92A0561 AS SmallDateTime))
GO

A little explanation – each of the tables will contain three records – one holds a floating-point value, second – a string and third – a date/time value. Each of has a candidate key of a on which a join will be performed. The view returns two rows joining a string (from SQL12T2.BazaS2.dbo.B2T1 table) with datetime (from SQL12T3.BazaS3.dbo.B3T1 table).
I am going to execute query below which is supposed to return three rows:

select t1.a, t1.f, t2.s, t2.d
from BazaS1.dbo.B1T1 t1
left join [SQL12T2].BazaS2.dbo.S3LinkedView t2
on t1.a = t2.a

The expected result is as follows:

linked_server_results

 

 

 

 

After going through initial setup – creating AD accounts/groups, creating databases/tables, creating logins and setting up permissions – the most it’s possible to be done on SQL12T1 is to join a table BazaS1.B1T1 on SQL12T1 with BazaS2.B2T1 on SQL12T2. However, this might fail if the service accounts of SQL Servers are not set properly in AD. To solve this, first check the SPN using

setspn -L BETON\sql12t1srv (or whatever service account is used on SQL12T1)

This command should get a list of registered SPNs for the service account. It should contain SPN for SQL Server service in format:

MSSQLSVC/Server_FQDN:port

If it’s missing, you should set correct SPN using following syntax:

setspn -S MSSQLSVC/Server_FQDN:port service_account

In this case this will be:

setspn -S MSSQLSVC/sql12t1.beton.test:1433 BETON\sql12t1srv

Important – after making changes to SPNs respective services must be restarted in order to take effect. This has to be taken into consideration while planning changes to production environments.

After SPNs are set properly linked servers should start working, however it’s still not possible to do a second hop while querying a view on SQL12T2 from SQL12T1 (remember that a view joins data from SQL12T3). The error message will say that login failed for “NT AUTHORITY\ANONYMOUS LOGON”. The solution for that is to set up a delegation in Active Directory for SQL Server service accounts – see the screenshot for details:
delegation_settings

 

 

 

 

 

 

 

The idea behind this is simple – you delegate from sql12t1srv account to SQL Server service on SQL12T2 and from sql12t2srv account to SQL Server service on SQL12T3. After that restart the services once again so that the delegation kicks in and finally it starts working, the query retrieves expected results.

Wrapping this all up – we discussed a few points regarding linked servers, made a point about Windows Authentication and then had an example how to configure security properly in order to fix double hop problem. As usual, I await your questions and suggestions.

Advertisements