/
SQL CLR Trigger

SQL CLR Trigger

Owned by Rakesh
Mar 14, 2025
6 min read

These guidelines are provided as-is and for your convenience only. Discuss with your internal security team(s) any security implications before enabling CLR stored procedures.

NLINK 8.0.230 added the option to use SQL Server CLR stored procedures. It is possible to use SQL CLR stored procedures as triggers to notify NLINK of real-time changes. The SQL CLR stored procedure can be used in place of DCOM-based SQL Extended Stored Procedures.

Requirements to use SQL CLR stored procedures with NLINK:

  1. Obtain the proper NLINK license component SQL Trigger.

  2. Install the NLINK SQL CLR DLL file on the SQL Server machine.

  3. Set up the NLINK Server host firewall to allow incoming calls on a specific port.

  4. Configure the NLINK meta-database to process incoming calls.

Setting up SQL Server

For CLR stored procedures to run, SQL Server should have .NET 4.8 or later installed.

  1. Enable CLR support in SQL Server.

    exec sp_configure 'clr enabled', 1; RECONFIGURE;

    If the CLR is not enabled on SQL Server you will get the following error either trying to install a SQL CLR trigger or at run time:
    Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option

  2. Copy the NLINK SQL CLR DLL file NLINKSqlTrigger.dll from the NLINK installation to some folder on SQL Server, e.g., C:\Program Files\Junot Systems\. (The default location for the DLL on the NLINK host is [NLINK INSTALL FOLDER]\NLINK Server\Connectors.)

  3. Get the hash of signed DLL using Windows Powershell with the following command:

    ”get-filehash -Algorithm SHA512 NLINKSqlTrigger.dll | Select-Object Hash”

    1. The result of the above command will be a hex string. Copy the entire hash value as it is needed in subsequent steps. If you see “…” at the end of the value, make the Powershell window bigger and run the command again. Make sure to get the full hash value.

      image-20240926-144930.png


      Prefix this value with “0x” (hex prefix). For example:

      0x339502F87CCDE2DD30BBB847B69F5A7DD94BD9E8364B250D931B43DA99215DF027BE6B9FA567D6FBE1D0A2699242FE7947075503824F7EF025FAE517EC501D13

  4. The following steps are in SQL Server Management Studio, assuming the full path of the NLINK CLR DLL is C:\Program Files\Junot Systems\NLINKSqlTrigger.dll:

    1. Mark the DLL as safe using the hash. See sys.sp_add_trusted_assembly (Transact-SQL) - SQL Server | Microsoft Learn for details.

      EXEC sp_add_trusted_assembly @hash = 0x339502F87CCDE2DD30BBB847B69F5A7DD94BD9E8364B250D931B43DA99215DF027BE6B9FA567D6FBE1D0A2699242FE7947075503824F7EF025FAE517EC501D13, @Description = N'NLINKSqlTrigger, version=1.0.0, culture=neutral, publickeytoken=null, processorarchitecture=msil';

    2. Create ASSEMBLY.

      CREATE ASSEMBLY NLINKSqlTrigger from 'C:\Program Files\Junot Systems\NLINKSqlTrigger.dll' WITH PERMISSION_SET = UNSAFE;

      The created assembly will show up in SQL Server under Assemblies

      image-20240918-224336.png

    3. Common error: If the assembly is marked as unsafe by the operating system or if the assembly is not

      signed it causes following error:
      CREATE ASSEMBLY for assembly 'NLINKSqlTrigger' failed because assembly 'NLINKSqlTrigger' is not trusted. The assembly is trusted when either of the following is true: the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission, or the assembly is trusted using sp_add_trusted_assembly.

  5. Create a Stored Procedure with proper parameters for authorization. NLINK CLR processing provides two different options--choose one or the other, depending on which authentication method is preferred. You do NOT need to create both. We recommend using option (a) Authenticating using API Token with JSON body.

    Note: The EXTERNAL NAME syntax is VERY important. It is case-sensitive and has to be exactly in the format specified.

    1. Authenticate using API Token with JSON Body

      IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[NLINK_TokenAuth]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].NLINK_TokenAuth GO CREATE PROCEDURE NLINK_TokenAuth( @apiUrl NVARCHAR(1024) , @ApiToken NVARCHAR(1024) , @JsonBody NVARCHAR(1024) , @returnval NVARCHAR(1024) OUT) AS EXTERNAL NAME NLINKSqlTrigger.[NLINKSqlTrigger.Procedures].TokenAuth; GO

    2. BASIC authentication and JSON object

      IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[NLINK_BasicAuth]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].NLINK_BasicAuth GO CREATE PROCEDURE NLINK_BasicAuth( @apiUrl NVARCHAR(1024) , @UserName NVARCHAR(1024) , @Password NVARCHAR(1024) , @JsonBody NVARCHAR(1024) , @returnval NVARCHAR(1024) OUT) AS EXTERNAL NAME NLINKSqlTrigger.[NLINKSqlTrigger.Procedures].BasicAuth; GO

  6. The stored procedure created above can be used in any trigger to call NLINK by passing appropriate values in the parameters.

  7. If multiple triggers need to be created with similar payloads, then each one can be created as different NLINK Event. Use the NLINK Event Key in the apiURL parameter as shown below.
    Sample trigger script:
    Use option 1 or option 2 as needed by your setup.

    IF OBJECT_ID ('NLINKTriggerOnInsert','TR') IS NOT NULL DROP TRIGGER NLINKTriggerOnInsert; GO -- -- This trigger to call NLINK with required data -- CREATE TRIGGER NLINKTriggerOnInsert ON [SOME_TABLE] /* TODO - change SOME_TABLE to valid Table */ AFTER INSERT AS IF (ROWCOUNT_BIG() = 0) RETURN; BEGIN DECLARE @apiUrl AS nvarchar(100) DECLARE @DataValue1 AS nvarchar(100) DECLARE @DataValue2 AS nvarchar(100) DECLARE @apiToken AS nvarchar(100) DECLARE @UserName AS nvarchar(100) DECLARE @Password AS nvarchar(100) DECLARE @returnval as nvarchar(100) DECLARE @jsonData as nvarchar(1000) select @DataValue1 = [SOME_FIELD1] from inserted /* TODO - change SOME_FIELD1 to valid field */ select @DataValue2 = [SOME_FIELD2] from inserted /* TODO - change SOME_FIELD2 to valid field */ --- -- Build the message in format expected by NLINK configuration -- In this example a simple JSON object with 2 values is expected by -- NLINK. -- NOTE: -- Change the names of JSON elements to match NLINK Meta-database -- TriggerData - Should be Message Format Table Name -- Value1 and Value2 - Should match Message Format Field Names --- SELECT @jsonData = N'{"TriggerData": {"Value1": "' + @DataValue1 + N'","Value2": "' + @DataValue2 + N'"}}' --- -- Set the URL for NLINK --- SELECT @apiUrl = 'http://[SOME_NLINK_HOST]:[PORT]/NLINK_EVENT_KEY' --- -- Call NLINK using either one of the options either token or the active directory authentications --- -- Option 1 - Using API Token authentication mechanism /* select @apiToken = 'TOKEN_VALUE_CONFIGURED_IN_NLINK' /* TODO - Set Token value */ EXECUTE [dbo].[NLINK_TokenAuth] @apiUrl , @apiToken , @jsonData , @returnval OUTPUT */ --- -- OR--- --- -- Option 2 - Using Active Directory authentication mechanism with UserId and Password /* select @UserName = '' /* TODO - Set Token value */ select @Password = '' /* TODO - Set Token value */ EXECUTE [dbo].[NLINK_BasicAuth] @apiUrl , @UserName , @Password , @jsonData , @returnval OUTPUT */ RETURN END; GO

