SQL Server service accounts and SPNs
- by simonsabin
Service Principal Names (SPNs) are a must for kerberos
authentication which is a must when using sharepoint, reporting services and sql
server where you access one server that then needs to access another resource,
this is called the double hop. The reason this is a complex problem is that the
second hop has to be done with impersonation/delegation. For this to work there
needs to be a way for the security system to make sure that the service in the
middle is allowed to impersonate you, after all you are not giving the service
your password.
To do this you need to be using kerberos.
The following is my simple interpretation of how kerberos works. I find the
Kerberos documentation rediculously complex so the following might be sligthly
wrong but I think its close enough.
Keberos works on a ticketing system, the prinicipal is that you get a
security token from AD and then you can pass that to the service in the middle
which can then use that token to impersonate you. For that to work AD has to be
able to identify who is allowed to use the token, in this case the service
account.But how do you as a client know what service account the service in the
middle is configured with. The answer is SPNs. The SPN is the mapping between
your logical connection to the service account. One type of SPN is for the DNS
name for the server and the port. i.e. MySQL.mydomain.com and 1433. You can see
how this maps to SQL Server on that server, but how does it map to the
account.
Well it can be done in two ways, either you can have a mapping defined in
AD or AD can use a default mapping (this is something I didn't know about).
To map the SPN in AD then you have to add the SPN to the user account, this is
documented in the first link below either directly or using a tool called
SetSPN. You might say that is complex, well it is and thats why SQL Server tries
to do it for you, at start up it tries to connect to AD and set the SPN on the
account it is running as, clearly that can only happen IF SQL is running as a
domain account AND importantly it has permission to do so. By default a normal
domain user account doesn't have the correct permission, and is why so many
people have this problem. If the account is a domain admin then it will have
permission, but non of us run SQL using domain admin accounts do we.
You might also note that the SPN contains the port number (this isn't a
requirement now in sql 2008 but I won't go into that), so if you set it manually
and you are using dynamic ports (the default for a named instance) what do you
do, well every time the port changes you need to change the SPN allocated to
the account. Thats why its advised to let SQL Server register the SPN
itself.
You may also have thought, well what happens if I change my service account,
won't that lead to two accounts with the same SPN. Possibly. Having two
accounts with the same SPN is definitely a problem. Why? Well because if there
are two accounts Kerberos can't identify the exact account that the service is
running as, it could be either account, and so your security falls back to NTLM.
SETSPN is useful for finding duplicate SPNs
Reading this you will probably be thinking Oh my goodness this is really
difficult. It is however I've found today in investigating something else
that there is an easy option.
Use Network Service as your service account.
Network Service is a special account and is tied to the computer.
It appears that Network Service has the update rights to AD to set an SPN
mapping for the computer account. This then allows the SPN mapping to work. I
believe this also works for the local system account.
To get all the SPNs in your AD run the following, it could be a large file,
so you might want to restrict it to a specific OU, or CN
ldifde -d "DC=<domain>" -l servicePrincipalName -F spn.txt
You will read in the links below that you need SQL to register the SPN this
is done
how to use Kerberos
authenticaiton in SQL Server - http://support.microsoft.com/kb/319723
Using
Kerberos with SQL Server -
http://blogs.msdn.com/sql_protocols/archive/2005/10/12/479871.aspx
Understanding
Kerberos and NTLM authentication in SQL Server Connections -
http://blogs.msdn.com/sql_protocols/archive/2006/12/02/understanding-kerberos-and-ntlm-authentication-in-sql-server-connections.aspx
Summary
The only reason I personally know to use a domain account is when you can't
get kerberos to work and you want to do BULK INSERT or other network service
that requires access to a a remote server. In this case you have to resort to
using SQL authentication and the SQL Server uses its service account to access
the remote service, and thus you need a domain account. You migth need this
if using some forms of replication. I've always found Kerberos awkward to setup
and so fallen back to this domain account approach.
So in summary to get Kerberos to work try using the network service or local
system accounts.
For a great post from the Adam Saxton of the SQL
Server support team go to http://blogs.msdn.com/psssql/archive/2010/03/09/what-spn-do-i-use-and-how-does-it-get-there.aspx
