Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Allowing a stored procedure to select from a system table using a certificate in SQL Server 2012

I'm currently having issues relating to permissions when attempting to run a stored procedure that tries to select from a system table. Basically I'm trying to work around the deprecation of the sp_bindtoken system stored procedure in SQL Server 2012 by grabbing the data I need from the sys.dm_tran_current_transaction table directly.

One solution I've found is to:

  • Create a certificate
  • Create a user/login from the certificate
  • Grant the user the VIEW SERVER STATE permission
  • Sign the stored procedure using the certificate
  • Execute the stored procedure, which runs in the context of the user that was just created

This approach seems to work fine in SQL Server 2008 R2. However in SQL Server 2012, even though the script appears to run correctly, at runtime the stored procedure fails with a permissions error when attempting to select from the above system table.

The script I have at present looks a little like this:

USE OurDatabase
GO

CREATE CERTIFICATE OurDatabaseProcCert
FROM FILE = 'C:\Path\To\OurDatabaseProcCert.cer'
WITH PRIVATE KEY (
FILE = 'C:\Path\To\OurDatabaseProcCert.pvk',
ENCRYPTION BY PASSWORD = '$0m3$tr0ngp@$$w0rd',
DECRYPTION BY PASSWORD = '$0m3$tr0ngp@$$w0rd');
GO

USE master
GO

CREATE CERTIFICATE OurDatabaseProcCert
    FROM FILE = 'C:\Path\To\OurDatabaseProcCert.cer'
WITH PRIVATE KEY (
    FILE = 'C:\Path\To\OurDatabaseProcCert.pvk',
    ENCRYPTION BY PASSWORD = '$0m3$tr0ngp@$$w0rd',
    DECRYPTION BY PASSWORD = '$0m3$tr0ngp@$$w0rd');
GO

CREATE LOGIN OurDatabaseServerLogin
    FROM CERTIFICATE OurDatabaseProcCert
GO

CREATE USER OurDatabaseServerLogin

REVOKE CONNECT SQL FROM OurDatabaseServerLogin
GO 

GRANT AUTHENTICATE SERVER TO OurDatabaseServerLogin
GO

GRANT VIEW SERVER STATE TO OurDatabaseServerLogin
GO

USE OurDatabase
GO

ADD SIGNATURE TO dbo.bsp_getTransactionID BY CERTIFICATE OurDatabaseProcCert WITH PASSWORD = '$0m3$tr0ngp@$$w0rd'

And this is the code which creates the stored procedure that I'm trying to sign/execute:

CREATE Procedure bsp_getTransactionID
(
    @TransactionID  VARCHAR(255) OUTPUT
)
AS      
BEGIN
    IF @@TRANCOUNT > 0  
    BEGIN
        SELECT SYSTEM_USER
        SELECT @TransactionID = sys.dm_tran_current_transaction.transaction_id FROM sys.dm_tran_current_transaction
    END 
 RETURN 0
END
GO

Has anyone run into this type of problem before?

like image 770
PairOne Avatar asked Dec 02 '11 15:12

PairOne


People also ask

How do I grant permission to run a stored procedure?

To grant permissions on a stored procedureFrom Stored Procedure Properties, select the Permissions page. To grant permissions to a user, database role, or application role, select Search. In Select Users or Roles, select Object Types to add or clear the users and roles you want.

How do you set Full Control permissions on the certificate for the SQL Server service account?

Right-click the certificate, select All Tasks > Manage Private Keys, and then grant full permissions to the SQL Server service account.

Can you execute a stored procedure in a CTE?

According to the CTE documentation, Common Table Expression is a temporary result set or a table in which we can do CREATE, UPDATE, DELETE but only within that scope. That is, if we create the CTE in a Stored Procedure, we can't use it in another Stored Procedure.


1 Answers

Although I do not understand why it would react different in 2012, I do wonder if you could work around it by using the EXECUTE AS <certificate_logon> clause in the CREATE PROCEDURE.

In theory this should be equivalent as to your solution as the default isEXECUTE AS owner, but maybe it reacts slightly different ??? Worth a try I think.

like image 196
deroby Avatar answered Oct 22 '22 16:10

deroby