Key Maintenance and Stored Procedures

Hot Topic

Change partition Source through XMLA
Developer Edition Remote connection and related issues
Merge Replication downloading rows immediately after uploading SQL 2005
capture line of flat file [Error]
Linked Server issue
multiple datasets
Client tools, what is it for and what bring us?
Finding the Max Value
SQL 2000 database upgraded to SQL 2005
Allowing Users to Filter on a Report Column

SQL Server

how to perform this query to return data?
Sending Table Variable Argument using MSFT JDBC Driver
Cannot configure SQL Server Express 2005 for remote connections
How to show a line between table column headers and the details
Not Associated with a trusted SQL Server connection
SQL Management Express Linked Server
Problems with an insert
Varchar of length > 8000 causing problems
How the windows forms application get the notification when record inserted in Table
Scripting Task and VBA Macros

Key Maintenance and Stored Procedures

Basically I would like to ask whether parameters can be used to pass the value of the 'symmetric key id', 'certificate' and optionally 'password' to a stored procedure that uses encryption functions.

The reason this is appealing is that when encryption keys etc change over time (we have a requirement to decrypt data, destroy and create new keys, then encrypt data every time we lose a staff - don't ask), as we would be passing the value of keys, passwords and certificates as parameters to a standard stored procedure.

Hardcoded Example (Working)

USE PSS

GO

CREATE PROC insert_payer_ba

-- define parameters

@param_rec_id NVARCHAR(MAX),

@param_bsb NVARCHAR(MAX),

@param_account NVARCHAR(MAX),

@param_account_name NVARCHAR(MAX)

AS

BEGIN

OPEN SYMMETRIC KEY bartlett_sym

DECRYPTION BY CERTIFICATE bartlett_cert

WITH PASSWORD = 'Bartlett12_3';

DECLARE @en_rec_id varbinary(max);

SELECT @en_rec_id = EncryptByKey(Key_GUID('bartlett_sym'),@param_rec_id);

DECLARE @en_bsb varbinary(max);

SELECT @en_bsb = EncryptByKey(Key_GUID('bartlett_sym'),@param_bsb);

DECLARE @en_account varbinary(max);

SELECT @en_account = EncryptByKey(Key_GUID('bartlett_sym'),@param_account);

DECLARE @en_account_name varbinary(max);

SELECT @en_account_name = EncryptByKey(Key_GUID('bartlett_sym'),@param_account_name);

INSERT INTO [PSS].[dbo].[payer_ba_sym]

(rec_id, bsb, account, account_name)

VALUES (

@en_rec_id,

@en_bsb,

@en_account,

@en_account_name

);

CLOSE SYMMETRIC KEY bartlett_sym;

END

GO

PROPOSED USAGE (WHICH DOESN'T WORK)

USE PSS

GO

CREATE PROC insert_payer_ba

-- define parameters

@param_symkeyguid NVARCHAR(MAX), --(tried VARBINARY as well)

@param_cert NVARCHAR(MAX),

@param_certpass VARBINARY(MAX),

@param_rec_id NVARCHAR(MAX),

@param_bsb NVARCHAR(MAX),

@param_account NVARCHAR(MAX),

@param_account_name NVARCHAR(MAX)

AS

BEGIN

OPEN SYMMETRIC KEY @param_symkeyguid

DECRYPTION BY CERTIFICATE @param_cert

WITH PASSWORD = @param_certpass;

end

DECLARE @en_rec_id varbinary(max);

SELECT @en_rec_id = EncryptByKey(Key_GUID(@param_symkeyguid),@param_rec_id);

DECLARE @en_bsb varbinary(max);

SELECT @en_bsb = EncryptByKey(Key_GUID(@param_symkeyguid),@param_bsb);

DECLARE @en_account varbinary(max);

SELECT @en_account = EncryptByKey(Key_GUID(@param_symkeyguid),@param_account);

DECLARE @en_account_name varbinary(max);

SELECT @en_account_name = EncryptByKey(Key_GUID(@param_symkeyguid),@param_account_name);

INSERT INTO payer_ba_sym

(rec_id, bsb, account, account_name)

VALUES (

@en_rec_id,

@en_bsb,

@en_account,

@en_account_name

);

CLOSE SYMMETRIC KEY @param_symkeyguid;

END

GO

Any assistance in correcting this syntax (if indeed these functions accept parameters would be greatly appreciated).

- Andrew

Answer this question

Key Maintenance and Stored Procedures

  • Ruben T

    Thanks Sung,

    You are right about passing paswords as variables. In fact I will only pass the certifcate and key names as vars.

    The syntax seems to work, I shall do some more testing, thanks mate.

    - Andrew

  • Michael Vanhoutte

    Also note that the other reason you need to check parameters and monitor permissions very closely is that this sort of parsing is VERY vulnerable to SQL injection attacks.

    Sung



  • Ben Santiago

    I am including a couple of links for SQL injection articles that may be helpful. I highly recommend reading the second link even if you are already familiar with SQL injection.

    · 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

    Thanks,

    -Raul Garcia

    SDE/T

    SQL Server Engine



  • lordJapheth

    You can do this if you use dynamic SQL. That is build the SQL query as a string, then pass to the "EXEC" statement. For example:

    DECLARE @sqlstring NVARCHAR(60);

    SET @sqlstring = 'OPEN SYMMETRIC KEY ' + @param_symkeyguid + ' DECRYPTION BY CERTIFICATE ' + @param_cert;

    EXEC (@sqlstring);

    Note that this procedure is a bit vulnerable especially because you are passing in a password as a parameter. You could make this somewhat more secure by having the certificate be encrypted by the database master key encrypted by the service master key. This will avoid the password issue.

    In general, I would hesitate to pass sensitive information in as parameters (this includes the symmetric key and certificate ids). You should be sure that the proc checks the parameters very careful and that the permission on the procedure and the underlying objects are tightly controlled.

    Please let me know if you would like further info.

    Thanks,

    Sung



  • MoniDD

    Hey Andrew,

    It's actually a little easier than that. Only DDL and perhaps a few other statement types don't support dynamic SQL. Built-ins should already support dynamic SQL so you could simply directly call:

    SELECT @en_rec_id = EncryptByKey(Key_GUID(@param_bartlett_sym),@param_rec_id)

    Also, Laurentiu suggested another website to check:

    http://www.sommarskog.se/dynamic_sql.html

    You can also look into the "sp_executesql" and "quotename" functions as they might help you.

    Sung



  • Mr_White

    Hey Andrew,

    Sorry for the late response.

    I did a quick test and it seems to work for me. Quick question, where do you open the key You can verify the key is actually open by checking the sys.open_keys catalog view. You will need to have the key open prior to encrypting with it.

    Thanks,

    Sung



  • Jeff Levinson

    Hello Sung,

    My old syntax prior to passing parameters for the certificate and key was this:

    DECLARE @en_rec_id varbinary(max);

    SELECT @en_rec_id = EncryptByKey(Key_GUID(bartlett_sym),@param_rec_id); it worked but values for key etc were hardcoded

    If I apply your method to the encryption statements:

    DECLARE $sqlstring NVARCHAR(MAX);

    SET @sqlstring = 'EncryptByKey(Key_GUID(' + "'" @param_bartlett_sym + "'" + '),' + @param_rec_id + ')';

    DECLARE @en_rec_id varbinary(max);

    SELECT @en_rec_id = EXEC(@sqlstring); -- when I run the code it bombs here near EXEC, so I need some help with this line

    Any assistance appreciated.

    - Andrew

  • Vaughan

    Hey Andrew,

    I would still be careful about possible SQL injection attacks. One way you can minimize this is by doing a simple parameter check such as

    if (cert_id(@cert_name) is not null) ... <your code here>

    else ... <your error code here>

    This should be done for each type on all names passed in. This, at a minimum, checks to make sure that people are actually passing in a valid object names.

    Hope this helps,

    Sung



  • LittleSettler

    Thanks Sung,

    Sorry about the delayed reply. Actually passing the sym key in the manner described eg

    SELECT @en_rec_id = EncryptByKey(Key_GUID(@param_bartlett_sym),@param_rec_id)

    results in null.

    So I'm not sure how to get a result...

    This doesn't work (might be on the right track thought).

    DECLARE @en_rec_id varbinary(max);

    DECLARE @str_cert NVARCHAR(MAX);

    SET @str_cert = 'SELECT @en_rec_id = EncryptByKey(Key_GUID(' + "'" + @param_cert + "'" + '),' + @param_rec_id + ')';

    EXECUTE sp_executesql @str_cert, N'@en_rec_id VARBINARY OUTPUT', @en_rec_id OUTPUT;

    Basically I wish to run the dynamic query and have it pass the value of @en_rec_id as output for later use in an insert statement in the same stored procedure.

    See original post above.

    Any help greatly appreciated.

    - Andrew
  • Key Maintenance and Stored Procedures
Answer this question