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/<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.
    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

Advertisements

BITS Functionality – Common Misconception

It is commonly thought that BITS can help with WAN bandwidth problems. However BITS in its current version is only aware of local interfaces. Long story short it can only see the Ethernet connection of the client not the WAN interface. Remember it was originally designed for dial up.

•Supports resumption of file transfers
•Current version does not work well over WAN links
•From the platform SDK:
“BITS is only aware of the network conditions on the client computer; BITS is not aware of network conditions beyond the client. If there are no applications running on the client that use the network, BITS consumes most of the available bandwidth. This does not mean the network beyond the client is idle; the network may be at full capacity.
If the client has a fast network card (10 Mbps) but is connected to the network via a slow link (56 Kbps), BITS will compete for the full bandwidth instead of using only the available bandwidth on the slow link. This is because BITS has no visibility of the network traffic beyond the client.”
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/bits/bits/network_bandwidth.asp?frame=true
•BITS is configurable via GPO for greater control if necessary