在 SQL Server 中綁定 dll

April 5, 2019

我的工作中有一個小項目。我的任務是將一些 SQLCLR 程式碼部署到我們的開發數據庫（SQL Server 2017）中進行測試。如果測試成功，我們希望在 SQLCLR 中開發觸發器和過程並將其部署給我們的客戶。我們有一個多租戶數據庫設計。我對 SQL Server 和 SQLCLR 很陌生，事實上這是我的第一個項目。

我的問題

SQL Server 數據庫項目的部署如何工作？我將 dll 綁定到我的開發機器上的 SQL Server 中。讓我們假設它工作正常，我將它推送到原始碼管理中。從這一點開始，它是否將其部署到我們客戶的不同數據庫中？
要將它綁定到我們的開發機器中，我假設我需要 db_owner/sysadmin 權限來創建用於 dll 身份驗證的非對稱密鑰。客戶是否還需要其數據庫中的權限來綁定 dll？

我試過什麼

我做了兩個測試項目，一個我在 VS17 中創建了一個SQL Server 數據庫項目，另一個我創建了一個簡單的**C# 類庫

對於SQL Server 數據庫項目，我遵循教程Stairway to SQLCLR並獲得執行和數據提供程序錯誤：

SQL 72045 腳本錯誤->錯誤發生在 GO 語句上。它下面標有紅線

SQL7214：.net sqlclient 數據提供者：msg 5000，級別 16，狀態 1 ->這裡我得到一個空白的紅線

在 c#-library 的情況下，我可以綁定 .dll 並在 sql studio 中設置TRUSTWORTHY（具有適當的權限），但我不想打開安全設置。如果沒有適當的權限（我們不知道客戶在他們的數據庫中的權限），我無法為伺服器中的 dll 創建非對稱密鑰以進行登錄。

我還在 Microsoft 的文件中閱讀了有關 SQLCLR 的資訊，並嘗試了教程SQLQuantumLeap。

編輯：來自 SQL72045 腳本錯誤的錯誤，從GO語句變為IF語句（見下圖）。

ConnectioinTypeTest.sql：此腳本由 AssemblySecuritySetup1、AssemblySecuritySetup2 和 PreDeploy-SecuritySetup 組合而成。對於其他腳本，請參見下面的 ConnectionTypeTest.sql 腳本。錯誤僅顯示在ConnectionTypeTest.sql中，而不顯示在整個腳本的部分中。

/*
Deployment script for db

This code was generated by a tool.
Changes to this file may cause incorrect behavior and will be lost if
the code is regenerated.
*/

GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;


GO
:setvar DatabaseName "db"
:setvar DefaultFilePrefix "db"
:setvar DefaultDataPath "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\"
:setvar DefaultLogPath "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\"

GO
:on error exit
GO
/*
Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
To re-enable the script after enabling SQLCMD mode, execute the following:
SET NOEXEC OFF; 
*/
:setvar __IsSqlCmdEnabled "True"
GO
IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
   BEGIN
       PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
       SET NOEXEC ON;
   END


GO
USE [$(DatabaseName)];


GO
IF (EXISTS(
          SELECT   sc.*
          FROM     sys.configurations sc
          WHERE    sc.[name] = N'clr enabled'
          AND      sc.[value_in_use] = 0
         )
  )
BEGIN
   EXEC sp_configure 'clr enabled', 1;
   RECONFIGURE;
END;
GO

/**********************************************
* Script:  AssemblySecuritySetup.sql
* Date:    2016-01-20
* By:      Solomon Rutzky
* Of:      Sql Quantum Leap ( http://SqlQuantumLeap.com )
* 
* Stairway to SQLCLR - Level 6: Development Tools
*
* Stairway to SQLCLR series:
* http://www.sqlservercentral.com/stairway/105855/
* 
**********************************************/


USE [master];
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET NOCOUNT ON;
GO
--------------------------------------------------------------------------------

DECLARE @ErrorMessage NVARCHAR(4000);



DECLARE @AssemblyName sysname, 
       @AsymmetricKeyName sysname,
       @LoginName sysname, 
       @PublicKeyToken VARBINARY(32),
       @SQL NVARCHAR(MAX);

SET @AssemblyName = N'$StairwayToSQLCLR-TEMPORARY-KeyInfo$';

SET @AsymmetricKeyName = N'StairwayToSQLCLR-06_PermissionKey';
SET @LoginName = N'StairwayToSQLCLR-06_PermissionLogin';

BEGIN TRY
   BEGIN TRAN;

   IF (NOT EXISTS(
               SELECT  *
               FROM        [sys].[assemblies] sa
               WHERE   [sa].[name] = @AssemblyName
           )
       )
   BEGIN
       SET @SQL = N'
       CREATE ASSEMBLY [' + @AssemblyName + N']
       AUTHORIZATION [dbo]

   FROM 0x4D5A90000300000004000000FFFF0000B80000000000000040000000000000000000000000000000000000000000400000;
