Open encryption key in stored procedure

Hi,
I would like to create a stored procedure, which accept RunAsUser, MasterKeyPassword and also ASymmetricKeyPassword. In this stored procedure, it call OPEN MASTER KEY and etc. I put these code in stored procedure so that my support staff can call it when doing maintenance by passing the parameter. I don't expect them to remember OPEN MASTER KEY and etc syntax.

However, I hit error "Invalid Syntax" when I run my code as below. Any ideas?

Thank you

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE GrantMe
-- Add the parameters for the stored procedure here
@.RunAsUser varchar(20), @.MasterKey varchar(30), @.ASKey varchar(30)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

EXEC AS USER=@.RunAsUser;

OPEN MASTER KEY DECRYPTION BY PASSWORD=@.MasterKey;

OPEN SYMMETRIC KEY HRMS
DECRYPTION BY ASYMMETRIC KEY FlexHRMS WITH PASSWORD=@.AsKey ;
END
GO

I don't believe DDL supports parameterization. You could use dynamic SQL, but it should be easier to simply use the DecryptByKeyAutoAsymKey built-in which will automatically open the symmetric key for you so you don't have to use the OPEN command.

Sung

|||

BTW, you should not have to call OPEN MASTER KEY if the ASYMMETRIC KEY is encrypted by password.

Sung

|||

DDL statements don’t support variables as arguments, therefore you need to change the OPEN MASTER KEY and OPEN SYMMETRIC KEY statements. Unfortunately , you will need to use dynamic SQL (the non-parameterized form) in order to execute such statements with user-defined content. Because you will need to create non-parameterized dynamic SQL, I really recommend reading about SQL injection and how to prevent it; below I included a few links regarding SQL Injection:

· Dynamic SQL & SQL Injection http://blogs.msdn.com/raulga/archive/2007/01/04/dynamic-sql-sql-injection.aspx

· SQL Injection http://msdn2.microsoft.com/en-us/library/ms161953.aspx

· New SQL Truncation Attacks And How To Avoid Them http://msdn.microsoft.com/msdnmag/issues/06/11/SQLSecurity/default.aspx

· Stop SQL Injection Attacks Before They Stop You http://msdn.microsoft.com/msdnmag/issues/04/09/sqlinjection/default.aspx

· Second-order Code Injection Attacks (http://www.ngssoftware.com/papers/SecondOrderCodeInjection.pdf)

I have some additional comments that hopefully will also help.

I am assuming that the intention of this SP is to open the DB master key (meaning it is not protected by the service master key) and open the symmetric key HRMS, leaving both keys opened in order to allow both of them to be used later in the same session, correct?

I would recommend adding comments specifying the intention of opening the keys and not closing them is intentional.

I am also assuming that the impersonation (EXECUTE AS USER) takes place because the calling context doesn’t have direct permission over either the symmetric key or the asymmetric key protecting it. Because the caller would need IMPERSONATE USER permission on @.RunAsUser, and this permission allows the calling user to impersonate that principal anytime, you may want to consider using EXECUTE AS in the module definition, or use digital signatures for allowing access to the keys instead.

I hope this information will be useful.

-Raul Garcia

SDE/T

SQL Server Engine

|||Hi,
Thank you for you comments.

>>digital signatures for allowing access to the keys instead.

Any reference for this?

Thank you

|||

Yes, see the following link:

http://msdn2.microsoft.com/en-us/library/ms181700.aspx

For examples, I have a few on my blog:

http://blogs.msdn.com/lcris/archive/2005/06/15/429631.aspx
http://blogs.msdn.com/lcris/archive/2006/01/13/512829.aspx

Thanks
Laurentiu