Say you're building the typical three-tier system and you want to use integrated security all the way through, from Internet Explorer through IIS back to SQL Server. And you'd like to have IIS use the client's credentials to talk to SQL Server, so you turn on impersonation in the web server and connect to the database.
There's a rather tricky thing that you need to know in order to make this work. Unless your SQL Server is running as SYSTEM (ugh) or Network Service, you will need to do BOTH of the following things:
a) Ensure that you use a DOMAIN account to run SQL Server (not a local account or Local Service).
b) Ensure that this domain account has an appropriate SPN assigned to it.
Of course you really need to be using domain accounts throughout the entire system (your IIS AppPool needs to be running with domain credentials as well; this includes Network Service and, ugh, SYSTEM). Your clients will need to be using domain accounts as well, and you'll need to turn on delegation for the account your IIS AppPool is using. But that is all covered pretty thouroughly in other places.
What's not covered so well is the form of the SPN. After doing some network tracing today, I noticed that the SQL client libraries go to the domain controller looking for a ticket with an SPN that takes the following form:
MSSQLSvc/foo.bar.com:1433
...assuming a connection string of
integrated security=sspi;server=foo.bar.com; ...
So apparently the SPN MUST include the port number, even though this is the default port for SQL Server! Say you were running SQL Server under a domain account of BAR\Bob. You'd need to run the following command to add an SPN to the Bob account so that Active Directory will issue tickets for Bob when asked for a ticket for MSSQLSvc/foo.bar.com:1433
setspn -A MSSQLSvc/foo.bar.com:1433 bar\bob
It also wouldn't hurt to also register an SPN without the port, in case you're using an earlier client stack that relies on the default port syntax:
setspn -A MSSQLSvc/foo.bar.com bar\bob
You should always use the full DNS name of the server in your connection string (don't assume that your client library will convert a netbios server name in your connection string to a DNS name for the SPN - my stack seems to be doing this at the moment, but nothing says yours will!), so prefer
server=foo.bar.com
to
server=foo
in your mid-tier connection strings if you want delegation to work properly. It's possible to get it to work with netbios names in SPNs, but netbios names aren't necessarily unique throughout the domain, and if you accidentally end up with a duplicate SPN, guess what? Yea, authentication fails.
Delegation can be tricky, but this particular gotcha (the port number in the SPN) is pretty painful to debug, so I hope this saves somebody some headaches. If you want to know what this whole “SPN” business really means, read the following items in my book online, in the following order:
Item 59: What is Kerberos?
Item 60: What is a service principal name (SPN)?
Item 62: What is delegation?
UPDATE (22 Aug 2006): fixed links
Posted
Jul 08 2004, 07:42 PM
by
keith-brown