asked
What permission domain account should have when they are used as SQL service account?
After changing SQL service account to a different domain account, I received below error: The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098, state: 15. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies. Users are able to connect to SQL using NTLM authentication. I am not sure if I can ignore this error? What permissions domain account should be granted to be used as SQl service account? Should it be added to local admin group? Also below errors were logged: Performance counter shared memory setup failed with error -1. Reinstall sqlctr.ini for this instance, and ensure that the instance login account has correct registry permissions. Cannot use Large Page Extensions: lock memory privilege was not granted. My server is windows 2008R2 and SQL server 2008. Its 64 bit physical server.
Did you change the service account using the SQL Configuration Manager? "Always use SQL Server tools such as SQL Server Configuration Manager to change the account used by the SQL Server ... In addition to changing the account name, SQL Server Configuration Manager performs additional configuration such as setting permissions in the Windows Registry so that the new account can read the SQL Server settings. Other tools such as the Windows Services Control Manager can change the account name but do not change associated settings. If the service cannot access the SQL Server portion of the registry, the service may not start properly." -
http://msdn.microsoft.com/en-us/library/ms143504(v=sql.100).aspx
This site runs off of votes. Please mark all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that answer.
Please check Glenn Berry's blog post -- Provisioning a New SQL Server Instance – Part One -- https://www.red-gate.com/simple-talk/sql/database-administration/provisioning-a-new-sql-server-instance-part-one/ -- "You should request that a dedicated domain user account be created for use by the SQL Server service. This should just be a regular, domain account with no special rights on the domain. You do not need or want this account to be a local admin on the machine where SQL Server will be installed. The SQL Server setup program will grant the necessary rights on the machine to that account during installation."
Yes, I changed the service account using the configuration manager. I am wondering if I should worry about the SPN related error or not. Also second part of the error message talks about performance counter. I checked and found that all my SQL related performance counters are missing. My lead suggested me to add to local admin as the previous account was also local admin but I am not sure if thats the right way.
Agreeing with KenJ comments. Also, Look for SPN registration for kerberos in this article:
http://technet.microsoft.com/en-us/library/ms191153.aspx Regarding Performance counters missing error, refer to this solution:
http://thelonelydba.wordpress.com/2012/07/24/missing-performance-counters-on-sqlserver-2008/
Current documentation is at https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?view=sql-server-ver15#Serv_Perm
The permissions list isn't just "logon as a service", and is a bit more complex than just assigning a few permissions through secpol.msc, so it's best to do it using the Configuration Manager tool.
17 People are following this question.
