SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Encryption - Create Certificate


SQL Encryption - Create Certificate

Author
Message
swoozie
swoozie
Old Hand
Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)

Group: General Forum Members
Points: 300 Visits: 438
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'


Perry Whittle
Perry Whittle
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20312 Visits: 17244
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" ;-)
swoozie
swoozie
Old Hand
Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)

Group: General Forum Members
Points: 300 Visits: 438
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'>



Perry Whittle
Perry Whittle
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20312 Visits: 17244
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" ;-)
swoozie
swoozie
Old Hand
Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)

Group: General Forum Members
Points: 300 Visits: 438
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.
Perry Whittle
Perry Whittle
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20312 Visits: 17244
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" ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search