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:
Create Service Principal Names (SPNs) for the Instance of SQL Server
Test connections are using Kerberos and not NTLM
Configure Delegation permissions for
Reporting Services Service Account
Reporting Services Server
Set the Reporting Services Service Account with Impersonate Permissions
Set the Authentication Type in rsreportserver.config file
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:
Instance Name SPN
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.
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)
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.