Set SPN for SQL 2005 (SCCM Remote SQL Fix)

I have found many references to issues with a remote SQL server running under a service account around the Internet. This issue only manifests itself if the SMS provider is located on the site server and the SQL server is located remotely running as a service and is running under standard privileges. The most common symptoms are errors in the installation log related to smsrprt.mof and anonymous login; posted here is a great description (http://www.eggheadcafe.com/software/aspnet/30654425/sccm-mixedmode-setup-fai.aspx)
So, here is the problem. If you are running SQL under a standard user service account as you would in a cluster or remote SQL instance the SPN must be registered with the FQDN and it must be registered both with and without the port number. There is a great description of how to do this here: http://msdn2.microsoft.com/en-us/library/ms189585.aspx; but it is related to IIS. I will give you the short version.

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/ Note YOU MUST USE THE FQDN
4. Run  setspn -A MSSQLSvc/:   Note YOU MUST USE THE FQDN, and the most common port is 1443
5. Run setspn -L 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.
  
  

I would love to reference all the posts and blogs and KB’s that I used to come to this but I wouldn’t know where to begin. I would also like to thank my good friend Prabhu Padhi on the SMS team for fielding my call last night and offering his assistance.
Originally posted here by me: http://www.myitforum.com/forums/m_164437/mpage_1/key_/tm.htm#164437