December 11, 2009 at 11:24 pm
I've created the master key, certificate and symmetric key, but we have many databases we administer with a general script to create or modify objects. Once these are created I want to test to see if they already exist so when we run this script on the database weekly, I skip past creating. I do this now for tables, functions etc. by checking for them in sysobjects. But that doesn't seem to work here. What is a SQL statement I can use to see if they already exist? This is what I used to create them in the first place...
create master key Encryption by password = 'passwordhere';
create certificate NewCert with subject = 'Encryption Certificate Name';
create symmetric key NewKey with algorithm=AES_256 Encryption by certificate NewCert;
I'd like some thing like the following or something else to know they have already been created if anyone can help...
IF SELECT Count(*) FROM sysobjects WHERE name = 'NewCert' = 0
BEGIN
create certificate NewCert with subject = 'Encryption Certificate Name';
END
Thank you!
December 13, 2009 at 9:17 am
I have found this out myself. In case anyone ever finds this, here is how you do it.
IF (select Count(*) from sys.symmetric_keys where name like '%DatabaseMasterKey%') = 0
BEGIN
CREATE master key Encryption by password = 'passwordhere';
END
IF (select Count(*) from sys.certificates where name = 'NewCert') = 0
BEGIN
CREATE CERTIFICATE NewCert with subject = 'Encryption Certificate Name';
END
IF (select count(*) from sys.symmetric_keys where name = 'NewKey') = 0
BEGIN
CREATE symmetric key NewKey with algorithm=AES_256 Encryption by certificate NewCert;
END
May 14, 2015 at 12:22 pm
Thanks for this, I was searching for the system tables and could not for the life of me find where to locate this. GREATLY Appreciated for the knowledge share!
May 14, 2015 at 12:47 pm
the Database Master key always has an symmetric_key_id = 101, i read someplace, so my scripts all use that:
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
BEGIN
PRINT 'Creating Database Master Key'
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'NotTheRealPassword'
END
ELSE
BEGIN
PRINT 'Database Master Key Alread Exists'
END
--drop certificate MyDemoDataSecurityCertificate?
--create our certificate.
IF NOT EXISTS(SELECT *
FROM sys.certificates
WHERE name = 'MyDemoDataSecurityCertificate')
BEGIN
CREATE CERTIFICATE MyDemoDataSecurityCertificate WITH SUBJECT = 'MyDemo DataSecurity Certificate', EXPIRY_DATE = '12/31/2024'
PRINT 'MyDemoDataSecurityCertificate Created'
END
ELSE
BEGIN
PRINT 'MyDemoDataSecurityCertificate Already Exists.'
END
Lowell
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply