How To Encrypt and Decrypt Data In Mssql

 

Hello Friend Today In This Blog I Will Show You How To Create Delete Data In Mssql.In this article I describe how to  Encrypt and Decrypt text in SQL Server. Encryption and decryption string is much easier in SQL Server 2008. There is a way to encrypt a password and then store a password as VarBinary in a column by using EncryptByPassPhrase function. Encrypted column can be decrypted using the DECRYPTBYPASSPHRASE function.

As you can see, the ENCRYPTBYPASSPHRASE and DECRYPTBYPASSPHRASE functions are easy to use when you need to encrypt and decrypt strings. However, there's a caveat—you're responsible for managing and securing the passphrase. By default, no permissions are required to execute the ENCRYPTBYPASSPHRASE and DECRYPTBYPASSPHRASE functions and you don't have the ability to select the algorithm they use for encryption and decryption. So, depending on how your SQL Server environment is set up, a few people might be able to see the passphrase and even execute the dbo.Encrypt and dbo.Decrypt functions if they had access to their code.

However, there are ways to help secure passphrases, such as storing them in a table that resides in msdb or a master system database and allowing only DBAs with the sysadmin server role access to that table. For example, the code in Listing 4 creates a table named passPhraseEncoders and inserts the 'SQL SERVER 2008' passphrase into it.

EncryptByPassPhrase:

This function works on the Triple DES algorithm to encrypt the text. EncryptByPassPhrase

Syntax

ENCRYPTBYPASSPHRASE ('PASSPHRASE','text')

EncryptByPassPhrase has two mandatory arguments: PASSPHRASE (specifies the data string to be used to derive an encryption key) and text (specifies text to be encrypted). Text should be of VarBinary type.

DECRYPTBYPASSPHRASE:

DECRYPTBYPASSPHRASE is used to decrypt the encrypted column. Similarly DECRYPTBYPASSPHRASE takes two two mandatory arguments: PASSPHRASE (generates the decryption key) and  text (specifies text to be decrypted).

Here Is An Example Related To Topic


Step 1 :Create database

Create database Protection

Step 2:Create table

Here In This step We Will Create table.


Here In This Step We Required Four Column With Name cid ,ename ,elocation and esalary  using Parameter bigint,varchar and float.


create table Scan
(
sids bigint,
sname varchar(50),
spassword varchar(50),
sPrice float
);

Step 3 :Create Insert Trigger Functionality

Here In This Step We Will Insert Data Using Insert Command.
Data Will Be Added Manually Using Insert Command.


Here We Will Encrypt Data Using EncryptByPassPhrase Functionality.


Insert Into Scan (sids,sname,spassword,sPrice)  values (1,'VIRAJ',EncryptByPassPhrase('Kayomacpro','ABC'),11000.2)


select *from Scan

Step 4 :Create Select Trigger Functionality
Here In This Step We Will View Data Using Select Command.

Data Will View Data Using Select Command.
Here We Will Decrypt Data Using DECRYPTBYPASSPHRASE Functionality.
select sids,sname,CONVERT(varchar(50),DECRYPTBYPASSPHRASE('Kayomacpro',spassword))as DecryptedPassword,sPrice from Scan

Here Is An Video  Related To Topic In Blog