Solved

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

Posted on 2016-09-12
9
57 Views
Last Modified: 2016-09-21
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
Comment
Question by:RamkumarSN
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
9 Comments
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 41795528
No attachments available.
0
 

Author Comment

by:RamkumarSN
ID: 41797525
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
 

Accepted Solution

by:
RamkumarSN earned 0 total points
ID: 41802575
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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

Author Comment

by:RamkumarSN
ID: 41802682
While converting character to binary, size must not be specified.
0
 

Author Comment

by:RamkumarSN
ID: 41802683
.
0
 

Author Comment

by:RamkumarSN
ID: 41802684
Character length must not be specified.
0
 

Author Comment

by:RamkumarSN
ID: 41802685
Character length must not be specified. Thanks.
0
 

Author Closing Comment

by:RamkumarSN
ID: 41808399
Trial and error basis, debug, debug, debug ... Finally got it.
0

Featured Post

Industry Leaders: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question