locked
Automatic SPN registration @ SQL-cluster without Domain Admin RRS feed

  • Question

  • Hello,

    I have a couple of SQL-clusters where I want to enable kerberos authentication. Reading http://technet.microsoft.com/en-us/library/ms191153.aspx it says " the Database Engine must be running under a built-in account, such as Local System (not recommended) or NETWORK SERVICE, or an account that has permission to register an SPN, such as a domain administrator account. If SQL Server is not running under one of these accounts, the SPN is not registered at startup and the domain administrator must register the SPN manually."

    Beeing SQL-clusters our SQL database enginegs are obviously running under locked down service-accounts. I googled some and found this blogpost http://poseidom.wordpress.com/2007/12/16/set-spn-for-sql-2005-sccm-remote-sql-fix/, I tried the 2nd method there which involves using ADSIedit to give the Serviceaccount rights to read/write its own "Service Principle Name" and this worked fine.

    However I've only done this in my test-enviroment and wanted to check if it is not recommended to this way since I cant find any MS-documentation which suggets this.

     

    Regards

    Jonas


    • Edited by Jonas_Bson Friday, May 27, 2011 1:44 PM fix spelling
    Friday, May 27, 2011 12:26 PM

Answers

  •  

    However I've only done this in my test-enviroment and wanted to check if it is not recommended to this way since I cant find any MS-documentation which suggets this.

     

    Regards

    Jonas



    Hi Jonas,

    I haven't seen any article directly recommending this practice but I have implemented this on a active-passive windows/sql cluster , this hasn't been a problem for me until now. The only reference I found was http://support.microsoft.com/kb/811889 and other PSSQL blog (I cant get hold of that link as of now) explaining the SQL error log message .


    Thanks, Leks
    • Proposed as answer by Stephanie Lv Saturday, June 4, 2011 2:08 AM
    • Marked as answer by Stephanie Lv Sunday, June 5, 2011 2:52 AM
    Saturday, May 28, 2011 7:31 AM

All replies

  • Jonas,

    You can also manually register the SPNs using an account that has authority to do so. If you manually register them there shouldn't be an issue with using kerberos. Remember though, after you register them you'll have to cycle the engine service.

    SetSPN: http://technet.microsoft.com/en-us/library/cc773257(WS.10).aspx

    Friday, May 27, 2011 1:20 PM
  • Hello Sean,

    Thank you for your reply!

    I am fully aware that manually registering of the SPN's is possible. However I would like the automatic registration to work since the SQL-server does a "DsWriteAccountSpn API call" at each startup.

    Regards

    Jonas

     

    Friday, May 27, 2011 1:22 PM
  • Hello Sean,

    Thank you for your reply!

    I am fully aware that manually registering of the SPN's is possible. However I would like the automatic registration to work since the SQL-server does a "DsWriteAccountSpn API call" at each startup.

    Regards

    Jonas

     


    Yes, you can use adsi to give the service accounts permissions to do so as you have already pointed out. Whether or not it is a best practice is nullified by the business decision to use locked down service accounts. Remember best practices are good guidelines but there is normally a trade-off.

    On the security side of it, the only thing that comes to mind is if someone could gain access to cmdshell and then be able to register other spn's (in addition to anything else they could do with the engine's service account). That's a decision you'll have to make.

    Sorry I can't be of more help, maybe someone else can add.

    -Sean


    Hope this helps, -Sean
    Friday, May 27, 2011 1:27 PM
  • Hello Sean,

    Thank you for shedding some lights ont his and also for your thoughts on this matter.

    It would be interesting how other people are doing this. Are people manually registern all SPN's and accepting the warnings-messages logged at service-startup or are you setting permissions on the service accounts?

     

     

    Regards

    Joans


    • Edited by Jonas_Bson Friday, May 27, 2011 1:44 PM rephrase
    Friday, May 27, 2011 1:41 PM
  •  

    However I've only done this in my test-enviroment and wanted to check if it is not recommended to this way since I cant find any MS-documentation which suggets this.

     

    Regards

    Jonas



    Hi Jonas,

    I haven't seen any article directly recommending this practice but I have implemented this on a active-passive windows/sql cluster , this hasn't been a problem for me until now. The only reference I found was http://support.microsoft.com/kb/811889 and other PSSQL blog (I cant get hold of that link as of now) explaining the SQL error log message .


    Thanks, Leks
    • Proposed as answer by Stephanie Lv Saturday, June 4, 2011 2:08 AM
    • Marked as answer by Stephanie Lv Sunday, June 5, 2011 2:52 AM
    Saturday, May 28, 2011 7:31 AM