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
