Kerberos authentication in Reporting Services
• Symptom
When you run a report of Reporting Services, you get the following error message.
An error has
occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'DataSource'. (rsErrorOpeningConnection)
The connection
either timed out or was lost.
Unable to read
data from the transport connection: An existing connection was forcibly closed
by the remote host.
An existing
connection was forcibly closed by the remote host
If the report is run from the report server machine the error doesn't come up.
The credentials used in the datasource of the reports are Windows Integrated. If we change the connection type to use Stored Credentials the error is not reproduced anymore. This is a hint to know that we are facing a Kerberos issue.
Environment:
1 client machine from which the error is got.
1 report server
1 SQL Server + SSAS server
• Cause
When the
datasource use Windows Integrated if we run the report from a client machine,
the user credentials needs to do a double hop: from the client machine to the
report server and from the report server to the database server.
For this
environment types, Reporting Services needs to be configured for Kerberos
authentication so that the user credentials arrive to database server.
This post
describes the steps to follow to configure Kerberos authentication in SSRS.
• Configuration
1. The Reporting Services configuration file rsreportserver.config (located by default at C:\Program
Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer)
we need to modify the authentication section and add RSWindowsNegotiate.
<AuthenticationTypes>
<RSWindowsNegotiate/>
</AuthenticationTypes>
After save the changes the SSRS service needs to be restarted.
2. Configure the SPNs against the associated service account (SSRS,SQL SERVER y SSAS) using the following command line:
setspn -s SPN domain\serviceaccount
* The command setspn -s avoids to register a SPN that is already registered for that service account and so, a duplicated SPNs cannot existed.
* To be able to register a SPN, you need domain administrator permission.
a. SPNs for Reporting Services (against SSRS service account).
HTTP/<hostname>
HTTP/<hostname.domain>
Where hostname is the one used in the Reporting Services URLs sites:
b. SPNs for SQL SERVER and SSAS
(Referred to user database defined on the report data source and the instance that hosts it)
DEFAULT INSTANCES
SQL SERVER (against SQL SERVER service account).
If MSSQLSERVER instance listens at the deault port:
MSSQLSvc/<serverhostname.domain>:1433
If the default instance listens at a different port from TCP:
MSSQLSvc/<serverhostname.domain>
SSAS (against SSAS service account).
MSOLAPSvc.3/serverHostName.domain
MSOLAPSvc.3/serverHostName
NAMED INSTANCES
SQL SERVER (against SQL SERVER service account).
Using TCP port:
MSSQLSvc/<serverhostname.domain>:port
Using a different port from TCP:
MSSQLSvc/<serverhostname.domain>:InstanceName
SSAS (against SSAS service account).
MSOLAPSvc.3/<serverHostName.domain>:InstanceName
MSOLAPSvc.3/<serverHostName> :instanceName
For named instance we need to register the SPNs for SQL Browser service:
MOLAPDisco.3/serverHostName
MOLAPDisco.3/serverHostName.domain
3. Enable delegation for the machine accounts and service accounts involved in the environment from the Active Directory.
a. Check that "Account is sensitive and cannot be delegated" option is unchecked for the every account. (This option is located at the "Account" tab of the account properties).
At "Delegation" tab you can configure the following options for delegation:
b. Full delegation: the account is enabled for delegation for all services.
c. Constrained delegation: the account is enabled for delegation for the services you set in this tab.
More information :
Register a Service Principal Name for Kerberos Connections
https://msdn.microsoft.com/en-us/library/ms191153.aspx
How to configure SQL Server 2008 Analysis Services and SQL Server 2005 Analysis Services to use Kerberos authentication
(This article applies to SSAS 2008 R2)
https://support.microsoft.com/kb/917409
If after the configuration above the error continue...
1. Check that no duplicated SPNs exists for any service account. Please use setspn –x to do this checking.
If any duplicated SPNs exists please delete it with the following command line:
Setspn –d SPN
domain\serviceaccount
2. If at any moment, before the Kerberos configuration, you have changed the service account of any service involved in this environment, ensure you did it through the proper configuration tool:
* Reporting Services Configuration Manager
* SQL Server Configuration Manager.
Otherwise the service account modified won’t have been updated properly.
3. If steps 1 and 2 are checked, you can enable the verbose logging for Kerberos which will give us detailed information of the issue. You can see then these logs in the system event log.
Do revoke the changes done for configuring Kerberos logging as soon as you have reproduced the issue to avoid have any impact on the server due to high logging.
How to enable Kerberos event logging