Configuring NLINK Meta-Database

A valid Junot license including the “SQL Trigger” component is necessary for the following steps to work.

  1. There is no SQL CLR wizard in NCM, so use attached sample meta-database to setup the required NLINK configuration:

  2. Unzip and open attached SQL Trigger CLR.nlk in NCM.

    1. NCM may prompt to upgrade. Please upgrade before proceeding

    2. Close the sample meta-database after upgrading.

  3. Open the meta-database which needs to be configured for SQL CLR Trigger.

  4. Select Import Transaction option using File >> Import from the menu.

    1. Select source meta-database SQL Trigger CLR.nlk

    2. Select Transaction SQL Trigger CLR to import

  5. After the Transaction is imported, select the new External System SQL CLR Trigger and set the Attributes as needed:

    image-20240926-183921.png

    1. TCP/IP Port - The port on which NLINK will receive calls from SQL CLR Trigger.
      Any firewall(s) between SQL Server and NLINK should allow incoming calls to the NLINK host on this port.

    2. Use SSL - Set to Yes if TCP/IP Port is configured for https, or to No for http-based communication

    3. Authentication Mode - Select API Token or Active Directory as appropriate

    4. API Key - If using API Token authentication mode then specify the token here

  6. Configure the sample Action Series and Event as needed

    1. Configure the Action Series to process the incoming data

      image-20240926-184530.png

    2. Set the Event Key Attribute URL Path to /SQLTrigger

      image-20240926-184356.png

  7. The sample configuration allows two values to be passed from the SQL Server trigger to NLINK. If you need more, then modify the Message Format and add more Message Format Fields.

    image-20240926-184747.png