Encrypt your data with SQL Server

In this article i will explain you how to Encrypt and Decrypt data with T-SQL inbuit functions. There are two inbuilt functions exist in SQL for encryption and decryption of data. This functions follows Triple DES algorithms.

Encrypt your data with SQL Server


Now a days Security is a very critical aspect for web based or desktop applications. database technology continue to widely used for backup storage
of networked applications. hence, it is increasingly necessary to focus on security. We use database to keep our vital data.
but any one easily hack it or can break our security to get data from database. so the question arise, how to keep it secure ?

How to keep data secure in Database ?

The answer is Encryption, Sensitive and crucial data such as Security code, banking code, credit card numbers, passwords, etc.
should be protected from chopping

How SQL support us ?

SQL plays a very important role in security concepts. There are number of techiniques to protect our data from being hacked.
SQL is endlessly improving and has includes many great features that can help developers and DBA's to lock down the database system
and protect vital data from black users.
SQL Server 2005 itself decrease the application-based cryptography need. it uses a varity of algorithm to decrypt data such as
1. triple DES (Data Encryption Standard),
2. RC4
3. AES (Advanced Encryption Standard).
4. DES

SQL provide a encryption technique by using following 4 ways
1. ENCRYPTION by passphrase
2. ENCRYPTION by symmetric keys
3. ENCRYPTION by Asymmetric keys
4. ENCRYPTION by certificates

in this resource our meeting point is ENCRYPTION by passphrase

Let's travel through the encrypted world in which we will see how to encrypt and decrypt given data using SQL Server inbuilt transact functions.
There are inbuilt functions in database

ENCRYPTION by passphrase
This function Encrypt data with a passphrase, using the TRIPLE DES algorithm with a 128 key bit length.
here is syntax

EncryptByPassPhrase ( { 'passphrase' | @passphrase }
, { 'cleartext' | @cleartext }
[ , { add_authenticator | @add_authenticator }
, { authenticator | @authenticator } ] )

A phrase or any meaningful word that can be used as security code from which to generate a symmetric key.

This is a plain text to encrypt

This is the integer value Indicates whether an authenticator will be encrypted together with the cleartext.
1 if an authenticator will be added.

Data from which to derive an authenticator.

The return type of this T-SQL function is varbinary with maximum size of 8,000 bytes.
The positive point using a passphrase is, it is very easier to keep track of a meaningful phrase or sentence than to remember a
comparably long string of characters.

following example will clear all the points
in following example we have create a EMP table with 4 columns Sr_No, Name, Sal, Encrypt_Name

create table EMP
Sr_No Numeric(8) not null primary key ,
name varchar(50) not null,
Sal Numeric(6) not null,
Encrypt_Name varbinary(MAX)


now we will insert data in above table using EncryptByPassPhrase function

insert into EMP values ('1','ABC','1000',EncryptByPassPhrase('sec_code','ABC'))

//here sec_code is encryption key which uses Triple DES algorithm to encrypt


here if we select EMP table content then we will get this look



DECRYPTION by passphrase
This function Decrypt data that was previously encrypted with a passphrase.
here is syntax

DecryptByPassPhrase ( { 'passphrase' | @passphrase }
, { 'ciphertext' | @ciphertext }
[ , { add_authenticator | @add_authenticator }
, { authenticator | @authenticator } ] )

Is the security code that was used to key for decryption. if this key goes wrong it will decrypt current cipher text into corrupted text

It is a encrypted text to be decrypt

add_authenticator, authenticator:
This is the integer value Indicates whether an authenticator will be encrypted together with the cleartext.
authenticator is the Data from which to derive an authenticator.

example will clear the way, in following example we will fetch the cipher data and decrypt it to original format.
here we will decrypt our data with secure key.

select convert(VARCHAR(100), decryptbypassphrase('sec_code', Encrypt_Name)) as decrypt from emp where sr_no=1


This is a small attempt to give you short idea of encryption using SQL.
We will see the remaining Encryption techniques in later version of this resource.
Suggestion are most welcome



No responses found. Be the first to comment...

  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: