Marios Philippopoulos (3/24/2008)
rbarryyoung (3/24/2008)
Marios: Glad I could help. I lost many days to this problem the first time that I tripped over it.Thank you!
This problem rears its ugly head in many different ways. It occurs when I attempt to open an SSRS report on my local workstation that connects through Windows auth. to a production instance and surveys user-database permissions. To resolve I need to create a special SQL login with sysadmin permissions on the instance! The alternative would be to map that login to EVERY single user database with db_datareader permissions, something hard to pull off logistically (and messy).
So at the moment I'm stuck with a less than ideal scenario, and I want to try the Kerberos-auth option...
This is what you're effectively doing and why you see the double hop issue:
client (through the web report) -> initial SQL Server (as Windows user) -> linked SQL Server (as Windows user)
That's the double-hop issue. Here's what would have to be configured to make Kerberos work in this situation (and it'll require a domain admin/forest admin to do):
- Service Principal Names (SPNs) properly configured for the initial SQL Server instance (and if this is on a cluster, the Network name will have to be configured for Kerberos authentication)
- Service Principal Names (SPNs) properly configured for the linked SQL Server instance (see above note about cluster config)
- Constrained delegation (I'm assuming Windows 2003/2008 AD) for the service account for the first SQL Server instance permitting it to connect and impersonate to the second SQL Server instance
- If in a multiple domain environment, where you're crossing domains in any way, using fully qualified domain names (FQDNs) in all computer name references (instead of just mysqlserver, mysqlserver.thedomain.net or what have you).
There's a great document which details how to configure and troubleshoot Kerberos delegation related problems here (it's essential reading for CRM 3.0 configurations where all the tiers are on separate servers):
Troubleshooting Kerberos Delegation
K. Brian Kelley
@kbriankelley