SPN Insanity
-
I have been trying to wrap my brain around the SPN concept and not quite getting it. Here is the situation. I installed a new instance of SQL 2005 the other day using domain user service accounts. The account was not a domain admin and the SPN did not get generated. As it was early in the morning and I had not slept in 24 hours I decided to add the account to the domain admins, and restart the sql service (awesome:)). Problem solved except that now I have my sql service account in domain admins (not awesome:().
The question.... If I remove the service account from domain admins will my SPN registration still work? And what if I need to restart the service?
Also, I think the whole SPN concept would gel if I could actually see the SPNs listed out somewhere but my attempts to use SETSPN -L (server name) on my DB server have yielded nothing that makes any sense. Any feedback on this would rock.
Thank you for reading my post.
-
there do seem to be problems with services running under domain accounts not registering their SPN's, local system works fine.
SETSPN -L
should list all registered SPN's (not just for sql server)
use setspn to generate a new spn in the format
SETSPN -A MSSQLSvc/host:port accountname
so for a server caller perrysql and domain account SQLC\perry on a sql server instance using port 1608
SETSPN -A MSSQLSvc/perrysql.SQLC.co.uk:1608 SQLC\perry
hope this helps to cure your headaches 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
-
An SPN will only be registered if the account in question is the computer's account (effectively System or Network Service) or a Domain Admin level account. If you run SQL Server under a regular local or domain user account, the SPN will not be added automatically. This isn't a problem though. As Perry Whittle has shown, you can use the SETSPN tool. It will need to be run by someone with Domain Admin privs.
You're looking to have the following entries:
MSSQLSvc/NetBIOSname:port accountname
MSSQLSvc/FQDN:port accountname
If the port is 1433, you can leave off :port.
The NetBIOS name is the name of the server without the rest of it. So in Perry's example, it would just be:
SETSPN -A MSSQLSvc/perrysql:1608 SQLC\perry
And if we were to use that example but the SQL Server was a default instance and you didn't change the port from 1433, you would just simply do the following (note the drop of :1608):
SETSPN -A MSSQLSvc/perrysql SQLC\perry
SETSPN -A MSSQLSvc/perrysql.SQLC.co.uk SQLC\perry
K. Brian Kelley
@kbriankelley -
Thank you for your replys.
So to list out the current spns I should use
SETSPN -L against what server? My domain controler or my database server? If I run it against my database server all I get is :
HOST\DBSERVER
HOST\dbserver.dc.dc.dc
I am assuming the SPN got set correctly becuase the service was started using a domain admin account. I just can't see it. Currently I am not having any connection issues, but before I changed the service account to a domain admin I was having issues.
Thank you,
-
There is another option to manage the SPN records. You can add the appropriate privileges to the domain account that is running SQL Server. Review the article at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;811889
Here is the relevent passage in that article:
How to configure the SQL Server service to create SPNs dynamically for the SQL Server instances
To configure the SQL Server service to create SPNs dynamically, you must modify the account's access control settings in the Active Directory directory service. You must grant the "Read servicePrincipalName" permission and the "Write servicePrincipalName" permission for the SQL Server service account.
Warning If you use the Active Directory Service Interfaces (ADSI) Edit snap-in, the LDP utility, or any other LDAP version 3 clients and you incorrectly modify the attributes of Active Directory objects, you can cause serious problems. These problems may require that you reinstall Microsoft Windows Server 2003, Microsoft Windows 2000 Server, Microsoft Exchange Server 2003, Microsoft Exchange 2000 Server, or both Windows and Exchange. We cannot guarantee that problems caused by incorrectly modifying the attributes of Active Directory objects can be resolved. Modify these attributes at your own risk.
Note To grant the appropriate permissions and user rights to the SQL Server startup account, you must be logged on as a domain administrator, or you must ask your domain administrator to do this task.
To configure the SQL Server service to create SPNs dynamically, follow these steps:
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.
Notes
•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 make sure that the check boxes for the following permissions are selected under Permissions:
•Read servicePrincipalName
•Write servicePrincipalName
9.Click OK three times, and then exit the ADSI Edit snap-in.
For help with this process, contact Active Directory product support, and mention this Microsoft Knowledge Base article.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Jeffrey's advice works, I did it sometime ago. By giving the permissions to the domain account it will be able to create the SPN.
-
nick harris (12/5/2008)
Thank you for your replys.So to list out the current spns I should use
SETSPN -L against what server? My domain controler or my database server? If I run it against my database server all I get is :
HOST\DBSERVER
HOST\dbserver.dc.dc.dc
I am assuming the SPN got set correctly becuase the service was started using a domain admin account. I just can't see it. Currently I am not having any connection issues, but before I changed the service account to a domain admin I was having issues.
Thank you,
based on my example
SETSPN -L perrysql
i've spoken to MS PSS on this issue sometime ago. As as i'm aware (and been led to believe) it shouldnt be a problem on domain user accounts to register SPN's as the Trusted for delegation flag should be in force (which allows itself to register the SPN). If for some reason it gets screwed (and god knows how) then you have to go through the polava of what Jeff posted or manually registering as i have shown.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
-
OK, I got what I was looking for. To list out the SPNs you have to do it under the account name that is creating the SPN. Which makes me believe that it is not the particular account running the service that is important, more that the SPN exists.
so if I use a domain admin account Nick_DA to set the SPN, to see the SPN I would type:
SETSPN -L Nick_DA
cool:D.
Thank you to all who replied. Gives me somethings to think about.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply