Help Using Asymetric/Symetric key in a Scalar UDF

The error message I get is as follows:
Invalid use of side-effecting or time-dependent operator in 'OPEN SYMMETRIC KEY' within a function.
&
Invalid use of side-effecting or time-dependent operator in 'CLOSE SYMMETRIC KEY' within a function.

Here is the code I am trying to implement:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER FUNCTION [dbo].[func_GetSIMSPassPhrase]
(
)
RETURNS varchar(30)
AS
BEGIN
OPEN SYMMETRIC KEY sims_sym_Key DECRYPTION BY ASYMMETRIC KEY sims_asym_key
DECLARE @.GUID UNIQUEIDENTIFIER
SET @.GUID = (SELECT key_guid FROM sys.symmetric_keys WHERE name = 'sims_sym_Key')
DECLARE @.passphrase varchar(30)
SELECT @.passphrase = (SELECT CAST(DecryptByKey(EncField) AS VARCHAR(30)) FROM tblEncryptTest)
CLOSE SYMMETRIC KEY sims_sym_Key
RETURN @.passphrase
END

Anyone have any suggestions? TIA

You cannot use OPEN SYMMETRIC KEY in a function. Write a procedure instead - you can use an OUTPUT argument to return the passphrase. For an example, see http://blogs.msdn.com/lcris/archive/2006/01/13/512829.aspx.

Thanks
Laurentiu

|||

Laurentiu,

I found your article after I posted that message. I am still getting this error though:

SELECT permission denied on object 'symmetric_keys', database 'mssqlsystemresource', schema 'sys'.

Here is what I have done so far. I moved OPEN SYMMETRIC KEY to a stored procedure (listed below):

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[uspPassPhraseGet]
@.pss varchar(30) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
OPEN SYMMETRIC KEY sims_sym_Key DECRYPTION BY ASYMMETRIC KEY sims_asym_key
DECLARE @.GUID UNIQUEIDENTIFIER
SET @.GUID = (SELECT key_guid FROM sys.symmetric_keys WHERE name = 'sims_sym_Key')
SET @.pss = (SELECT CAST(DecryptByKey(EncField) AS VARCHAR(30)) FROM tblEncryptTest)
CLOSE SYMMETRIC KEY sims_sym_Key
END

I granted execute permissions to the role that contains the user I am using to access this stored procedure. I am calling this stored procedure from within another stored procedure to access the encrypted passphrase contained in a table encrypted by the Symmetric/Asymetric keys. See example below:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[uspFreeFormList]
@.userid int
AS

DECLARE @.pss varchar(30)
EXEC [dbo].[uspPassPhraseGet] @.pss OUTPUT

SELECT
...
,CONVERT(varchar(max),DecryptByPassPhrase(@.pss, CONVERT(varchar(max),dbo.tbl_msg_app_freeform.title), 1, CONVERT(varbinary, 23))) as title
,CONVERT(varchar(max),DecryptByPassPhrase(@.pss, CONVERT(varchar(max),dbo.tbl_msg_app_freeform.description), 1, CONVERT(varbinary, 23))) as description
,CONVERT(varchar(max),DecryptByPassPhrase(@.pss, CONVERT(varchar(max),dbo.tbl_msg_app_freeform.shortdesc), 1, CONVERT(varbinary, 23))) as shortdesc,
...
FROM
...
WHERE
...

I know the permissions to the stored procedures are correct because if I set the @.pss output parameter in uspPassPhraseGet to a static string everything works fine. It is when I am accessing the symmetric key that I don't have select granted on sys.symmetric_keys. I have gone in and explicitly granted SELECT for the role I am using to sys.symmetric_keys. However this isn't working. What am I missing? Is there some archane setting I am missing? Also, on a more academic note, is this the right approach to protect a passphrase used in the DecryptByPassPhrase/EncryptByPassPhrase function or is there a better suggestion/scenario to use?

Thanks for your time and attention,

Mike

|||

It looks like you have explicitly denied SELECT permissions on the sys.symmetric_keys catalog to either the use or one of the roles he belongs to.

To see to whom you have denied the permissions, execute the following query in your database:

select user_name(grantee_principal_id) from sys.database_permissions where state = 'D' and major_id = object_id('sys.symmetric_keys')

Then, take the result and execute

revoke select on sys.symmetric_keys to grantee

replacing grantee with the result of the previous query.

Regarding the code, why do you retrieve that GUID in uspPassPhraseGet? And why do you use a passphrase for encryption instead of using the symmetric key itself?

Thanks
Laurentiu