Trouble with Service Principal Name (SPN) - delegate access

bbrownbbrown Member Posts: 3,268
edited 2009-01-05 in NAV Three Tier
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?
There are no bugs - only undocumented features.

Answers

  • Slawek_GuzekSlawek_Guzek Member Posts: 1,690
    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
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • bbrownbbrown Member Posts: 3,268
    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.
    There are no bugs - only undocumented features.
  • Slawek_GuzekSlawek_Guzek Member Posts: 1,690
    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
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • bbrownbbrown Member Posts: 3,268
    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.
    There are no bugs - only undocumented features.
  • bbrownbbrown Member Posts: 3,268
    The SPN "MSSQLSvc" is registered under the account running the SQL service. Not under the computer account as indicated in the NAV install instructions.
    There are no bugs - only undocumented features.
  • Slawek_GuzekSlawek_Guzek Member Posts: 1,690
    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
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
Sign In or Register to comment.