November 11, 2010 at 10:47 am
hi all
i'm trying to write a function which will allow you to insert a record into a table, encrypting the password value as the data is inserted. So for example:
insert into users
select dbo.EncryptPwds('mypassword')
And the code in my function is:
CREATE FUNCTION EncryptPwds (@pwd varchar(20))
RETURNS varbinary
AS
BEGIN
DECLARE @DBAPassphrase varchar(128);
DECLARE @cryptPWD varbinary(max);
SET @DBAPassphrase = 'The DBAs wield the power!';
SET @cryptPWD = EncryptByPassphrase(@DBAPassphrase, CAST(@pwd AS VARBINARY(MAX)))
RETURN(@cryptPWD)
END
However, it's writing the password as null into the table. I can see that by selecting from the table and i get 0x01 which means NULL.
Please can someone help??! I can't figure out why it's doing this...
Thanks
Doodles
November 16, 2010 at 3:08 am
It should be RETURNS varbinary(MAX)
CREATE FUNCTION EncryptPwds (@pwd varchar(20))
RETURNS varbinary(MAX)
AS
BEGIN
DECLARE @DBAPassphrase varchar(128);
DECLARE @cryptPWD varbinary(max);
SET @DBAPassphrase = 'The DBAs wield the power!';
SET @cryptPWD = EncryptByPassphrase(@DBAPassphrase, CAST(@pwd AS VARBINARY(MAX)))
RETURN(@cryptPWD)
END
November 16, 2010 at 4:50 am
Hi Suresh
That's worked - thanks so much! Seems obvious now 🙂 In fact, there is no need to cast the @pwd variable within the EncryptByPassphrase function. Simply returning as varbinary max as you have suggested is good enough.
Thanks again!
Doodles
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply