November 2, 2015 at 1:42 pm
I am working to set up encryption of my database and I am hoping you can help me get my head wrapped around this subject. I am trying to implement a process proposed by the DoD Standard Technical Implementation Guide (STIG) and cannot figure it out. I am hoping you can help
I am using SQL 2012 and the requirement is to
A: Run the following to create a certificate:
USE <'database name'>
CREATE CERTIFICATE <'certificate name'>
ENCRYPTION BY PASSWORD = '<'password'>'
FROM FILE = <'path/file_name'>
WITH SUBJECT = 'name of person creating key',
EXPIRY_DATE = '<'expiration date: yyyymmdd'>'
This script did not work – I did not know what file it was referencing and it kept erroring out, so I used my own process as follows
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mY_P@$$w0rd'
CREATE CERTIFICATE certificate_name
WITH SUBJECT = 'Certificate for my database'
USE <Database-Name>
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE <Certificate Name>
GO
ALTER DATABASE databasename SET ENCRYPTION ON
OK so the certificate and master key is created and TDE is implemented.
The next requirement of the STIG is to Run the following SQL script to create a symmetric key and assign an existing certificate:
USE <'database name'>
CREATE SYMMETRIC KEY <'key name'>
WITH ALGORITHM = AES_256
ENCRYPTION BY <'certificate name'>
This also error out, but my questions are the following:
What would the symmetric key be encrypting?
The application that is using the database is IIS, so would there be a problem with it communicating with the database?
Does the key need to be installed on that IIS server?
Do I need to apply the encryption to the database, column or table.
Any help is appreciated.
Jeff
November 3, 2015 at 4:21 am
Are you merely attempting to enable TDE?
jayoub (11/2/2015)
A: Run the following to create a certificate:USE <'database name'>
CREATE CERTIFICATE <'certificate name'>
ENCRYPTION BY PASSWORD = '<'password'>'
FROM FILE = <'path/file_name'>
WITH SUBJECT = 'name of person creating key',
EXPIRY_DATE = '<'expiration date: yyyymmdd'>'
This script did not work – I did not know what file it was referencing and it kept erroring out, so I used my own process as follows
Firstly, this server certificate should be created in the master database.
Secondly, the FROM FILE clause is only relevant when creating a certificate from a backup
jayoub (11/2/2015)
USE masterGO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mY_P@$$w0rd'
CREATE CERTIFICATE certificate_name
WITH SUBJECT = 'Certificate for my database'
This is correct, an expiry date would be advisable
jayoub (11/2/2015)
The next requirement of the STIG is to Run the following SQL script to create a symmetric key and assign an existing certificate:USE <'database name'>
CREATE SYMMETRIC KEY <'key name'>
WITH ALGORITHM = AES_256
ENCRYPTION BY <'certificate name'>
This also error out, but my questions are the following:
What would the symmetric key be encrypting?
The application that is using the database is IIS, so would there be a problem with it communicating with the database?
Does the key need to be installed on that IIS server?
Do I need to apply the encryption to the database, column or table.
Any help is appreciated.
Why do you need a symmetric key, what will you encrypt with it?
You already have a symmetric key protecting the database, which in turn is protected by the server certificate, this symmetric key was created by the following code
USE <Database-Name>
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE <Certificate Name>
GO
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 3, 2015 at 9:18 am
Thank you very much for the help.
Jeff
November 3, 2015 at 2:16 pm
you're welcome
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply