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

18 Responses

  1. […] account was in the admin group on the SQL server. I also knew that I had already taken care of the SPN registration issue, so I posted on the Technet SCCM forum. In hindsight, Stan White (a moderator on the forum) nailed […]

  2. I see the port you reference is 1443 is it supposed to be 1433?

    Thanks
    Scott

  3. It can be either, the most common port I have seen is 1443, I suggest that you check before you run the command. I should probably update the post to define how to check.

  4. A place that I have seen this message (today even) is when installing SCCM it fails during the installation of the SMS Provider. The pop-up message states: “failed to install sms provider”. The SMSprov.log file includes the following error:

    SQL Error: [28000][18456][Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’.

    • Jarvis (or anyone) – How did you fix your error?

      [ODBC SQL Server Driver][SQL Server]Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’. — [28000][18456][Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’

      I’m having that exact same error. I have my remote SQL server running the services with 3 different standard user accounts I created in AD. SQL install gives you the option to use those accounts, separate accounts for each service.

      My install bombs out with “setup failed to install sms provider”

      I’ve tried running the commands off this website and have confirmed my SPN but I can’t quite figure out how to run the command off this site?

      select auth_scheme from sys.dm_exec_connections where session_id=@@spid

      I tried this: sqlcmd -S -d -q “select auth_scheme from sys.dm_exec_connections where session_id=@@spid” -U sccmsqlserver

      I can’t get the output that’s expected, it errors out saying…

      “Named Pipes Provider: Could not open a connection to SQL Server”

      Named pipes on my SQL server is disabled by default.

      Another note, my firewall is on but I’ve confirmed all the ports that are supposed to be open are open and after clearing the log prior to the install the log is still empty so nothing got blocked from my FW.

      Any help would be appreciated.

  5. So what is the correct way to see which port the sql server is using? Thanks. 🙂

  6. Well spotted Scott (in above post) – default SQL port is 1433. It is worth checking in case a custom port is used, but in most cases for a default instance (and default install) it is 1433. Just thought I’d mention this in case anyone else had some problems trying the above using port 1443!

  7. Hello. This issue has been getting me for a few hours now.

    You state that the SQL query [select auth_scheme from sys.dm_exec_connections where session_id=@@spid] must return KERBEROS. Well, what if it returns NTLM. How can I change this to KERBEROS?

    Thanks…

  8. I’m getting the MOF SMS provider error when my SQL server is running under Local System account. Another blog suggested switching to Network Service account but this didn’t fix the error. I’m therefore confused ‘cos all the blog and KBs point to this issue being caused by the SQL Server service on the SQL server running as an alternative user account and this not having the SPN registered. So whats causing my issue?

  9. You write
    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.

    as i undestand this is means that if i use local system (as i do), i should use my own admin account, this i am not really sure of ?

    when i run setspn -L i get
    MSSQLSvc/:1433
    HOST/
    HOST/
    is this correct ?

    ps i dont have a service account

  10. Jakob,

    If you are clustered you must use a service account and that is what the AccountName reference refers to.

  11. I spent a week and about 30 hours on this. Many things seems like the right fix, but nothing worked. Ultimately, this is what helped me:

    1. Enable Kerberos logging Q262177
    2. Enable kerberos auth for SQL cluster, Q319723. Same as above, either method works.
    3. Read the SQL SPN section from http://technet.microsoft.com/en-us/library/ms189585(SQL.90).aspx . Mainly, this is to make sure you have the correct SPN
    — non-cluster
    — with clustered SQL

    My setup, we have a cluster, so at a minimum I needed 2 SPN. But I added couple more for each cluster node (SpnA:1433 and SpnB:1433) just to be safe

    Finally, my real fix came from using a combination of adisedit.msc and ldifde

    At the end of the article from step 3, you will see there are two ways to run ldifde.

    ldifde -d “CN=User Name,DC=betaland” -l servicePrincipalName -F NewoutputUsersAll.txt

    ldifde -d “CN=Users,DC=betaland” -l servicePrincipalName -F NewoutputUsersOne.txt

    One for all users, one for your domain user. The key was to grep the output files, make sure to create separate files, for Server Name from the Kerberos errors in the System log. There can only be one SPN for each SQL instance. If you have duplicates, you will get KDC_ERR_S_PRINCIPAL_UNKNOWN errors. Once you locate the duplicates, you can use adisedit.msc to remove them. We had duplicates because we run the cluster under two different accounts at different times.

    If you do everything right, use KERBTRAY and eventually you will see the MSSQLSvc ticket .

    One last note, when you run SMS setup, during prereq check, you will see Kerberos errors. If you see the error for SPN that you didn’t add, then add it. If you see errors for SPN that you already added, then probably means you have duplicates.

  12. […] https://poseidom.wordpress.com/2007/12/16/set-spn-for-sql-2005-sccm-remote-sql-fix/ This entry was posted in Sikkerhet. Bookmark the permalink. Trackbacks are closed, but you can […]

  13. I’ve been using this to troubleshoot a lot of Kerberos issues lately. It is very valuable info.

    One suggestion I have is instead of using setspn -a command, use setspn -s. This does a query to make sure the spn isn’t a duplicate before adding a spn to an ID. you can also use setpsn -q to query to see who owns a particular spn. I think that the -s and -q commands are only available in server 2008 or windows 7 (not server 2003 or xp).

  14. […] Set SPN for SQL 2005 (SCCM Remote SQL Fix) – This is required for remote SQL named instances. The “SQL Server Browser” service must be enabled if installing SCCM on a named SQL instance. […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: