Link to home
Start Free TrialLog in
Avatar of RamkumarSN
RamkumarSNFlag for India

asked on

Under the Login Screen how to match the Encrypted password with the user input of txtPassword.txt Database SQL Server 2005. Front End VB.NET 2012

We created EncryptPassword column to varbinary(256) under SQL Server 2005 Standard Edition SP1

Following are the code :

CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 'BAuthority'
GO

CREATE CERTIFICATE EncryptTestCert
WITH SUBJECT = 'BAuthority'
GO

CREATE SYMMETRIC KEY TestTableKey
WITH ALGORITHM = TRIPLE_DES ENCRYPTION
BY CERTIFICATE EncryptTestCert
GO

ALTER TABLE MasterPassword
ADD EncryptPassword VARBINARY(256)
GO

Insert Into MasterPassword
(UserName, Password, UserLevel, EncryptPassword)
Values( 'sekar', 'sekar', 'L', EncryptByKey(KEY_GUID('TestTableKey'), 'sekar') )


OPEN SYMMETRIC KEY TestTableKey
DECRYPTION BY CERTIFICATE EncryptTestCert
Select UserName, Password, EncryptPassword, CONVERT(nvarchar(15),DECRYPTBYKEY(EncryptPassword)) AS DecryptPassword
From MasterPassword
Where ( ( UserName = 'sekar' ) and
( CONVERT(nvarchar(15),DECRYPTBYKEY(EncryptPassword)) = 'sekar' ) )

0 rows selected. why ? Any mistake in the above SQL

if we search only with the UserName = 'sekar',  1 row is selected,   CONVERT(nvarchar(15),DECRYPTBYKEY(EncryptPassword)) AS DecryptPassword displays with the Chinese language why, it must display sekar only, why in Chinese language.  Kindly see the screen shot in the attached excel file.

Attached the excel file for your reference.

Thanks.

S.N.Ramkumar.
Workings.xlsx
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

No attachments available.
Avatar of RamkumarSN

ASKER

How to match the password text character with the Varbinary data type.

We tried the following

Where ( ( UserName = 'sekar' ) and
( CONVERT(nvarchar(15),DECRYPTBYKEY(EncryptPassword)) = 'sekar' ) )


which is not working. How to solve it.

Attached excel file for the various screen shots.

S.N.Ramkumar.
ASKER CERTIFIED SOLUTION
Avatar of RamkumarSN
RamkumarSN
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
While converting character to binary, size must not be specified.
.
Character length must not be specified.
Character length must not be specified. Thanks.
Trial and error basis, debug, debug, debug ... Finally got it.