Encrypting database tables in SQL Server 2008

Question

I have a Windows application using a database in SQL Server 2008.

I do not want users to see the database tables.

How can I encrypt tables in my database?

Answer 1

You have different options here.

• You can use symmetric encryption for your data:

  CREATE TABLE sales ( ... )

Create symmetric key:

CREATE CERTIFICATE cert_sales WITH SUBJECT = N'Sales certificate',
START_DATE = N'2009-01-01', EXPIRY_DATE = N'2018-12-31';

CREATE SYMMETRIC KEY symkey_sales WITH ALGORITHM = AES_256 
ENCRYPTION BY CERTIFICATE cert_sales

Encrypt data:

TRUNCATE TABLE sales;
OPEN SYMMETRIC KEY symkey_sales DECRYPTION BY CERTIFICATE cert_sales;
INSERT INTO sales() SELECT a, ENCRYPTBYKEY(Key_Guid(N'symkey_sales'), B) FROM T2;
CLOSE SYMMETRIC KEY symkey_sales;

Decrypt data:

OPEN SYMMETRIC KEY symkey_sales DECRYPTION BY CERTIFICATE cert_sales;
SELECT a, CAST(DecryptByKey(B) as nvarchar(100)) FROM sales;
CLOSE SYMMETRIC KEY symkey_sales;

• You can use asymmetric encryption for your data
• You can use Transparrent Data Encryption for encrypt all database files:

Create master key:

USE master
go
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'My$Strong$Password$123'

Create certificate:

CREATE CERTIFICATE DEK_EncCert WITH SUBJECT = 'DEK Encryption Certificate'

Create DEK:

USE MySecretDB
go
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE DEK_EncCert

Turn on encryption:

ALTER DATABASE MySecretDB SET ENCRYPTION ON

• You can use BitLocker - complete volume encryption

Answer 2

Encryption wont help - SQL Server level encryption encrypts the files. The data is visible once you log in.

The only proper solution is called "programming". Basically go client/server and don't have users connect to the database.

Alternatively you could use permissions on the tables + an application password to elevate the rights for the application (not the user), but that is unsafe too (because you have to put the password somewhere).