CODEWITHSUNDEEP
sqlsql-serverstored-proceduressql-server-2012database-permissions
PairOne
PairOne

Reputation: 111

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:

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?

Upvotes: 11

Views: 1158

Answers (1)

deroby
deroby

Reputation: 6002

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.

Upvotes: 1

Related Questions