0

Is it true that when the sql service account is changed via the config manager then the new account can decrypt the SMK.

However if the change was made via the services.msc, then the new account doesn't have access to the SMK?

And that in the later case - sensitive data that is encrypted by SMK still continues to work because the computer account is able to access the SMK always. However when such a master db is moved to another server then and assuming that server has the sql service account different to the account that can decrypt the SMK then the linked server, credentials will not work?

How can I test whether a service account can access the SMK or not?

1 Answer 1

0

How can I test whether a service account can access the SMK or not?

Try this to open and close the master key.

USE <your database>
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = '<your password>'
GO
CLOSE MASTER KEY
GO

If you would like to re-encrypt your master key with new SMK. Then you could do this,

USE <your database>
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = '<your password>'
GO
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
GO
CLOSE MASTER KEY
GO
3
  • The re-encryption can then be auto decrypted by the sql server service account?
    – variable
    May 13 at 9:48
  • I didn't get your question but the alter command will be doing this ADD ENCRYPTION BY SERVICE MASTER KEY causes a copy of the master key to be encrypted using the service master key and stored in both the current database and in master. More details can be found here May 13 at 16:16
  • If you restore a master db on another server, then you will get error that master key is not initialized. Where is <your password> defined?
    – variable
    May 13 at 16:36

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.