';
       EXEC (@SQL);
   END;

   SET @PublicKeyToken = CONVERT(VARBINARY(32), ASSEMBLYPROPERTY(@AssemblyName, 'PublicKey'));

   IF (NOT EXISTS(
               SELECT  *
               FROM        [sys].[asymmetric_keys] sak
               WHERE   sak.[thumbprint] = @PublicKeyToken
           )
       )
   BEGIN
       SET @SQL = N'CREATE ASYMMETRIC KEY [' + @AsymmetricKeyName + N'] AUTHORIZATION [dbo] FROM ASSEMBLY [' + @AssemblyName + N'];';
       EXEC (@SQL);
   END;

   SET @SQL = N'DROP ASSEMBLY [' + @AssemblyName + N'];';
   EXEC (@SQL);

   COMMIT TRAN;
END TRY
BEGIN CATCH
   IF (@@TRANCOUNT &gt; 0)
   BEGIN
       ROLLBACK TRAN;
   END;

   SET @ErrorMessage = ERROR_MESSAGE();
   RAISERROR(@ErrorMessage, 16, 1);
   RETURN; -- exit the script
END CATCH;


-- If the Asymmetric Key exists but the Login does not exist, we need to:
-- 1) Create the Login
-- 2) Grant the appropriate permission
IF (EXISTS(
           SELECT  *
           FROM        [sys].[asymmetric_keys] sak
           WHERE   sak.[thumbprint] = @PublicKeyToken
       )
   ) AND
   (NOT EXISTS(
           SELECT      *
           FROM            [sys].[server_principals] sp
           INNER JOIN  [sys].[asymmetric_keys] sak
                   ON  sak.[sid] = sp.[sid]
           WHERE   sak.[thumbprint] = @PublicKeyToken
       )
   )
BEGIN
   BEGIN TRY
       BEGIN TRAN;

       SET @SQL = N'CREATE LOGIN [' + @LoginName + N'] FROM ASYMMETRIC KEY [' + @AsymmetricKeyName + N'];';
       EXEC (@SQL);

       SET @SQL = N'GRANT EXTERNAL ACCESS ASSEMBLY TO [' + @LoginName + N'];';
       EXEC (@SQL);

       COMMIT TRAN;
   END TRY
   BEGIN CATCH
       IF (@@TRANCOUNT &gt; 0)
       BEGIN
           ROLLBACK TRAN;
       END;

       SET @ErrorMessage = ERROR_MESSAGE();
       RAISERROR(@ErrorMessage, 16, 1);
       RETURN; -- exit the script
   END CATCH;
END;
--------------------------------------------------------------------------------




GO
USE [$(DatabaseName)];
GO

GO
PRINT N'Creating [StairwayToSQLCLR_06_ConnectionTypeTest1]...';


GO
CREATE ASSEMBLY [StairwayToSQLCLR_06_ConnectionTypeTest1]
   AUTHORIZATION [dbo]
   FROM 0x4D5A90000... AS N'StairwayToSQLCLR_06_ConnectionTypeTest1.pdb';


GO
PRINT N'Creating [dbo].[StairwayToSQLCLR_ConnectionTest]...';


GO
CREATE FUNCTION [dbo].[StairwayToSQLCLR_ConnectionTest]
(@SqlToExecute NVARCHAR (MAX) NULL, @UseImpersonation BIT NULL)
RETURNS SQL_VARIANT
AS
EXTERNAL NAME [StairwayToSQLCLR_06_ConnectionTypeTest1].[Testing].[ConnectionTypeTest]


GO
PRINT N'Update complete.';


GO

裝配安全設置 1


USE [master];
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET NOCOUNT ON;
GO
--------------------------------------------------------------------------------

DECLARE @ErrorMessage NVARCHAR(4000);




DECLARE @AssemblyName sysname,
       @AsymmetricKeyName sysname, 
       @LoginName sysname, 
       @PublicKeyToken VARBINARY(32),
       @SQL NVARCHAR(MAX);

SET @AssemblyName = N'$StairwayToSQLCLR-TEMPORARY-KeyInfo$';

SET @AsymmetricKeyName = N'StairwayToSQLCLR-06_PermissionKey';
SET @LoginName = N'StairwayToSQLCLR-06_PermissionLogin';

BEGIN TRY
   BEGIN TRAN;

   IF (NOT EXISTS(
               SELECT  *
               FROM        [sys].[assemblies] sa
               WHERE   [sa].[name] = @AssemblyName
           )
       )
   BEGIN
       SET @SQL = N'
       CREATE ASSEMBLY [' + @AssemblyName + N']
       AUTHORIZATION [dbo]

裝配安全設置2

