swoozie (8/8/2014)
I feel like this is aother silly question, however I am not finding the answer that makes sense to me.I would like to understand the difference between using the CREATE CERTIFICATE stateme with FROM FILE with obtaining a Certificate, installing it, and implementing it through the Configmanager GUI?
In addition, I was provided this SQL statement to use and it isnt valid, and I am not sure which combination would be correct:
In addition, I would like to know if this can be made dynamic, with Variables being passed.
CREATE CERTIFICATE NameOfCert
FROM FILE = 'C:\Path\NameOfCert.cer'
ENCRYPTION BY PASSWORD = 'CerTpa$$w0rd' --Error, Expecting FOR
WITH SUBJECT = 'UserName',--Error, Expecting ( or AS
EXPIRY_DATE = 'expirationdate: yyyymmdd'
The syntax for the create from file is incorrect.
Whether creating new or from file you don't want the encryption by password, leave this section out to encrypt the cert by the Database Master Key.
When creating from file the subject is also moot as is the start date and end date.
To create your own new cert use the following
CREATE CERTIFICATE [MyNewCert] AUTHORIZATION [dbo]
WITH SUBJECT = 'My New Certificate'
, START_DATE = '2014-01-01 00:00:00'
, EXPIRY_DATE = '2020-01-01 00:00:00'
Take a backup of the new cert immediately use
BACKUP CERTIFICATE [MyNewCert] TO
FILE = 'E:\Bak\MSSQL12.INST1\MSSQL\Backup\MyNewCert.cer'
WITH PRIVATE KEY(
FILE = 'E:\Bak\MSSQL12.INST1\MSSQL\Backup\MyNewCert.pky',
ENCRYPTION BY PASSWORD = 'P@sswordt0encryptcertbackup')
This backup can then be restored to a new server use
CREATE CERTIFICATE [MyNewCert] AUTHORIZATION [dbo]
FROM FILE = 'E:\Bak\MSSQL12.INST1\MSSQL\Backup\MyNewCert.cer'
WITH PRIVATE KEY (
DECRYPTION BY PASSWORD = 'P@sswordt0encryptbackup'
, FILE = 'E:\Bak\MSSQL12.INST1\MSSQL\Backup\MyNewCert.pky'
)
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