SQL Server中的绑定dll
我的工作有个小项目。我的任务是在开发数据库( Server 2017)中部署一些SQLCLR代码来测试它。如果测试成功，我们希望在SQLCLR中开发触发器和过程，并将其部署到我们的客户。我们有一个多租户数据库设计。我对Server和SQLCLR非常陌生，实际上这是我的第一个项目。
My问题
- 如何部署Server数据库项目？我将dll绑定到我的dev计算机上的Server。假设它工作得很好，我把它推到源代码管理中。从现在开始，它是否将其部署到来自客户的不同数据库？
- 要将其绑定到开发机器中，我假设需要db_owner/sysadmin权限来为dll的身份验证创建非对称密钥。客户还需要数据库中的权限来绑定dll吗？
What我试过吗？
我做了两个测试项目，一个是在VS17中创建Server数据库项目，另一个是创建一个简单的C#类库
- 在Server数据库项目中，我遵循了教程通往SQLCLR的楼梯并获得了一个执行和数据提供程序错误：
- SQL 72045脚本错误->错误发生在GO状态器上。下面有一条红线
- SQL7214：.net sqlclient数据提供程序: msg 5000，级别16，状态1 ->here
- 在c#-库的情况下，我可以通过在studio中设置TRUSTWORTHY (具有适当的权限)绑定.dll，但我不希望切换安全设置。如果没有适当的权限(我们不知道客户在DB中的权限)，我就无法为服务器中的dll创建一个不对称密钥以供登录。
我还在微软的文档中读到了有关SQLCLR的内容，并尝试了教程SQLQuantumLeap。
编辑：来自SQL72045脚本错误的错误，从GO语句转到IF状态(见下图)。
ConnectioinTypeTest.sql：这个scirpt由AssemblySecuritySetup1、AssemblySecuritySetup2和PreDeploy组合而成。有关其他脚本，请参见下面的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程序集1USE [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程序集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 > 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 > 0)
BEGIN
ROLLBACK TRAN;
END;
SET @ErrorMessage = ERROR_MESSAGE();
RAISERROR(@ErrorMessage, 16, 1);
RETURN; -- exit the script
END CATCH;
END;
--------------------------------------------------------------------------------预部署-安全设置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;
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 > 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 > 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程序集1A28程序集2A29预部署-安全设置A30;
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
A29
预部署-安全设置
A30; 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 > 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 > 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
A28
程序集2
A29
预部署-安全设置
A30
回答 1
Database Administration用户
发布于 2019-04-03 13:00:25
我是这两篇文章的作者。第二篇文章(在https://SqlQuauntumLeap.com/上找到的较新的一篇文章)完全取代了以前在Server上找到的一篇文章(“SQLCLR的阶梯”文章)。而且，由于您使用的是Server 2017，所以需要使用更新的Server 2017。
目前，我不确定与GO语句相关的第一个错误。我可能需要看到更多该部分之前的脚本，或者可能需要更多的错误消息。该错误是来自已完成的安装脚本，还是来自部分预发布脚本？唯一重要的是，一旦您完成了构建，就完成了脚本。有一些部分脚本在构建之前会出现语法错误，因为它是分段完成的，一旦构建过程将这些部分组合在一起，就不会出现任何语法错误。
就权限而言，只需要sysadmin来启用"clr已启用“，并且可能需要创建登录名并授予UNSAFE ASSEMBLY权限。DB用户只需要CREATE ASSEMBLY，以便在完成其馀部分后加载程序集。但是这个脚本是包含所有内容的，所以它假设执行它的登录/用户拥有所有必要的权限。
https://dba.stackexchange.com/questions/233807
复制相似问题
腾讯云开发者
