Sysadmin Blog Just another IT Blog

SQL SPNs can really bite you in the you know what

I was moving some databases from local SQL servers to one remote SQL server. The new Remote box is a SQL 2008 R2 box and I do have to say I like SQL 2008 and R2 a lot. Anyway I was moving a SCCM 2007 R2 SP2 Site database and the VMM 2008 R2 database to this server and nothing was quite working right. It for some reason was trying to authenticate with NT Anonymous logon which I of course didn't wnat to open up my database to everybody and their brother. So i researched a bit and found this article:

http://poseidom.wordpress.com/2007/12/16/set-spn-for-sql-2005-sccm-remote-sql-fix/

I know it is for SQL 2005 however it works on SQL 2008 and R2 just as well and I would actually say you should do that with every SQL server, that uses a domain service account, you got because sooner or later you will run into that problem.

I actually did the Method 2 on his blog because i might want some more instances and i don't want to worry about SPNs any more! (also for teh 2nd Method you don't need the port numbers)

Method 1: The “Right” way

1. Install the Windows 2003 support tools somewhere on a machine in the domain
2. Login as a Domain Admin
3. Run  setspn -A MSSQLSvc/<FQDN> <SQL_Service_Account> Note YOU MUST USE THE FQDN
4. Run  setspn -A MSSQLSvc/<FQDN>:<Port> <SQL_Service_Account>  Note YOU MUST USE THE FQDN, and the most common port is 1443
5. Run setspn -L <SQL_Service_Account> validate that “servicePrincipalName:” has been set like you expect
6. Restart the SQL server after AD replication has completed
7. Run the following query on the SQL server; this MUST return KERBEROS:
   select auth_scheme from sys.dm_exec_connections where session_id=@@spid

Method 2: The “easy” way

In adsiedit grant the service account the ability to write the servicePrincipalName to “SELF”
Taken from: http://support.microsoft.com/kb/319723

• 1. Click Start, click Run, type Adsiedit.msc, and then click OK.
     • Note The ADSIEdit tool is included in the Windows Support Tools. To obtain the Windows Support Tools, visit the following Microsoft Web site: http://www.microsoft.com/downloads/details.aspx?familyid=6EC50B78-8BE1-4E81-B3BE-4E7AC4F0912D&displaylang=en
  2. In the ADSI Edit snap-in, expand Domain [DomainName], expand DC= RootDomainName, expand CN=Users, right-click CN= AccountName , and then click Properties.
     • DomainName is a placeholder for the name of the domain.
     • RootDomainName is a placeholder for the name of the root domain.
     • AccountName is a placeholder for the account that you specify to start the SQL Server service.
     • If you specify the Local System account to start the SQL Server service, AccountName is a placeholder for the account that you use to log on to Microsoft Windows.
     • If you specify a domain user account to start the SQL Server service, AccountName is a placeholder for the domain user account.
  3. In the CN= AccountName Properties dialog box, click the Security tab.
  4. On the Security tab, click Advanced.
  5. In the Advanced Security Settings dialog box, make sure that SELF is listed under Permission entries.
     • If SELF is not listed, click Add, and then add SELF.
  6. Under Permission entries, click SELF, and then click Edit.
  7. In the Permission Entry dialog box, click the Properties tab.
  8. On the Properties tab, click This object only in the Apply onto list, and then click to select the check boxes for the following permissions under Permissions:
     • Read servicePrincipalName
     • Write servicePrincipalName
  9. Click OK two times.

Of course the first method is more secure and probably better for most environments but if your environment is like mine where stuff changes every week I would go with Method 2.

Anyway now both SCCM and VMM are happy like never before. They like not having a SQL server on the same box.

Andreas