RamkumarSN
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('Tes tTableKey' ), 'sekar') )
OPEN SYMMETRIC KEY TestTableKey
DECRYPTION BY CERTIFICATE EncryptTestCert
Select UserName, Password, EncryptPassword, CONVERT(nvarchar(15),DECRY PTBYKEY(En cryptPassw ord)) AS DecryptPassword
From MasterPassword
Where ( ( UserName = 'sekar' ) and
( CONVERT(nvarchar(15),DECRY PTBYKEY(En cryptPassw ord)) = '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),DECRY PTBYKEY(En cryptPassw ord)) 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
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('Tes
OPEN SYMMETRIC KEY TestTableKey
DECRYPTION BY CERTIFICATE EncryptTestCert
Select UserName, Password, EncryptPassword, CONVERT(nvarchar(15),DECRY
From MasterPassword
Where ( ( UserName = 'sekar' ) and
( CONVERT(nvarchar(15),DECRY
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),DECRY
Attached the excel file for your reference.
Thanks.
S.N.Ramkumar.
Workings.xlsx
No attachments available.
ASKER
How to match the password text character with the Varbinary data type.
We tried the following
Where ( ( UserName = 'sekar' ) and
( CONVERT(nvarchar(15),DECRY PTBYKEY(En cryptPassw ord)) = 'sekar' ) )
which is not working. How to solve it.
Attached excel file for the various screen shots.
S.N.Ramkumar.
We tried the following
Where ( ( UserName = 'sekar' ) and
( CONVERT(nvarchar(15),DECRY
which is not working. How to solve it.
Attached excel file for the various screen shots.
S.N.Ramkumar.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
While converting character to binary, size must not be specified.
ASKER
.
ASKER
Character length must not be specified.
ASKER
Character length must not be specified. Thanks.
ASKER
Trial and error basis, debug, debug, debug ... Finally got it.