How to test whether current sql engine service account can decrypt the SMK?



  • 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?



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

    Try this to open and close the master key.

    USE 
    GO
    OPEN MASTER KEY DECRYPTION BY 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 
    GO
    OPEN MASTER KEY DECRYPTION BY PASSWORD = ''
    GO
    ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
    GO
    CLOSE MASTER KEY
    GO
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2