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?
Sql-server – Which account should be used for the SQL Server Service logon account where the server has been added to a domain
encryptionsql serverwindows-server
Related Question
- SQL Server – Service Account Windows Privileges and Rights
- SQL Server – How to Configure Encrypted Connections to Prevent MITM Attacks
- SQL Server – Configure to Encrypt Data Traffic in Both Directions
- SQL Server Service Account – Points to Consider When Replacing
- SQL Server 2016 – Always Encrypted Certificate Clarification
- SQL Server Certificate Restore Issue with Different Service Account
- SQL Server 2017 – Encrypted Connections Configuration Issues
Best Answer
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.