Avatar of RamkumarSN
RamkumarSN
Flag 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
Visual Basic.NETMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
RamkumarSN

8/22/2022 - Mon
Vitor Montalvão

No attachments available.
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
RamkumarSN

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
RamkumarSN

ASKER
While converting character to binary, size must not be specified.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
RamkumarSN

ASKER
.
RamkumarSN

ASKER
Character length must not be specified.
RamkumarSN

ASKER
Character length must not be specified. Thanks.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
RamkumarSN

ASKER
Trial and error basis, debug, debug, debug ... Finally got it.