1

Some background:

Question is follow up of this question:

Is there a way in SQL Server to make a table only able to insert by trigger?

I'm trying to achieve what the answer to the original question above stated (i.e Trigger on the trigger table to have access to DMVs):

** I do have mostly-complete example code (about 75% done) for a Trigger on the Audit table that would disallow updates from anything but code signed by the Certificate, but ran out of time to complete it. The concept is that a lock is taken on the Certificate during the process, and the lock entry includes the Certificate ID. You can verify that the Certificate ID is the desired Certificate and ROLLBACK if it isn't or no Certificate is used in the Transaction. The problem was that VIEW SERVER STATE is needed to use sys.dm_tran_locks. However, that is a fairly easy problem to solve as it can be granted via a Certificate-based Login, which can even be the same Certificate. In that case, the Certificate can be backed-up and restored into master for the purpose of creating the Login from it. Then just grant that Login the VIEW SERVER STATE permission, and finally sign the Trigger on the Audit table with that same Certificate (already in that DB as it was used to sign the Trigger on the base table).

The closest answer to above problem can be found here by the same author:

https://sqlquantumleap.com/2018/02/15/safely-and-easily-use-high-level-permissions-without-granting-them-to-anyone-server-level/

The difference is that my base table of the trigger is on another schema, not on .dbo as suggested in the original answer, and I suspect this might be the culprit.

In the link to the website, eventually login is not mapped to a db certifice user after the full setup but the steps for multiple schemas may (or may not) require the login to be mapped to a certificate db user and cannot be unchecked. Creating another certificate did not help either.

Still the same error message:

The module being executed is not trusted. Either the owner of the database of the module needs to be granted authenticate permission, or the module needs to be digitally signed.

Have been struggling with it for the past few days so please do not vote for close and let me explain further if something is not clear.

Code block to setup the second trigger on the trigger table (most of which provided from the links above thanks to Solomon Rutzky):

SET NOCOUNT, XACT_ABORT ON

CREATE CERTIFICATE Cert
ENCRYPTION BY PASSWORD = 'StrongPa$$word'
WITH SUBJECT = '"SUBJECT"'
GO

CREATE USER Cert_User FROM CERTIFICATE Cert
GO

CREATE SCHEMA [LogTrigger]
    AUTHORIZATION [Cert_User];
GO

EXEC(N'CREATE TRIGGER [LogTrigger].[AuditINSERT]
  ON [LogTrigger].[BaseTriggerTable]
  AFTER INSERT
AS
SET NOCOUNT ON;
 
DECLARE @NetAddress NVARCHAR(50);
 
SELECT  @NetAddress = conn.[client_net_address]
FROM    sys.dm_exec_connections conn
WHERE   conn.[session_id] = @@SPID;
 
PRINT ''Audited Net Address: '' + @NetAddress;
');

ADD SIGNATURE
    TO  [LogTrigger].[AuditINSERT]
    BY CERTIFICATE [Cert]
    WITH PASSWORD = 'StrongPa$$word';


DECLARE @Cert NVARCHAR(4000) =
         CONVERT(NVARCHAR(4000),
                 CERTENCODED(CERT_ID(N'Cert')),
                 1);
 
EXEC (N'USE [master];
CREATE CERTIFICATE [Cert]
FROM BINARY = ' + @Cert);
 
EXEC (N'USE [master];
CREATE LOGIN [Cert_ViewServerState]
    FROM CERTIFICATE [Cert];
 
GRANT VIEW SERVER STATE TO [Cert_ViewServerState];
');
3

1 Answer 1

1

First of all, when I reproduce using your code - I have a different message:

Msg 300, Level 14, State 1, Procedure AuditINSERT, Line 9 [Batch Start Line 0] VIEW SERVER PERFORMANCE STATE permission was denied on object 'server', database 'master'. Msg 297, Level 16, State 1, Procedure AuditINSERT, Line 9 [Batch Start Line 0] The user does not have permission to perform this action.

That's because sys.dm_exec_connections requires VIEW SERVER STATE permissions.

This is a server-level permission, so you cannot grant it to a User, which is a database-level permission.

You need to:

  1. Create a cert in the database with the table/trigger
  2. Export the same certificate into the master database
  3. Create login from the cert in the master database
  4. Grant VIEW SERVER STATE permissions to the login
  5. Back in the database with the trigger, sign the trigger with the certificate

I describe the certificate export step on my blog - https://straightforwardsql.com/posts/cross-db-access-with-module-signing/

use [YourDB]
CREATE CERTIFICATE TriggerCert
    ENCRYPTION BY PASSWORD = 'Cert_Private_Key'
    WITH
       SUBJECT = 'Module sign Table trigger'
       , EXPIRY_DATE = '99991231'

SELECT CERTENCODED(CERT_ID('TriggerCert')) AS PublicPortionBinary -- <- copy to clipboard

USE master

CREATE CERTIFICATE TriggerCert
FROM BINARY = /* paste from clipboard */

CREATE login TriggerCert_Login FROM CERTIFICATE TriggerCert
GRANT VIEW SERVER STATE TO TriggerCert_Login

use [YourDB]

/* assuming the trigger already exists */

ADD SIGNATURE
    TO  [LogTrigger].[AuditINSERT]
    BY CERTIFICATE TriggerCert
    WITH PASSWORD = 'Cert_Private_Key';

If the example in your post is not just an example and client_net_address is all you need, then you can use

DECLARE @NetAddress NVARCHAR(50) = (SELECT cast (CONNECTIONPROPERTY ('client_net_address') AS nvarchar(50)))

which doesn't require any elevated permissions.

7
  • I followed the steps what Solomon advised to do as in the explanation so I think it can be done yet although I give VIEW SERVER STATE to the login it doesn't work. My post is just an example and eventually the trigger will need to have VIEW SERVER STATE permission. I suspect schema authorization by certificate user is causing this error. Module has to be in the user database for the project. Thank you. Commented Feb 16 at 6:31
  • @Stackoverflowuser I've updated my answer
    – Zikato
    Commented Feb 16 at 13:21
  • Thank you but I already tried your steps as described in the beginning and even more such as creating the certificate user in the user db and creating the schema and giving authorization on that schema to the certificate user which I think causing the error message. Nonetheless, I tried once again with your code and didn't work out and got the same "The module being executed is not trusted. Either the owner of the database of the module needs to be granted authenticate permission, or the module needs to be digitally signed." message. Commented Feb 16 at 16:40
  • 1
    And can you repro when creating the tables triggers, schemas, etc, yourself? Because it's hard to debug something if you cannot repro and I don't have access
    – Zikato
    Commented Feb 16 at 16:50
  • I couldn't reproduce your error message :( Obviously our setup is somewhat different. My project has two tables with their own respective triggers. First trigger writes upon insert to the table on the other schema. The trigger on that second table checks whether the update is coming from the certificate i.e trigger from the first table as what I tried to explain in the issue description, Solomon's quote above from the first question. Honestly, this is about being finicky about security but I now wonder where it stuck. Thank you anyway. Commented Feb 17 at 9:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.