How to pass symmetric key to query

Question

I want to know how to pass symmetric key to SQL query as parameter.

I'm usually hardcoding it within the query as follows:

OPEN SYMMETRIC KEY SymKey
DECRYPTION BY CERTIFICATE CertiFi;

SELECT * FROM MasterBlaster

CLOSE SYMMETRIC KEY SymKey

But I want to pass it as parameter.

Answer 1

According to https://learn.microsoft.com/en-us/sql/t-sql/statements/open-symmetric-key-transact-sql?view=sql-server-ver16 you can't use parameters or variables in OPEN KEY statement. One way to do it is:

declare @key nvarchar(100) = 'test'
, @sql nvarchar(max)

set @sql = N'
OPEN SYMMETRIC KEY ' + QUOTENAME(@key) + N'
DECRYPTION BY CERTIFICATE CertiFi;

SELECT * FROM MasterBlaster;

CLOSE SYMMETRIC KEY ' + QUOTENAME(@key) + N';'
EXEC SP_EXECUTESQL @SQL

Note that you gotta be careful with sql injection, therefor QUOTENAME is used