?
Solved

Confounding problem with EncryptByPassPhrase function in SQL?

Posted on 2009-02-16
3
Medium Priority
?
1,659 Views
Last Modified: 2012-05-06
I am absolutely stumped as to why this is happening.

If I use EncryptByPassPhrase() and feed it the value of a variable I'm passing to my stored proc, it only encrypts the first letter of the string and nothing else.  I discover this by decrypting the resulting varbinary value.   Why is it only encrypting the first letter?

HOWEVER if I hard code the string directly in the stored proc (see alternative in the attached code), it encrypts the entire string successfully.

Is this a bug, or am I missing something?  I want to pass a string and encrypt the entire string.  How can I do this successfully?
ALTER PROCEDURE [dbo].[myStoredProc]
    @Email nvarchar(256),
    @UserPassword nvarchar(256)
AS
BEGIN
 
-- do not change this passphrase because the encryption is symmetrical
DECLARE @passphrase nvarchar(16)
SET @passphrase = N'Passwords String';
 
DECLARE @PasswordEncode VARBINARY(256)
--******  THE FOLLOWING EXAMPLE ONLY ENCRYPTS THE FIRST LETTER OF THE @UserPassword string *****************
SET @PasswordEncode = EncryptByPassPhrase(@passphrase, @UserPassword);
----***** THIS EXAMPLE CORRECTLY ENCRYPTS THE ENTIRE STRING
-- SET @PasswordEncode = EncryptByPassPhrase(@passphrase, 'my big long string');
 
 
END

Open in new window

0
Comment
Question by:cacklebunny
  • 2
3 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23655723
I would recommend NOT encrypting user passwords, rather, hashing them.  There is no need to ever decrypt a password.

For user login validation, you simply hash the value they pass in and validate it.  Ideally, your hash would be "salted" so that a dictionary attack cannot be run against your user table.
ALTER PROCEDURE [dbo].[myStoredProc]
    @Email nvarchar(256),
    @UserPassword nvarchar(256)
AS
BEGIN
 
DECLARE @Passwordhash BINARY(16)
,@hashString nvarchar(max)
set @HashString = lower(@Email) + @userpassword
--******  THE FOLLOWING EXAMPLE ONLY ENCRYPTS THE FIRST LETTER OF THE @UserPassword string *****************
SET @Passwordhash = hashbytes('SHA1',@HashString)
 
 
 
 
END

Open in new window

0
 
LVL 2

Accepted Solution

by:
cacklebunny earned 0 total points
ID: 23655748
I appreciate your suggestion about not encrypting passwords, but in my case there is a need to decrypt the password because that's what the client wants, regardless of the risks I pointed out to them.

In any case, I found the answer to my question:  You need to pass a varchar type, not an nvarchar type and it will encrypt the full string.  Hope that helps someone else out there with the same issue.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23655787
I think you are wrong about what you've found as well.  As you can see from this example (and as document in http://technet.microsoft.com/en-us/library/ms190357.aspx), you CAN pass nvarchar into the function.
drop procedure up_TESTENC 
go
create procedure up_TESTENC 
 
    @Email nvarchar(256),
    @UserPassword nvarchar(256)
as
declare    @PasswordEncode VARBINARY(256)
 
 
 
DECLARE @passphrase nvarchar(16)
SET @passphrase = N'D795ECA4-285E-44CD-8078-CDE8DACCF20F';
 
--******  THE FOLLOWING EXAMPLE ONLY ENCRYPTS THE FIRST LETTER OF THE @UserPassword string *****************
SET @PasswordEncode = EncryptByPassPhrase(@passphrase, @UserPassword);
----***** THIS EXAMPLE CORRECTLY ENCRYPTS THE ENTIRE STRING
-- SET @PasswordEncode = EncryptByPassPhrase(@passphrase, 'my big long string');
 
SElect cast(@PasswordEncode as nvarchar(max))
SElect cast(decryptByPassPhrase(@passphrase, @PasswordEncode) as nvarchar(max))
 
go
exec up_TESTENC @Email=N'', @UserPassword = 'ASDF@!#%SDVAW%WR'

Open in new window

0

Featured Post

Independent Software Vendors: 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

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Integration Management Part 2
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

839 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