Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Encryption - Create Certificate Expand / Collapse
Author
Message
Posted Friday, August 8, 2014 9:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 18, 2014 8:33 AM
Points: 26, Visits: 114
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'

Post #1601226
Posted Friday, August 8, 2014 9:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:20 PM
Points: 6,371, Visits: 13,709
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"
Post #1601244
Posted Friday, August 8, 2014 9:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 18, 2014 8:33 AM
Points: 26, Visits: 114
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'>


Post #1601251
Posted Friday, August 8, 2014 10:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:20 PM
Points: 6,371, Visits: 13,709
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"
Post #1601256
Posted Friday, August 8, 2014 10:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 18, 2014 8:33 AM
Points: 26, Visits: 114
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.

Post #1601271
Posted Friday, August 8, 2014 3:32 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:20 PM
Points: 6,371, Visits: 13,709
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"
Post #1601343
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse