I'm trying to setup Always Encrypted in my SQL Server database. I'd like to store the certificate for the CMK in LocalMachine/TrustedPublisher since I can push the certificate to that location on clients using Group Policy. However it seems I am only allowed to use LocalMachine/My (or CurrentUser/My, but that's not what I want either). I guess what I'd like is for someone who is really familiar with this feature to confirm for me that it's true that the certificate needs to be in "my".
The certificate is a self-signed certificate that I created in PowerShell. I then exported it from my personal certificate store and imported it into my computer's Trusted Publishers store.
I'm able to create the CMK:
CREATE COLUMN MASTER KEY CMK_1
WITH (
KEY_STORE_PROVIDER_NAME = 'MSSQL_CERTIFICATE_STORE',
KEY_PATH = 'LocalMachine/TrustedPublisher/MY_CERT_THUMBPRINT'
)
But if I then try to use it to create a CEK in SSMS (using GUI), I get this error message:
TITLE: Microsoft SQL Server Management Studio
Invalid certificate store 'TrustedPublisher' specified in certificate path 'LocalMachine/TrustedPublisher/MY_CERT_THUMBPRINT'. Expected value: 'My'. Parameter name: masterKeyPath (Microsoft.SqlServer.Management.AlwaysEncrypted.Management)
------------------------------ ADDITIONAL INFORMATION:
Invalid certificate store 'TrustedPublisher' specified in certificate path 'LocalMachine/TrustedPublisher/MY_CERT_THUMBPRINT'. Expected value: 'My'. Parameter name: masterKeyPath (Microsoft.Data.SqlClient)
