• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 71
  • Last Modified:

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
0
RamkumarSN
Asked:
RamkumarSN
  • 7
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
No attachments available.
0
 
RamkumarSNAuthor Commented:
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.
0
 
RamkumarSNAuthor Commented:
Got the solution on own.
While converting the Binary to Character,  I have restricted the character size to 15 because of the column size by putting
CONVERT(nvarchar(15), DECRYPTBYKEY(EncryptPassword)) = 'sekar' )

no size restriction just

CONVERT(varchar, DECRYPTBYKEY(EncryptPassword) ) = 'sekar'
1
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
RamkumarSNAuthor Commented:
While converting character to binary, size must not be specified.
0
 
RamkumarSNAuthor Commented:
.
0
 
RamkumarSNAuthor Commented:
Character length must not be specified.
0
 
RamkumarSNAuthor Commented:
Character length must not be specified. Thanks.
0
 
RamkumarSNAuthor Commented:
Trial and error basis, debug, debug, debug ... Finally got it.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now