Trouble with Service Principal Name (SPN) - delegate access

bbrown

I am working thru setting up NAV 2009 with the database (SQL) and middle-tier (NAV server) on separate computers. I've been following thru the section "Walkthrough: Installing the Three Tiers on Three Computers". I have reached the section "To delegate access to the SQL Server service". When I get to step 10 the SQL Server Service "MSSQLSvc" is not listed in the "Add Services" box. I have verified in the SQL Server Log that the SPN is registered during startup.

This is a Windows 2008 domain. All servers are running Windows 2008. I am using SQL Server 2008.

Any ideas?

Slawek_Guzek

Most probably MSSQLSvc SPN was not registered by SQL becasue SQL Server service account doesn't have enough priviledges to do that.

You can let sql sevice account to register MSSQLSvc automatically by additg it for a while to AD Administrators group, restarting SQL service, removing account from AD Administrators, restarting once again, or by addding some special priviledges (not so wide as AD Administrator) to sql service account, or you can register SPN manually.

If your SQL Server service account is not a member of AD Administrator then you need to grant the account Write servicePrincipalName permission.

Basically this permission is required every time when Windows Authentication needs to be used, and server SQL is installed as a named instance

To grant Write servicePrincipalName permission to sq service account you need to you need to have AD Admininstrator priviledge and do a few steps:

1. download ADSIEDIT utility, http://technet.microsoft.com/en-us/libr ... 73354.aspx,
2. run ADSIEDIT.msc console (probably wou need to run it on domain controller, but I'm not sure), find your SQL server service account
3. right click, go to Permission tab
4. click Advanced
5. scroll to Allow, SELF, SPECIAL priviledge, click Edit
6. click to Properties tab, scroll down until you'll see Write servicePrincipalName
7. Click allow, close all
8. restart your sql server service

Now the sql service account should be able to register SPN itself.

In order to add SPN manually yo need to use setspn.exe utility (http://technet.microsoft.com/en-us/libr ... 73257.aspx) which can be downloaded as a part of Windows Server Support Tools

Regards,
Slawek

bbrown

Thanks for the reply.

The SQL service account is running with the domain administrator acount, so I don't think permissions would be an issue. (This is an isolated development system so I'm not concerned with granting SQL too many permissions). The SQL log indicated that the SPN is successfully registered during startup. If I manually stop and restart the SQL service I get entries in the log that tell me the SPN has been unregistered and then registered. This is the behavior I would expect.

This is a default SQL 2008 instance.

Slawek_Guzek

Hi,

Well, after rereading your first email i've noticed that you've said that SPN gets registered.. :oops:

Anyway try to download Support Tools, run setspn -L and check whether you can see the SPN from the server running middlte trier.. Then you may login as account used for running middle trier and check the same... At least you'll check if the problem is in OS level or within NAV..

Regards,
Slawek

bbrown

This is Windows Server 2008 so SetSPN is already there. No need to download.

Running "SetSpn -L ServerName" does not list the SPN. I tried on both the SQL box and the middle-tier. However querying explicitly for it by using "SetSpn -Q SPN" does find it.

bbrown

The SPN "MSSQLSvc" is registered under the account running the SQL service. Not under the computer account as indicated in the NAV install instructions.

Slawek_Guzek

And that's correct I think.

MSSQLSvc SPN is registered with computers's account only if SQL Server is run using NetworkService account as far as I know.

Regards,
Slawek