1

I have installed SQL Server 2017 Express onto Windows Server 2012 R2. I need to add a self signed certificate to SQL Server Protocols in order to enable wire encryption. This comes with a requirement that the SQL Server logon account has full control permissions over the certificate. The server machine is on a domain. When I try to grant full control over the certificate to the default "NT Service\MSSQL$InstanceName" account, it throws an unrecognised name error. Should this issue be resolved by changing the SQL Server logon account to a domain account with administrator permissions? Are there any potential hazards associated with this practice or is this the recommended solution to this problem?

2

What I have seen implemented in a few enterprise size companies is to have an Active Directory managed service account created on a per server or cluster basis which runs the database and agent services.

Depending on group policies in place you may have to also request that AD group policies or local security policies be modified to allow the right to "logon as a service". There is no need to grant extra privileges to the account.

Ideally you would add this account during the setup of a new install so the permissions on the SQL Server executables are granted automatically.

This benefits security policy as auditors can see that the password policy of the domain is applied to the account and that the account has privileges only on the server/cluster that are appropriate to run the database and nothing else.

I recommend a managed service account to do this because if you are at the stage where you are implementing encryption you might as well do all the right things from a security standpoint now so you don't have to go back and redo them later. A well named dedicated account will allow you apply a complex password that can be changed as required by company standards without wondering about where else you used that account.

  • When SQL Server 2016 was originally deployed onto this server, I don’t recall there being a problem assigning full control over the certificate to the built-in SQL Server service logon account. Perhaps a local admin account was used to implement this which has since been disabled. Does a domain account necessarily need to be used in this case or is there another built-in service account that is suitable? I have a generic admin domain account which possibly has the right to “logon as a service”. If so, would this account be suitable or would you specifically recommend using an MSA? – Guru Josh May 7 at 17:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.