Hello all,
I was trying to implement encryption in my SQL server with Symmetric key and password.
from what I understand is in each server, we need to create Database Master Key then Symmetric Key.
But what I have found is encryption without DMK works as well as per code below
Is there any reason why we need to create DMK in this case?
Also is there a way to create the symmetric key on server level without creating one by one on each database? or how is the easiest to have global applied to the whole server? (e.g. create same keyname and password for each database?)
Thanks guys,
I was trying to implement encryption in my SQL server with Symmetric key and password.
from what I understand is in each server, we need to create Database Master Key then Symmetric Key.
But what I have found is encryption without DMK works as well as per code below
Code:
USE DBName;
IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys WHERE name = 'CornerStone_Key2')
CREATE SYMMETRIC KEY CornerStone_Key2
WITH ALGORITHM = AES_128
ENCRYPTION BY PASSWORD = N'test'
;
GO
OPEN SYMMETRIC KEY CornerStone_Key2
DECRYPTION BY PASSWORD = N'test';
GO
UPDATE CLNT2
SET Encrypted_FRST_NAME = EncryptByKey(Key_GUID('CornerStone_Key2'), FRST_NAME);
GO
SELECT C.*, CONVERT(varchar, DecryptByKey(encrypted_frst_name)) FROM CLNT2 C
Is there any reason why we need to create DMK in this case?
Also is there a way to create the symmetric key on server level without creating one by one on each database? or how is the easiest to have global applied to the whole server? (e.g. create same keyname and password for each database?)
Thanks guys,