Kerberos troubles with SSRS/SQL Server

Question

I'm trying to resolve what I suspect is a Kerberos issue in an installation.

In this installation, it is configured like this:

• SQL1 - Running SQL 2005, database engine service running as "Local System"
• SQL2 - Running SQL 2012, database engine service running as "NT Service\MSSQLSERVER"
• SSRS1 - Running SSRS 2008 R2, service running as "Network Service"

I can create a SSRS Data Source on SSRS1 using "Windows Integrated Security", that connects to a database on SQL1.

When I try to create a SSRS Data Source on SSRS1 using "Windows Integrated Security" that connects to SQL2, I get the dreaded "Login failed for user NT AUTHORITY\ANONYMOUS LOGON".

In AD, all three servers are trusted for delegation.

There are SPN's set up for each of the three servers, using the hostname, for example:

setspn -A MSSQLSvc/SQL2 SQL2

I suppose ideally SQL2 should be running in a domain account but I'm trying to avoid disruption. But since SSRS1 can connect to SQL1 which is running as "Local System", why wouldn't it be able to connect to SQL2 running as "NT Service\MSSQLSERVER"?

Answer 1

Hello,

Have you checked for duplicate SPNs ?

I would also look what happen when starting the SQL2 service as local system, if it fails too it may mean the spn registration is wrong for local machine.

for more troubleshooting try to do the following:

1. look on the client machine event log - in this case SSRS server is the client - you may see the actual reason for the Kerberos failure.

2. stop and start the SQL Server service on SQL2 - this may sort the issue out...

3. look on SQL2 SQL server recent errorlog file, when service is starting it should register SPNs, check if last restart success to register the SPN correctly- any message on this may give us more hints...

HTH,
Noam

Answer 2

No duplicate SPN's.

But I'll be darned - I took your suggestion of restarting the SQL Server service and took it a bit further - I rebooted the SQL Server (SQL2).

And after the reboot... magically the SSRS data source started connecting with Windows Integrated Security!

Thanks!