Encryption and Decryption in SQL Server 2005

In this tutorial we shall see how to implement Microsoft SQL Server 2005 Encryption Techniques. Microsoft SQL Server 2005 Encryption are a good way to encrypt sensitive and confidential fields such as passport number, credit card number, phone numbers, and other user information.

Many applications require security of important data in today’s fiercely completive business environment. A good solution, Microsoft SQL Server 2005 provides, they are Certificates, Symmetric Keys, and Asymmetric Keys. The Encryption technique take place at the database level and are created by using a Database Master Symmetric Key. A Database Master Symmetric Key is generated at the server level. The Windows Data Protection API automatically encrypts Database Master Symmetric Key by using the credentials of the SQL Server service account.

SQL Server 2005 currently supports two types of encryption

1) Symmetric Encryption – the encryption algorithms supported include: AES, DES, RC2, and RC4. Symmetric keys can be encrypted by:

• Another symmetric key created by a database user, which was derived from a user-supplied password
• An asymmetric key
• Another symmetric key
• A certificate’s public key

2) Asymmetric Encryption – the RSA encryption algorithm is used to support asymmetric encryption and can be used with a 512-, 1,024-, or 2,048-bit key. Asymmetric encryption is used to encrypt symmetric keys, which are then stored in the database. Asymmetric keys can be encrypted by:

• A symmetric key created by a database user, which was derived from a user-supplied password
• The Database Master Key
• Certificates: SQL Server 2005 supports the IETF’s (Internet Engineering Task Force) certificate standard X.509 version 3 (X.509v3). This support can be used to generate certificates from within SQL Server, or to use any externally issued certificates.
• Key Hierarchy

Implementation of Encryption techniques

This tutorial is a step-by-step guide for encryption and decryption algorithms in MS SQL Server 2005.

Creating Table

We are going to implement the encryption technique on sample table Employee. Below is the SQL script for encryption and decryption algorithms.

CREATE TABLE [dbo].[Employee](
[EmpID] [int] IDENTITY(1,1) NOT NULL,
[EmpName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EmpPhone] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EmpAddrerss] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[EmpID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Insert the record in Employee table.

Insert into Employee values('Sumana','9874563210','315 Grant Hayat Mumbai')
Insert into Employee values('Chmana','985525210','41 A Saki Naka Mumbai')

Creating Master Key

CREATE MASTER KEY ENCRYPTION BY PASSWORD ='Employee@2009'

Before creating a master key you can execute the below SQL select query to check if a master key is exists or not which you are trying to create.

SELECT * FROM SYS.SYMMETRIC_KEYS

Creating a Certificate

CREATE CERTIFICATE EmployeeTable ENCRYPTION BY PASSWORD='Employee@2009'
        WITH SUBJECT ='Encrypt Employee Data',
        START_DATE='10/20/2008',
        EXPIRY_DATE='12/31/2025'

Creating a Symmetric Key

CREATE SYMMETRIC KEY Sym_EmployeeData WITH ALGORITHM = TRIPLE_DES, KEY_SOURCE = 'EmpTable',
IDENTITY_VALUE = 'emp' ENCRYPTION BY CERTIFICATE EmployeeTable, PASSWORD='Employee@2009'

Altering Database Tables to Store Encrypted Data

ALTER table Employee ALTER column EmpName nvarchar(MAX)
ALTER table Employee ALTER column EmpPhone nvarchar(MAX)
ALTER table Employee ALTER column EmpAddrerss nvarchar(MAX)

Encrypting Table Data

Update Employee set EmpName=EncryptByKey(Key_GUID('Sym_EmployeeData'),EmpName)
EmpPhone=EncryptByKey(Key_GUID('Sym_EmployeeData'),EmpPhone),
EmpAddrerss= EncryptByKey(Key_GUID('Sym_EmployeeData'),EmpAddrerss)

Decrypting Encrypted Data

OPEN SYMMETRIC KEY Sym_EmployeeData DECRYPTION BY CERTIFICATE EmployeeTable with password='Employee@2009'
SELECT EmpId,CAST(decryptbykey(EmpName) AS nvarchar) AS EmpName, EmpName,
CAST(decryptbykey(EmpPhone) AS nvarchar) AS EmpPhone, EmpPhone,
CAST(decryptbykey(EmpAddrerss) AS nvarchar) AS EmpAddrerss,
EmpAddrerss
From Employee

Output

Conclusion

This article has given you a few tips and insides in how you can use SQL Server encryption and decryption, we have implemented SQL Server data encryption techniques with sample SQL script in a simple application in Microsoft SQL Server 2005. Encryption and Decryption is an important aspect for database security in SQL Server 2005 database.

Related Posts

1. Ad hoc Reporting using SQL Server Reporting Services 2005
2. Troubleshooting SSRS Error “The report server is not responding”
3. SQL Server Cluster Resource Failed When Bringing Online
4. SQL Server – Maximum Capacity Specifications Comparison
5. SQL Server 2005 database DBA Checklist for database administrator

Tags: SQL Server 2005, SQL Server 2005 Encryption and Decryption, SQL Server Decryption, SQL Server Encryption