';
       EXEC (@SQL);
   END;

   SET @PublicKeyToken = CONVERT(VARBINARY(32), ASSEMBLYPROPERTY(@AssemblyName, 'PublicKey'));

   IF (NOT EXISTS(
               SELECT  *
               FROM        [sys].[asymmetric_keys] sak
               WHERE   sak.[thumbprint] = @PublicKeyToken
           )
       )
   BEGIN
       SET @SQL = N'CREATE ASYMMETRIC KEY [' + @AsymmetricKeyName + N'] AUTHORIZATION [dbo] FROM ASSEMBLY [' + @AssemblyName + N'];';
       EXEC (@SQL);
   END;

   SET @SQL = N'DROP ASSEMBLY [' + @AssemblyName + N'];';
   EXEC (@SQL);

   COMMIT TRAN;
END TRY
BEGIN CATCH
   IF (@@TRANCOUNT &gt; 0)
   BEGIN
       ROLLBACK TRAN;
   END;

   SET @ErrorMessage = ERROR_MESSAGE();
   RAISERROR(@ErrorMessage, 16, 1);
   RETURN; -- exit the script
END CATCH;


-- If the Asymmetric Key exists but the Login does not exist, we need to:
-- 1) Create the Login
-- 2) Grant the appropriate permission
IF (EXISTS(
           SELECT  *
           FROM        [sys].[asymmetric_keys] sak
           WHERE   sak.[thumbprint] = @PublicKeyToken
       )
   ) AND
   (NOT EXISTS(
           SELECT      *
           FROM            [sys].[server_principals] sp
           INNER JOIN  [sys].[asymmetric_keys] sak
                   ON  sak.[sid] = sp.[sid]
           WHERE   sak.[thumbprint] = @PublicKeyToken
       )
   )
BEGIN
   BEGIN TRY
       BEGIN TRAN;

       SET @SQL = N'CREATE LOGIN [' + @LoginName + N'] FROM ASYMMETRIC KEY [' + @AsymmetricKeyName + N'];';
       EXEC (@SQL);

       SET @SQL = N'GRANT EXTERNAL ACCESS ASSEMBLY TO [' + @LoginName + N'];';
       EXEC (@SQL);

       COMMIT TRAN;
   END TRY
   BEGIN CATCH
       IF (@@TRANCOUNT &gt; 0)
       BEGIN
           ROLLBACK TRAN;
       END;

       SET @ErrorMessage = ERROR_MESSAGE();
       RAISERROR(@ErrorMessage, 16, 1);
       RETURN; -- exit the script
   END CATCH;
END;
--------------------------------------------------------------------------------

PreDeploy-SecuritySetup

IF (EXISTS(
          SELECT   sc.*
          FROM     sys.configurations sc
          WHERE    sc.[name] = N'clr enabled'
          AND      sc.[value_in_use] = 0
         )
  )
BEGIN
   EXEC sp_configure 'clr enabled', 1;
   RECONFIGURE;
END;
GO

:r "C:\Users\kzais\source\repos\StairwayToSQLCLR-06_ConnectionTypeTest\AssemblySecuritySetup.sql"

GO
USE [$(DatabaseName)];
GO

我是這兩篇文章的作者。第二篇文章（在https://SqlQuauntumLeap.com/上發現的較新文章）完全取代了在 SQL Server Central 上發現的較早文章（“通往 SQLCLR 的階梯”文章）。而且，由於您使用的是 SQL Server 2017，因此您需要使用較新的版本。
目前我不確定與該GO語句相關的第一個錯誤。我可能需要查看該部分之前的更多腳本，或者可能需要更多錯誤消息。該錯誤來自已完成的安裝腳本，還是來自部分預發布腳本？唯一重要的是完成建構後的完整腳本。有些部分腳本在建構之前會出現語法錯誤，因為它是分段完成的，一旦建構過程將這些片段組合在一起，就不會有任何語法錯誤。
就權限而言，sysadmin只需要啟用“clr enabled”並且可能創建登錄並授予UNSAFE ASSEMBLY權限。數據庫使用者只需要CREATE ASSEMBLY在其餘部分完成後載入程序集。但是這個腳本是包羅萬象的，所以它假設執行它的登錄名/使用者擁有所有必要的權限。

引用自：https://dba.stackexchange.com/questions/233807

在 SQL Server 中綁定 dll

相關問答

如何在 C# SQLCLR 函式中將多個雙精度數組作為表返回

MS SQL - CLR 儲存過程以已刪除使用者身份執行

升級到 SQL Server 2017 後，錯誤消息 10314、級別 16、狀態 11 和 SAFE 程序集

SQL Server CLR 函式中的 System.Web

錯誤 0x80131904 未能載入程序集 id 65536 / HRESULT：microsoft.sqlserver.integrationservices.server 的 0x8013150A

對於 CLR 函式輸入參數，使用 Strings 而不是 SqlStrings 是否安全？