Module signing on a trigger which accesses system views. Base table is on a custom schema

Question

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];
');

Answer 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.