Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL encryption without DMK

Status
Not open for further replies.

peac3

Technical User
Jan 17, 2009
226
AU
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

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,
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top