I restored a masterkey, and then created a certificate and symmetric key SSMS with:
use AdventureWorks2022
DROP MASTER KEY
RESTORE MASTER KEY
FROM FILE = 'c:\stuff\master_key_backup.mas'
DECRYPTION BY PASSWORD = 'ASDF$E£%^IJYK*&234'
ENCRYPTION BY PASSWORD = '259087M#MyjkFkjhywiyedfgGDFD'
OPEN MASTER KEY DECRYPTION BY PASSWORD = '259087M#MyjkFkjhywiyedfgGDFD';
-- create new certificate and symkey to encode a new column
CREATE CERTIFICATE Certificate_test2 WITH SUBJECT = 'Protect my second data';
CREATE SYMMETRIC KEY SymKey_test2 WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE Certificate_test2;
I then encrypted a column from a table using:
open symmetric key SymKey_test2 decryption by certificate Certificate_test2
update AdventureWorks2022.[Person].[EmailAddress]
set email_encrypt = ENCRYPTBYKEY(key_guid('symkey_test2'), emailaddress)
from AdventureWorks2022.[Person].[EmailAddress]
close symmetric key symkey_test2
and tested that it worked with:
open symmetric key symkey_test2 decryption by certificate certificate_test2
select convert(nvarchar, decryptbykey(email_encrypt)) as 'decrypted email',*
from AdventureWorks2022.[Person].[EmailAddress]
close symmetric key symkey_test2
I put the code that worked in a new script, but came with the error:
Please create a master key in the database or open the master key in the session before performing this operation.
I can get it to work if I include the line: OPEN MASTER KEY DECRYPTION BY PASSWORD = '259087M#MyjkFkjhywiyedfgGDFD';, but I do not want to expose my password in a new script that will be called in a stored procedure and excel data calls.
Is there a way to open a master key without exposing the password? The final goal is to have this set up in a stored procedure that is called by excel data connection, but of course, I do not want the final users to have access to the master password.
