1

I am trying to find out what undesirable effects using a gMSA as a service account for MS SQL Server could have when using TDE certificates to encrypt databases.

As far as I have understood, there exists an encryption hierarchy in SQL Server. At the top level there is the Service Master Key, which is generated automatically when the SQL Server Instance is started for the first time. The Service Master Key is encrypted with a key, that more or less based on the Windows credentials of the SQL Server service account and the computer's credentials. The Service Master Key in turn encrypts the Database Master Key.

The Database Master Key in turn is used to protect the private keys of certificates.

When using TDE-certificates to encrypt databases it is best practice to create a backup of that certificate and the private key.

A special feature of a gMSA is, that the password will be managed by the active directory. As far as I am informed, part of this management is, that active directory changes that password regulary.

So my questions are:

  1. What happens when the password of the gMSA will be changed by the active directory in respect of the existing Service Master Key, Database Master Key and Certificates?
  2. Can the databases (encrypted with TDE certificates) within this SQL Server instance still be decrypted?
  3. Can the backup of the certificate and its private key still be used to attach databases encrypted with these certificates in another SQL Server Instance?

I have done some research on this topic but it was kind of difficult to find precise information on how the whole encryption hierarchy is affected when the service account password changes. This article e.g. states that the Service Master Key will become unusable: https://www.sqlserverscience.com/security/data-security/filesystem-security-tde-keys-certificates/?utm_medium=referral&utm_source=dba.stackexchange.com&utm_campaign=232330

On the other hand, it's hard to believe that using a gMSA - which I think is normally a good choice as service account for SQL Server - can cause such problems.

So I would be really grateful for help on this topic. Thank you in advance!

1 Answer 1

4

What happens when the password of the gMSA will be changed by the active directory in respect of the existing Service Master Key, Database Master Key and Certificates?

Nothing, it doesn't matter what the password is or was for the account, so long as the gMSA is able to logon and verify itself, all is well.

Can the databases (encrypted with TDE certificates) within this SQL Server instance still be decrypted?

Yes.

Can the backup of the certificate and its private key still be used to attach databases encrypted with these certificates in another SQL Server Instance?

Yes.

[...] it was kind of difficult to find precise information on how the whole encryption hierarchy is affected when the service account password changes.

The password itself doesn't really play a role, but you need the account and the password to do a logon, which the service will need to do when it starts. If you don't have this, the service won't start. Having said that, you can change the service account to another account, and with adding any needed permissions, start SQL Server and have access to everything. What you can't do is change the machine account and the service account at the same time.

Note that I'm using password here as if one would know the password for a gMSA, which one won't.

In case of migration or disaster recovery, I could migrate encrypted databases to a new SQL Server instance as long as I have a backup copy of the certificate and use the same gMSA on the new instance (as the machine account would change in these cases).

You truly only need a copy of the certificate, this is what encrypts the database encryption key (dek) in the user database (this cert lives in master, or an EKM device). If you have the certificate, you can restore it to the master database on any instance of SQL Server and then attach/restore the user database and it'll decrypt. There's no need to use the same accounts or server. This is why it is very important to have known good and safe copies of the certificate.

1
  • @JLP1985 updated the answer, you just need the certificate and nothing else really matters, so keep it safe! Commented Dec 12, 2023 at 11:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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