Encapsulate encryption with an UDF and a view

I have a table that has an encrypted account number column. In order to give users access to the data BUT not access to the symmetric key used to encrypt the data, I'm trying to implement the following:

Create an UDF that opens the symmetric key, call the EncryptByKey function, and close the symmetric key
Create a view on the table containing decrypted account numbers
Grant users select permission on the view

However, I got an error when trying to add 'open symmetric key' statement to the UDF:

Msg 443, Level 16, State 14, Procedure fn_DecyptAcctNum, Line 7
Invalid use of side-effecting or time-dependent operator in 'OPEN SYMMETRIC KEY' within a function.

Has anyone else had the same problem OR is there another way that I can limit users' access to the keys?

Thanks!

You cannot use OPEN SYMMETRIC KEY and CLOSE SYMMETRIC KEY in a UDF because they change the state of the openkeys catalog (this is the side-effect from the message).

But if you only want to give users access to data, a view that performs the decryption should be sufficient; there is no need to encapsulate the encryption in a UDF, unless you want to do more than limiting access to the data.

Thanks
Laurentiu|||Laurentiu, Thanks for your help on this!

However, I'm still not sure where to put the 'open symmetric key' statement since it is required to make both EncryptByKey and DecryptByKey work. Let me give you a scenario:

A user wants to lookup the account ID for an account number

The application calls a stored procedure sp_AccountNumberLookup:
sp validates the passed in account number
sp selects from the view (with decrypted account number using UDF)
sp returns the account ID if found

Without having the 'open key' statement before 'select from view', the decrypted account number returns NULL. If I put the 'open key' statement in the sp and give the user exec perm to the sp, then the user would have access to keys as well.

Thanks again for your time!
|||You should create your view using one of the "auto" decrypt builtins, as shown in the example from: http://blogs.msdn.com/lcris/archive/2005/06/10/428178.aspx - check view v_employees_auto. You'll need to protect your symmetric key using a certificate for this to work, but it will allow you to avoid opening the key explicitly.

Thanks
Laurentiu|||Your code sample is extremely helpful and I've implemented the DecryptByKeyAutoCert in the view to restrict user access to the symmetric key.

Thanks a lot, Laurentiu!

Encapsulate encryption with an UDF and a view

I have a table that has an encrypted account number column. In order to give users access to the data BUT not access to the symmetric key used to encrypt the data, I'm trying to implement the following:

Create an UDF that opens the symmetric key, call the EncryptByKey function, and close the symmetric key
Create a view on the table containing decrypted account numbers
Grant users select permission on the view

However, I got an error when trying to add 'open symmetric key' statement to the UDF:

Msg 443, Level 16, State 14, Procedure fn_DecyptAcctNum, Line 7
Invalid use of side-effecting or time-dependent operator in 'OPEN SYMMETRIC KEY' within a function.

Has anyone else had the same problem OR is there another way that I can limit users' access to the keys?

Thanks!

You cannot use OPEN SYMMETRIC KEY and CLOSE SYMMETRIC KEY in a UDF because they change the state of the openkeys catalog (this is the side-effect from the message).

But if you only want to give users access to data, a view that performs the decryption should be sufficient; there is no need to encapsulate the encryption in a UDF, unless you want to do more than limiting access to the data.

Thanks
Laurentiu|||Laurentiu, Thanks for your help on this!

However, I'm still not sure where to put the 'open symmetric key' statement since it is required to make both EncryptByKey and DecryptByKey work. Let me give you a scenario:

A user wants to lookup the account ID for an account number

The application calls a stored procedure sp_AccountNumberLookup:
sp validates the passed in account number
sp selects from the view (with decrypted account number using UDF)
sp returns the account ID if found

Without having the 'open key' statement before 'select from view', the decrypted account number returns NULL. If I put the 'open key' statement in the sp and give the user exec perm to the sp, then the user would have access to keys as well.

Thanks again for your time!|||You should create your view using one of the "auto" decrypt builtins, as shown in the example from: http://blogs.msdn.com/lcris/archive/2005/06/10/428178.aspx - check view v_employees_auto. You'll need to protect your symmetric key using a certificate for this to work, but it will allow you to avoid opening the key explicitly.

Thanks
Laurentiu|||Your code sample is extremely helpful and I've implemented the DecryptByKeyAutoCert in the view to restrict user access to the symmetric key.

Thanks a lot, Laurentiu!

Enabling E-Mail Delivery for RS

What is required in order to schedule a report for email delivery?
I have modified the RSReportServer.config file on SQL Server and created a
subscription for a report.
I can setup the subscription but it never runs.
Is there another step that I have left out?Carl,
Do you get any error messages in the Status column on the Subscriptions page
of report designer?
Andre
"Carl Meister" wrote:
> What is required in order to schedule a report for email delivery?
> I have modified the RSReportServer.config file on SQL Server and created a
> subscription for a report.
> I can setup the subscription but it never runs.
> Is there another step that I have left out?
>|||Andre,
On the subscriptions page for the report in the status column is "New
Subscription".
The Last Run column is empty.
I have this report scheduled to run every hour but it seems like it never
kicks off.
"Andre" wrote:
> Carl,
> Do you get any error messages in the Status column on the Subscriptions page
> of report designer?
> Andre
> "Carl Meister" wrote:
> > What is required in order to schedule a report for email delivery?
> >
> > I have modified the RSReportServer.config file on SQL Server and created a
> > subscription for a report.
> >
> > I can setup the subscription but it never runs.
> >
> > Is there another step that I have left out?
> >|||Hi Carl
I think the "SQL Agent" need to run, check this.
BR Per