Enhancing Database
Security in SQL Using Database Encryption
Why?
We grant access to users in SQL
to do read and write on a database. But this data gets stored on physical
drives as MDF & LDF files. How we protect those data? In SQL the Data
Encryption method to ensure that our data is protected. This makes your
database to get encrypted & ensure no data is available for anyone to read
from outside SQL.
How?
In SQL
the database encryption is achieved by Transparent Data Encryption (TDE)
concept. This will have various set up of keys & certificate which ensures
that all your data is protected & only authenticated users from SQL server
can read or do a write to the tables present in the database. Let’s begin
Hierarchy of Keys in TDE:
When a
SQL server is installed on windows a SERVICE MASTER KEY (SMK) will get created
for the SQL server for the particular SQL Instance. There will be a DATABASE
MASTER KEY (DMK) will be created in Master database. This DATABASE MASTER KEY
will get encrypted by SERVICE MASTER KEY of the SQL Server. There will be
certificate which will be associated in database encryption. This Certificate
will get created in Master Database & will be encrypted by DATABASE MASTER
KEY. The Key which we use to encrypt database is known to be DATABASE
ENCRYPTION KEY (DEK) this will be created on the respective database & will
be encrypted by above mentioned Certificate. It is a best practice to backup
your keys and certificate for recovering any loss of the same.
In short,
• Service Master Key (SMK)
– Created during SQL Server
Installation
• Database Master Key (DMK)
– Created in master database
– Encrypted by SMK
• Server Certificate
– Created in master, encrypted by DMK
• Database Encryption Key (DEK)
– Created in user database, encrypted
by Cert
Scenario1:
I would
like to start with a backup of servercounts database with compression option.
Followed by creating Master Key for encryption in Master database. We will
create a server certificate named ‘tdecert’ for transparent database
encryption. Finally we will encrypt the database with an encryption algorithm
which is mapped to this certificate ‘tdecert’.
1. Taking default
compressed backup of the database
Below script is used
to take backup of the database with compression
2.Creating master key &
certificate
The database Master Key & the certificate got created
with above queries.
3. Creating Database
Encryption Key in Servercounts database with tdecert Certificate
Once you create an encryption key for your
database SQL will intimate about the certificate backup that we need to perform
as a safety measure. We will see hoe to backup certificates later in this post.
4. Enabling
encryption on Servercounts database & viewing the same
While enabling the encryption on servercounts we will select
& run above mentioned queries together this will result in showing current
status of encryption on databases.
You can see the tempdb got
encrypted first & the status of Servercounts is getting encrypted. To see
logically this is how SQL works, to encrypt the data in Servercounts it is
using tempdb as a temporary storage to swap non encrypted & encrypted data
of Servercounts database.Now rerun the second part alone & see the status
of servercounts encryption went to ‘3’.
Now we can ensure that the
database got encrypted with TDE & has high security within it against any
intervention to it. Since we got the security on our database, we may need to
dilute ourselves in usage of space to hold backup files. Let’s see how this
database encryption affects compression of database backups. We are going to
take one backup without compression & another backup with compression in
our TDE enabled servercounts database.
Comparison of backups
sizes:
You may see the backup
compression worked well on servercounts database when it is not encrypted the
size of the backup file is just 1721 KB. Once the database is encrypted the
backup size with compression raised to 8174 KB which is very near to the
size backup taken without compressing it
with encryption on 8414 KB. Thus enabling Encryption on your databases may
result in a little diverse effect on backup compression.
No
worries for this Microsoft has overcome this issue in SQL 2014 , we will
discuss the same in upcoming posts. Till then have fun J.
========================================================
Thanks for coming till this point!!VSG
SQL DBA
No comments:
Post a Comment