Database Table Encryption in SQL Server 2008

Question

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

I do not want users to see database tables.

How can I encrypt tables in my database?

Answer 1

Here you have different options.

• You can use symmetric encryption for your data:

  CREATE TABLE sales (...)

Create a 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 to 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 

Enable Encryption:

 ALTER DATABASE MySecretDB SET ENCRYPTION ON 

• You can use BitLocker - full volume encryption

Answer 2

Encryption won't help - SQL Server-level encryption encrypts files. Data is visible after logging in.

The only correct solution is called "programming." Basically, the client / server host and users do not have a database connection.

Alternatively, you can use table permissions + application password to elevate the rights for the application (not the user), but this is also unsafe (because you must specify the password somewhere).

Answer 3

Users will not see the contents of the tables unless you grant them SELECT permission. This means that they should NOT be connected as members of the dbo group. Instead, create one or more groups for different user security groups and assign permissions to the database objects that you want them to have access to these groups.

Please note: if you have a group of objects that will be jointly allowed for one or more user groups, you can create these groups in a separate scheme , and then grant the user group access rights to the whole scheme. This allows you to resolve a one-time case when adding database objects to the schema.