• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 91
  • 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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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