How to decrypt a encrypted password which is in varbinary in SQL Server 2005?

Question

I have field called pasword varbinary(50). While inserting the data :

insert into sometable values(pwdencrypt(pasword)) it's working fine.

The problem is when I am comparing the password with the pwdcompare(@oldpwd,@curPwd) method each time it's returning false. So how to compare the old password in sql server 2005.

The sample code is given below

begin

declare @cmp int

declare @result varbinary(50)

declare @pwd varbinary(50)

set @result = convert(varbinary(50),pwdencrypt('test'))

set @pwd= convert(varbinary(50),pwdencrypt('test'))

set @cmp=pwdcompare(@result,@pwd)

if(@cmp=1)

begin

print 'true'

end

else

begin

print 'false'

end

Each time it's prints false after passing the same value also.

chaitanya vonaja · Answer

Hiwhat is the method or alogarithm u have adopted for encryption? I got some encryption and decryption code .It is TRIPLEDES Imports System Imports System.IO Imports System.Text Imports System.Security.Cryptography Imports System.Data Imports System.Data.SqlClient Public Class Form1 Dim cn As SqlConnection Dim cmd As SqlCommand Dim da As SqlDataAdapter Dim drr As SqlDataReader Dim TDES As New TripleDES() Dim ds As New DataSet Private Sub Button1_Click( ByVal sender As System.Object ByVal e As System.EventArgs) Handles Button1.Click cn = New SqlConnection( Server=vk-9425e50f68b0;User Id=sa;Password=runvav;DataBase=chaitanya ) Dim Name As String Dim Desg() As Byte Dim TDES As New TripleDES() Name = TextBox1.Text Desg = TDES.Encrypt(TextBox2.Text) Dim paramName As New SqlParameter( @Name  Name) Dim paramDesg As New SqlParameter( @Desg  Desg) cn.Open() cmd = New SqlCommand( Insert into VK(NameDesg)values(@Name@Desg)  cn) cmd.Parameters.Add(paramName) cmd.Parameters.Add(paramDesg) cmd.ExecuteNonQuery() cn.Close() MsgBox( Inserted ) End Sub Private Sub Button2_Click( ByVal sender As System.Object ByVal e As System.EventArgs) Handles Button2.Click Dim dr As DataRow Dim Desg As String Dim TDES As TripleDES TDES = New TripleDES() Dim lvi As ListViewItem Dim n As Integer Try cn = New SqlConnection( Server=vk-9425e50f68b0;User Id=sa;Password=runvav;DataBase=chaitanya ) cn.Open() cmd = New SqlCommand( Select * from VK where Name=' & TextBox1.Text & '  cn) drr = cmd.ExecuteReader If (drr.HasRows) Then While (drr.Read = True ) TextBox1.Text = drr( Name ).ToString n = TDES.Decrypt(drr.Item( Desg )) TextBox2.Text = n End While End If Catch ex As Exception MsgBox(ex.Message) End Try End Sub Private Sub Form1_Load( ByVal sender As System.Object ByVal e As System.EventArgs) Handles MyBase .Load Dim dr As DataRow Dim Desg As String Dim TDES As TripleDES TDES = New TripleDES() Dim lvi As ListViewItem Dim n As Integer Try cn = New SqlConnection( Server=vk-9425e50f68b0;User Id=sa;Password=runvav;DataBase=chaitanya ) cmd = New SqlCommand( Select * from VK   cn) ' drr = cmd.ExecuteReader ' If (drr.HasRows) Then ' While (drr.Read = True) ' TextBox1.Text = drr(Name).ToString ' n = TDES.Decrypt(drr.Item(Desg)) ' TextBox2.Text = n ' End While ' End If ' ' da = New SqlDataAdapter da.SelectCommand = cmd cn.Open() Dim ds As DataSet = New DataSet() da.Fill(ds VK ) For Each dr In ds.Tables( VK ).Rows TDES = New TripleDES() Desg = TDES.Decrypt(dr.Item( Desg )) lvi = New ListViewItem() lvi.Text = dr.Item( Name ) lvi.SubItems.Add(Desg) ListView1.Items.Add(lvi) Next Catch ex As Exception MsgBox(ex.Message) End Try End Sub End Class Public Class TripleDES Private key() As Byte = {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24} Private iv() As Byte = {65 110 68 26 69 178 200 219} Public Function Encrypt( ByVal plainText As String ) As Byte () ' Declare a UTF8Encoding object so we may use the GetByte ' method to transform the plainText into a Byte array. Dim utf8encoder As UTF8Encoding = New UTF8Encoding() Dim inputInBytes() As Byte = utf8encoder.GetBytes(plainText) ' Create a new TripleDES service provider Dim tdesProvider As TripleDESCryptoServiceProvider = New TripleDESCryptoServiceProvider() ' The ICryptTransform interface uses the TripleDES ' crypt provider along with encryption key and init vector ' information Dim cryptoTransform As ICryptoTransform = tdesProvider.CreateEncryptor( Me .key Me .iv) ' All cryptographic functions need a stream to output the ' encrypted information. Here we declare a memory stream ' for this purpose. Dim encryptedStream As MemoryStream = New MemoryStream() Dim cryptStream As CryptoStream = New CryptoStream(encryptedStream cryptoTransform CryptoStreamMode.Write) ' Write the encrypted information to the stream. Flush the information ' when done to ensure everything is out of the buffer. cryptStream.Write(inputInBytes 0 inputInBytes.Length) cryptStream.FlushFinalBlock() encryptedStream.Position = 0 ' Read the stream back into a Byte array and return it to the calling ' method. Dim result(encryptedStream.Length - 1) As Byte encryptedStream.Read(result 0 encryptedStream.Length) cryptStream.Close() Return result End Function Public Function Decrypt( ByVal inputInBytes() As Byte ) As String ' UTFEncoding is used to transform the decrypted Byte Array ' information back into a string. Dim utf8encoder As UTF8Encoding = New UTF8Encoding() Dim tdesProvider As TripleDESCryptoServiceProvider = New TripleDESCryptoServiceProvider() ' As before we must provide the encryption/decryption key along with ' the init vector. Dim cryptoTransform As ICryptoTransform = tdesProvider.CreateDecryptor( Me .key Me .iv) ' Provide a memory stream to decrypt information into Dim decryptedStream As MemoryStream = New MemoryStream() Dim cryptStream As CryptoStream = New CryptoStream(decryptedStream cryptoTransform CryptoStreamMode.Write) cryptStream.Write(inputInBytes 0 inputInBytes.Length) cryptStream.FlushFinalBlock() decryptedStream.Position = 0 ' Read the memory stream and convert it back into a string Dim result(decryptedStream.Length - 1) As Byte decryptedStream.Read(result 0 decryptedStream.Length) cryptStream.Close() Dim myutf As UTF8Encoding = New UTF8Encoding() Return myutf.GetString(result) End Function End Class