CODEWITHSUNDEEP
asp.netsql-serversql-server-2008encryption
Deepak Setia
Deepak Setia

Reputation: 45

How to encrypt data in sql server 2012 or sql server 2008 r2?

I want to encrypt the username and password both in sql tables . is it useful(if any method for data encryption in Sql Server) or i should apply for the Encryption and Decryption keys from front end.

till now i m using encryption and decryption from the front end using HttpUtility and base 64 method .

Upvotes: 0

Views: 526

Answers (2)

Rajesh
Rajesh

Reputation: 1620

You can use the PWDENCRYPT and PWDCOMPARE like @ Paresh J in his comment during new user Insertion use PWDENCRYPT like

Declare @Uname Varchar(250)='User2'
Declare @Pwd Varchar(250)='password'

Declare @UserTbl Table
(
id int identity(1,1),
Uname Varbinary(250),
Pwd Varbinary(250)
)

Insert Into @UserTbl(Uname,Pwd)
select PWDENCRYPT(@Uname),PWDENCRYPT(@Pwd)

and during the login of that user use PWDCOMPARE like

Declare @UnameEncr Varbinary(max)
Declare @PwdEncrypt Varbinary(max)

Select @UnameEncr=Uname,@PwdEncrypt=Pwd from @UserTbl where id=1

Select LoginMessage=Case When PWDCOMPARE(@Uname,@UnameEncr)=1 
                              and PWDCOMPARE(@Pwd,@PwdEncrypt)=1  
                              Then 'Correct Username / Password' 
                              else 'Incorrect Username / Password' end

Upvotes: 0

Dhaval
Dhaval

Reputation: 2379

Column-level encryption (aka cell-level encryption) was introduced in SQL Server 2005 and is available in all editions of SQL Server, including the free SQL Server Express edition. To use cell-level encryption, the schema must be changed to varbinary, then reconverted to the desired data type. This means the application must be changed to support the encryption-decryption operation; in addition, it can affect performance. Encryption of the database occurs at the page level, but when those pages are read to buffer pool, they're decrypted. Data can be encrypted using a passphrase, an asymmetric key, a symmetric key, or a certificate. The supported algorithms for column-level encryption are AES with 128,196,256 bit keys and 3DES. To learn more about column-level encryption

For Information Please Read This article http://technet.microsoft.com/en-us/library/ms179331.aspx

Upvotes: 1

Related Questions