Set up delegation trust for domain user

Question

I have a domain user account (in mydomain.com, let's say) that is set to be the logon account for numerous instances of SQL Server 2005 on multiple machines. I want to have this domain account (not the machine accounts) trusted for delegation, primarily to do 2 hop authentication between the SQL servers via linked servers. The linked server security would be configured to use the context of the existing connection and all SQL Server instances are running under the context of said domain account.

For argument sake, this will be the instance names of the SQL instances: svra (default sql instance) svra\inst2 (named sql instance) svrb (default sql instance)

I think, then, the SPNs that I want to register in Active Directory would be MSSQLSvc/svra.mydomain.com:1433 MSSQLSvc/svrb.mydomain.com:1433

I don't need a 3rd for the named instance on svra, do I?

Can someone confirm that that is the correct format for the SPN's? Also, that would allow IIS running under the same domain account do 2 hop authentication to the SQL instances, correct?

Solution 1

Generally you need to register both the FQDN SPN and the Netbios name SPN: MSSQLSvc/svra.mydomain.com:1433 AND MSSQLSvc/svra:1433

For the secondary instance (inst2) you need an SPN for that instance's TCP port MSSQLSvc/svra:1500 (for example)

Also, if the SQL Server service process runs under a domain account you need to specify that as well, eg

MSSQLSvc/svra.mydomain.com:1433

Note that if you run the SQL Server service process as LOCALSYSTEM, these SPNs are created automatically.

Finally, you need to set the "Trusted for delegation" flag on the IIS server's computer account in the domain.

Solution 2

This doesn't cover the delegation portion, but it covers setting up authentication and getting the SPNs right:

Configuring Kerberos Authentication

Solution 3

You need an SPN per instance, and the port at the end of the SPN designates the instance on multi-instance machines. In some environments you may need to have an SPN for both the netbios and the FQDN. The SPN must belong to the current service account and not to any other account. See KB 811889 How to troubleshoot the "Cannot generate SSPI context" error message

Also, it goes against security best practices to use the same account for more than one service.

Comments

[squillman]

Great, thanks! re: IIS - I would still need to set the flag on the ISS server's machine account even though IIS is running as the aforementioned domain account?

[Kevin Dente]

Sorry, I didn't understand that part of your config. I don't think you need to enable delegation on the machine if IIS is running as the user, but I've never actually tried that config. Also, be aware that you may need to register an IIS SPN in that case.

[squillman]

Cool, thanks. That helps a lot. The IIS portion is of secondary concern. The delegation for linked server connections is key here.

[Ryan Fisher]

IMPORTANT NOTE: Make sure that you check for duplicate SPNs when you set this up. If your SQL server was running as LocalSystem then the computer account for srva (SRVA$) will have the SPN assigned to it already. You must delete this as Kerberos auth will fail if two accounts have the same SPN assigned.