Kerberos Authentication to your SQL Server Instance

Setting up a SQL Server Instance can be achieved relatively simply with following the bouncing ball and either creating a Default or Named Instance on your Workstation, Laptop or Server. If your requirements are a stand-alone instance and it does not need to be accessed from an application residing on another server then more than likely you will have no further problems with authentication and accessing your data. However more often than not this simple scenario is not what is required and if your reading this article you are looking at a setup that is a little more complicated. To give you a basic idea of what we are going to be talking about here in this article is being able to connect and use the data in your database on Server1 from Server2.

Server1 – Has an instance of SQL Server (Default or Named Instance) that has a couple of databases available for use.

Server2 – Has an instance of SQL Server Reporting Services (Default or Named Instance).

Our scenario is that we need to be able to connect from Server2 through to Server1, and perform retrieve data to populate our reports. Our Instance on Server1 is configured for Windows Authentication Only, so we are relying on gaining access to the instance and subsequent databases using a windows domain account. To achieve this there are two security protocols NTLM and Kerberos. Both of these protocols are supported with Kerberos being the preferred method as it provides greater security. Kerberos comes into its own when there is a requirement to pass through credentials to be able to access data on a remote server. The data sources for the reports are pointing at a database on Server1 and you are connecting to the SSRS server from your workstation or laptop. The data source for the SSRS report is configured to use the account of the person running the report. Under this scenario if Kerberos is not configured you will run into issues and will not be able to retrieve the data.

To configure Kerberos in your environment there are some steps that need to be undertaken:

1. Create Service Principal Names (SPNs) for the Instance of SQL Server
2. Test connections are using Kerberos and not NTLM
3. Configure Delegation permissions for
   1. Reporting Services Service Account
   2. Reporting Services Server
4. Set the Reporting Services Service Account with Impersonate Permissions
5. Set the Authentication Type in rsreportserver.config file
6. Test Reporting Services Report connections

Create SQL Server Instance SPNs

There are 2 SPNs that you will want to create for your Instance of SQL Server that is holding your data to be used by a SSRS Report:

1. Instance Name SPN
2. TCP/IP Port Number SPN

To be able to create the SPNs you will need elevated permissions. Either the service account has been configured with permissions to create the SPNs or you are logged in with an account that has Domain Admin Permissions. These SPN’s depending on your version of Windows Server can be created using the following syntax:

• SetSPN –S MSSQLSvc/[Server1.FQDN]:[InstanceName] [Domain][ServiceAccountName]
• SetSPN –S MSSQLSvc/[Server1.FQDN]:[PortNumber] [Domain][ServiceAccountName]

To confirm the creation you are able to use the SetSPN –L Domain][ServiceAccountName]

There potentially will be a delay for this setting to take effect in your environment.

Test Connections are using Kerberos

From your workstation or laptop or second server that has SQL Server Management Studio installed, Create a connection to the instance of SQL Server Server on Server1 that the SPNs have just been created for. Open a new query window and run the following statement:

SELECT auth_scheme 
FROM sys.dm_exec_connections 
WHERE session_id = @@SPID;

A result of Kerberos indicates that your setup so far is working.

Configure Delegation

To configure delegation you will require elevated permissions within your Active Directory. Opening up Active Directory Users And Computers find and navigate to SQL Server Reporting Services Service Account. On the Delegation Tab Choose the appropriate option that meets your environment. (The preferred option is to “Trust this user for delegation to specified services only” and enter the appropriate servers that you want to configure the ability to delegate to)

Find and navigate to Server2. Right Click and select the properties. Navigate to the Delegation Tab. Under this tab you have a couple of options. Choose the appropriate option that meets your environment. (The preferred option is to “Trust this computer for delegation to specified services only” and enter the appropriate servers that you want to configure the ability to delegate to)

Impersonate Permissions

The next step is to allow the Reporting Services Service account to have the ability to Impersonate a client after authentication. This is a local server security policy that needs to be set. Find and open “Local Security Policy” snap-in on Server2 | Expand Local Policies and select User Rights Assignment. Scroll down until you find Impersonate a client after authentication. Open this up and add your Reporting Services Service Account.

RSReportServer.Config Authentication Type

Navigate to %Program FilesMicrosoft SQL ServerMSRS12.MSSQLSERVERReporting ServicesReportServer  (for SQL Server 2014) and edit the RSReportServer.config file. Locate and ensure you are using RSWindowsNegotiate or RSWindowsKerberos based on your environment requirements. To understand these values more you can read the MSDN RSReportServer Configuration File.

Test your Reports

Now all that is left is for you to test your report that it connects to the datasource on Server1 and returns the report populate without any connection errors.