Access to SQL Server encryption (1 Viewer)

alexcarter404

New member
Local time
Today, 05:57
Joined
Jun 12, 2013
Messages
4
Hi All,
I have an access database recently ported to SQL server which is working perfectly. Just have one problem which is encryption.

Basically what I would like to do is either encrypt a whole table or a whole database or just several fields in a table, whichever is easiest. I know there are several ways to encrypt the data but I am not sure of the best solution for my situation and how to implement it. I have 4 fields in a table which need encrypting but am quite happy to encrypt the whole database or table if it is easier. Does anyone have any solutions to this?

Many Thanks!
Alex
 

ButtonMoon

Registered User.
Local time
Today, 05:57
Joined
Jun 4, 2012
Messages
304
The easiest way to encrypt the entire database is to use Transparent Data Encryption. This effectively protects data on the filesystem or in backups and assuming that's what you wanted to achieve then TDE is the way to do it. In case it's not obvious, the encryption won't provide you with any extra security against people accessing the data through a normal database connection. You still need to secure the SQL Server database through the usual user/role security model and only grant users access to the data you want them to access.
 

SQL_Hell

SQL Server DBA
Local time
Today, 05:57
Joined
Dec 4, 2003
Messages
1,360
Here is the procedure I use:

--Service Master Key - already there when SQL installed!
USE master
GO

--Create the master key - service master key encrypts the DMK for the master database
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'Hrd2GessP@$$w0rd!'

--Create TDE Cert
--DMK of the master database creates a certificate in the master database
--the certificate is used to encrypt the database encryption key (DEK) in the user database
CREATE CERTIFICATE MyEncryptionCert
WITH SUBJECT = 'My Encryption Certificate'

--query
SELECT name, pvt_key_encryption_type_desc
FROM sys.certificates
WHERE name = 'MyEncryptionCert'

--create DB
--create DEK
USE MyEncryptionDB
GO

--create DEK that is encrypted by the certificate
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyEncryptionCert

--BACKUP CERT AND PRIVATE KEY AND PRACTICE RESTORES ETC!
--Enable TDE
--The entire user database is secured by the DEK of the user database by using TDE
ALTER DATABASE MyEncryptionDB SET ENCRYPTION ON

--querying TDE views
SELECT name, is_encrypted FROM sys.databases

--tempDB is too
SELECT
dbs.name,
keys.encryption_state,
keys.percent_complete,
keys.key_algorithm,
keys.key_length
FROM
sys.dm_database_encryption_keys AS keys
INNER JOIN sys.databases AS dbs ON keys.database_id = dbs.database_id

--test
SELECT * FROM tbTest
 

Users who are viewing this thread

Top Bottom