August 8, 2014 at 9:10 am
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'
August 8, 2014 at 9:45 am
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" 😉
August 8, 2014 at 9:57 am
So to Clarify, I should be creating a new certificate 1st, Taking a backup, then If I need to replicate on other servers use the back up and that this has absolutely nothing to do with the Certificates that I installed through MMC.
CREATE CERTIFICATE NameOfCert AUTHORIZATION [dbo]
WITH SUBJECT= 'UserName'
,START_DATE= '2014-01-01'
,EXPIRY_DATE= '2015-01-01'
BACKUP CERTIFICATE [NameOfCert ] TO
FILE = 'E:\PATH\NameOfCert .cer'
WITH PRIVATE KEY(
FILE = 'E:\PATH\MyNewCert.pky'
,ENCRYPTION BY PASSWORD = 'P@$$w0rdt0EncryptCertb@ckup')
--SEPARTATE SCRIPT TO INSTALL\RESTORE on new server
CREATE CERTIFICATE [MyNewCert] AUTHORIZATION [dbo]
FROM FILE = 'E:\PATH\NameOfCert .cer'
WITH PRIVATE KEY (
DECRYPTION BY PASSWORD = 'P@$$w0rdt0EncryptCertb@ckup'
, FILE = 'E:\PATH\MyNewCert.pky'
)
Do I ever need to use?
CREATE SYMMETRIC KEY <'key name'>
WITH ALGORITHM = AES_256
ENCRYPTION BY <'certificate name'>
August 8, 2014 at 10:08 am
The create cert you specified was incorrect for a restore or for a new cert. What do you want the certificate for, are you encrypting within sql server?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 8, 2014 at 10:50 am
It isn't so much as having to encrypt something right this minute as it is to compliance with a new organizational policy,
We were provided those scripts and told to run them if encryption didnt allready exist. This is where I am having a disconnect between understanding the difference between running these scripts, and the SQL Signed Certificates we request and install and store in the Personal folder.
I mean, the scripts provided made it appear that there should already be a Certificate file available.
and there is no definate explaination with them. So I was trying to get some clarification and I also wasnt sure what to ask.
August 8, 2014 at 3:32 pm
swoozie (8/8/2014)
We were provided those scripts and told to run them if encryption didnt allready exist.
Who provided them
swoozie (8/8/2014)
This is where I am having a disconnect between understanding the difference between running these scripts, and the SQL Signed Certificates we request and install and store in the Personal folder.
certificates used by sql server are loaded into sql server, the thumbprint and cert detail are stored in the master database.
swoozie (8/8/2014)
I mean, the scripts provided made it appear that there should already be a Certificate file available.and there is no definate explaination with them. So I was trying to get some clarification and I also wasnt sure what to ask.
Have you been provided a certificate by a trusted CA?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply