June 2, 2010 at 2:48 am
Hi,
I have a SP that when executed, encrypts the password column.
When after execution i check for the encrypted and the decrypted value, they are returned.
However, when I call this SP within another SP, and then check for the encrypted and decrypted values, It returns the encrypted value, but the decrypted value returns NULL
HELP!!!
June 2, 2010 at 3:04 am
namrata.dhanawade-1143388 (6/2/2010)
Hi,I have a SP that when executed, encrypts the password column.
When after execution i check for the encrypted and the decrypted value, they are returned.
However, when I call this SP within another SP, and then check for the encrypted and decrypted values, It returns the encrypted value, but the decrypted value returns NULL
HELP!!!
A guess at best without seeing the code. Can you post the code of both sprocs please?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 2, 2010 at 3:30 am
actual SP
ALTER PROCEDURE InsIdentification
(
@TypeIDint
, @Valuevarchar(255)
, @Passwordvarchar(50)
, @IdentificationIDint OUTPUT
)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET XACT_ABORT ON;
BEGIN TRY
OPEN SYMMETRIC KEY SSN_Key_001
DECRYPTION BY CERTIFICATE IdentificationCertificate;
BEGIN TRANSACTION
SELECT @IdentificationID = ISNULL ((MAX (IdentificationID) + 1), 1)
FROM dbo.Identification WITH (NOLOCK)
INSERT dbo.Identification (IdentificationID, TypeID, Value, Password)
SELECT @IdentificationID, @TypeID, @Value, case @Password
when Null then NULL
else EncryptByKey(Key_GUID('SSN_Key_001'),
Password)
end
COMMIT
END TRY
BEGIN CATCH
-- Raise an error with the details of the exception
END CATCH
----------------------------------------------------------------------------------------------
The SP calling this SP
ALTER PROCEDURE InsProduct
(
@AgentIDint
, @ProductIDint = 21
, @TypeIDint
, @CPWNReference int= NULL
, @Valuevarchar (255) = NULL
, @Passwordvarchar(50)
, @ProductInsIDint OUTPUT
)
AS
SET NOCOUNT ON
SET XACT_ABORT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
Declare @IdentificationID Int
Select @IdentificationID = NULL
BEGIN TRY
BEGIN TRAN
IF @IdentificationID IS NULL and @ProductID = 21
BEGIN
EXEC ServicesDatabase.dbo.SRVInsIdentificationService
@TypeID = @TypeID
, @Value= @Value
, @Password= @Password
, @IdentificationID= @IdentificationID OUTPUT
COMMIT TRAN
END TRY
BEGIN CATCH
--
END CATCH
June 2, 2010 at 4:14 am
Which columns on what table are you checking for the encrypted and decrypted values? Column Password of dbo.Identification should contain the encrypted value.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 2, 2010 at 4:19 am
Also, note that your encryption sproc is called InsIdentification, and the sproc you are calling within your second sproc is called ServicesDatabase.dbo.SRVInsIdentificationService - are they the same?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 2, 2010 at 4:56 am
Yes, they are the same. There is no syntax problem in the code. Everything else that the SP is supposed to return is coming out fine, except for the @Password(I want to display the decrypted value)
June 2, 2010 at 5:03 am
namrata.dhanawade-1143388 (6/2/2010)
Yes, they are the same. There is no syntax problem in the code. Everything else that the SP is supposed to return is coming out fine, except for the @Password(I want to display the decrypted value)
There are problems with the code as it stands, but that's a different issue.
Where/how do you think the column (or variable) Password is being decrypted?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 2, 2010 at 6:08 am
after the inserts using the 1st SP, I just take the ID and try to decrypt using
SELECT A.Value,
CONVERT(varchar, DecryptByKey(A.Password)),
A.Password
FROM ServicesDatabase.dbo.IdentificationService A WITH (NOLOCK)
where IdentificationID = @IdentificationID
It gives me the decrypted value
UserName TestTesting123 0x00F41E6A8ECB7F479AF5A6826828
However, if I do the same after the 2nd SP, I get
UserName TestNULL0x00F41E6A8ECB7F479AF5A6826828
June 2, 2010 at 6:17 am
namrata.dhanawade-1143388 (6/2/2010)
after the inserts using the 1st SP, I just take the ID and try to decrypt usingSELECT A.Value,
CONVERT(varchar, DecryptByKey(A.Password)),
A.Password
FROM ServicesDatabase.dbo.IdentificationService A WITH (NOLOCK)
where IdentificationID = @IdentificationID
It gives me the decrypted value
UserName TestTesting123 0x00F41E6A8ECB7F479AF5A6826828
However, if I do the same after the 2nd SP, I get
UserName TestNULL0x00F41E6A8ECB7F479AF5A6826828
after the 2nd SP, you get the result:
UserName TestNULL0x00F41E6A8ECB7F479AF5A6826828
The second column of this result set contains the value 'Test', which is the value returned from CONVERT(varchar, DecryptByKey(A.Password)) - at least it is as far as you have posted. I think you need to doublecheck the logic of your test environment.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 2, 2010 at 6:25 am
It gives me the decrypted value
UserNameTest Testing123 0x00F41E6A8ECB7F479AF5A6826828
However, if I do the same after the 2nd SP, I get
UserNameTest NULL 0x00F41E6A8ECB7F479AF5A6826828
'UserNameTest' was a string -- A.Value
June 2, 2010 at 6:30 am
namrata.dhanawade-1143388 (6/2/2010)
It gives me the decrypted valueUserNameTest Testing123 0x00F41E6A8ECB7F479AF5A6826828
However, if I do the same after the 2nd SP, I get
UserNameTest NULL 0x00F41E6A8ECB7F479AF5A6826828
'UserNameTest' was a string -- A.Value
Ok my misunderstanding.
Do you actually have a value for A.Password for the decryption function to operate against? You're showing a value of 0x00F41E6A8ECB7F479AF5A6826828.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 2, 2010 at 7:52 am
Yes.
0x00F41E6A8ECB7F479AF5A6826828 is the encrypted value for the password
now I want to decrypt it (that is get it back in the varchar format)
June 2, 2010 at 8:08 am
Hi Namrata,
All I found was in the first SP in line number 28 you are passing "password". In that place you should pass the variable "@Password".
Is this cause the problem?
June 2, 2010 at 8:25 am
namrata.dhanawade-1143388 (6/2/2010)
Yes.0x00F41E6A8ECB7F479AF5A6826828 is the encrypted value for the password
now I want to decrypt it (that is get it back in the varchar format)
So what you are really trying to do is simply decrypt a value in a column in a table?
Have you tried the following:
1. Add a new row to your table using the stored procedure InsIdentification and a given password, say 'wednesday'
2. Add a new row to the table using the stored procedure InsIdentification called by the second stored procedure, using the same password 'wednesday'
3. Compare the value for password for both rows
Do they have the same (encrypted) value?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply