Opening a symmetric key in a stored procedure

Question

Opening a symmetric key in a stored procedure

maraclec

Ten Centuries

Points: 1175
More actions
June 12, 2007 at 2:58 am

#372216

Hi all,
I've created a symmetric key using an asymmetric key with a password. I would like to open the symmetric key in a stored procedure and pass in the password as a parameter.
I get an error on the statement below
OPEN SYMMETRIC KEY SymKey DECRYPTION BY ASYMMETRIC KEY AsymKey WITH PASSWORD = @pass
Thank you for any help,
Chuck

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What's the error you get?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

maraclec Ten Centuries Points: 1175 More actions · Answer 2

Hi,

Here is the code to the stored procedure. This is a test stored procedure to test the ability with.

CREATE PROCEDURE [dbo].[spEncrypt]

@passvarchar(10),

@plaintext varbinary(8000),

@ciphertext varbinary(8000) output

AS

BEGIN

OPEN SYMMETRIC KEY SymKey

DECRYPTION BY CERTIFICATE CertKey WITH PASSWORD = @pass

SET @ciphertext = encryptbykey(key_guid('SymKey'), @plaintext)

CLOSE SYMMETRIC KEY SymKey

END

The error that I get is most helpful

Msg 102, Level 15, State 1, Procedure spEncrypt, Line 8

Incorrect syntax near '@Pass'.

Thanks

-=JLK=- SSCertifiable Points: 5315 More actions · Answer 3

/*------ Set up the encryption information -----

USE TestEncryptionDB;

go

DROP MASTER KEY;

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'My p@55w0Rd';

GO

DROP CERTIFICATE cert_test

go

CREATE CERTIFICATE [cert_test]

ENCRYPTION BY PASSWORD = 'Another p@55w0Rd'

WITH SUBJECT = 'Test Certificate';

GO

DROP SYMMETRIC KEY key_test

go

CREATE SYMMETRIC KEY [key_test] WITH

KEY_SOURCE = 'Key Source generation bits',

ALGORITHM = triple_des,

IDENTITY_VALUE = 'Key Identity generation bits'

ENCRYPTION BY CERTIFICATE [cert_test];

GO

------- End setup -------------------------------*/

--Create the User Stored Procedure

IF OBJECT_ID (N'dbo.uspEncrypt') IS NOT NULL

DROP procedure dbo.uspEncrypt;

GO

CREATE PROCEDURE [dbo].[uspEncrypt]

@Pass varchar(20),

@plaintext varchar(8000),

@ciphertext varbinary(8000) output

AS

BEGIN

declare @sql nvarchar(500)

--set @sql = 'OPEN SYMMETRIC KEY key_test DECRYPTION BY CERTIFICATE cert_test WITH PASSWORD = ''Another p@55w0Rd'''

--print @sql

set @sql = 'OPEN SYMMETRIC KEY key_test DECRYPTION BY CERTIFICATE cert_test WITH PASSWORD = ''' + @pass + ''''

--print @sql

exec sp_executesql @sql

SET @ciphertext = encryptbykey(key_guid('key_test'), @plaintext)

--------------------------------------------------------

--What is really weird is the following throws an error when the parameter password is used (second set sql statement)

--but not the first.

--CLOSE SYMMETRIC KEY key_test

-----------------------------------------------------

set @sql = 'CLOSE SYMMETRIC KEY key_test'

exec sp_executesql @sql

END

/*------- Test the function ---------------

declare @ciphertext varbinary(8000)

exec dbo.uspEncrypt 'Another p@55w0Rd', 'This is my text', @ciphertext out

print @ciphertext

--------- end of test ---------------------*/

maraclec Ten Centuries Points: 1175 More actions · Answer 4

Thanks for that.

I had thought of executing a sql string, but was hoping that I was forgetting something and that there was a simpler (nicer) way. As in just putting the parameter into the WITH PASSWORD = statement.

Thank you for your help,

Chuck

-=JLK=- SSCertifiable Points: 5315 More actions · Answer 5

Your welcome. I noticed I didn't post the comments at the top that I intended to. Basically I tried everything I could think of to get the "Open Symmetric Key" statement to take a parameter but it would not. Which is why I ended up with the sp_executeSQL.

Another weird problem was the "Close Symmetric Key" statement, if I ran the sp_executesql string with a hard coded password the close statement worked, when the sp_executesql string had the parameter concatenated into it, it failed unless it also was executed via the sp_executesql statement. Though I never checked the return code from sp_executesql so don't know if it was also failing there also which could lead to a memory leak I guess.

James.