解决因SQL Server数据库引用程序集而不得不开启trustworthy方法

​前文中提到有提到，当SQL Server 数据库开启trustworthy时，存在着用户提升权限的风险。那么，我们是不是直接查出开启trustworthy的数据库，将其关闭就解决风险了呢？如下脚本可以直接关闭实例下所有开启trustworthy数据库的trustworthy属性：

use master
go
declare @sql varchar(max)     
set @sql=''
select @sql=@sql+'alter database '+ QUOTENAME(name,'[')+' set trustworthy ' +' off '
from sys.databases
where is_trustworthy_on=1
print @sql
--exec(@sql)

有心的朋友可能已经注意到，上面脚本的最后一句是注释掉的，我们是不能直接执行这个脚本的（重要的事情说三篇，不能直接执行！！！不能直接执行！！！不能直接执行！！！），我们必须确认数据库上是否有需要开启trustworthy的内容，如程序集。

    带有EXTERNAL_ACCESS 或 UNSAFE的程序集能正常执行的方案有两种：

1. 开启数据库trustworthy，保证数据库拥有者有程序集 [EXTERNAL ACCESS] 或者 [UNSAFE] 权限。

2.  程序集已使用其对应登录名具有 EXTERNAL ACCESS ASSEMBLY 权限的证书或非对称密钥加以签名。

考虑到前文中提到的安全隐患，如果数据库中存在着带有EXTERNAL_ACCESS 或 UNSAFE的程序集，并且数据库的trustworthy为开启状态，我们就需要采用方案2来代替方案1，消除安全隐患，下面我将分享方案2的处理步骤。

 

为ddl加强名称签名

强名称签名的私钥公钥生成

本文中是使用Windows强名称签名工具sn生成私钥和公钥的，首先我们需要到目录 ：

C:\Program Files (x86)\Microsoft SDKs\Windows\v8.1A\bin\NETFX 4.5.1 Tools 下查找是否有sn工具，没有的话，就要另外安装，如果存在，以管理员身份打开CMD，切换到上面对应目录下：

使用强名称工具sn生成私钥、公钥的具体脚本及步骤如下：

最终将生成的私钥pri.snk 拷贝到项目文件中

在VS解决方案资源管理器中，右击项目名称→属性

在弹出的属性列表中选择“签名”→勾选为程序集签名，找到前面生成的私钥文件，这里是pri.snk：

最后重新生成dll，到此完成程序集的强名称签名。

将dll 部署到SQL Server数据库

下面我将给出将强名称签名的程序集部署到数据库上的脚本，并进行测试，如下：

use DB1
go
exec sp_configure 'show advanced options',1
go
reconfigure
go
exec sp_configure 'clr enabled',1
reconfigure with override
exec sp_configure 'show advanced options',0
go
reconfigure
go
​
USE master  --这个数据库一定是master
--创建非对称密钥
CREATE ASYMMETRIC KEY SQLCLRTestKey FROM EXECUTABLE FILE = 'D:\TgSQLPlus\Skew.dll'  
--创建登录名
CREATE LOGIN SQLCLRTestLogin FROM ASYMMETRIC KEY SQLCLRTestKey
--把权限授予给该登录名 
GRANT EXTERNAL ACCESS ASSEMBLY TO SQLCLRTestLogin;
USE DB1
--创建程序集
create ASSEMBLY DescriptiveStatistics from
'D:\TgSQLPlus\Skew.dll'
with PERMISSION_SET=EXTERNAL_ACCESS;
--创建用户定义函数（UDF）
CREATE AGGREGATE dbo.Skew(@s float)
returns float
external name DescriptiveStatistics.Skew;
create table test (name varchar(60),id bigint)
--测试
insert into test
select top 100 name,object_id
from sys.objects
go 10
with CustomerSalesCTE as
(
select
      name
       ,SUM(id) as TotalAmount
from test 
group by name
)
select
       ROUND(avg(TotalAmount),2) as Average
       ,ROUND(STDEV(TotalAmount),2) as StandardDeviation
       ,ROUND(dbo.Skew(cast(TotalAmount as decimal(30,2))),6) as Skewness
from CustomerSalesCTE
--确认trustworthy是否启用
select name,is_trustworthy_on from sys.databases
where name='DB1'

测试发现trustworthy关闭状态，自定义函数正常运行。

注意：

• 如果两个程序集使用同一个私钥进行签名，在master库创建非对称秘钥时，会报如下错误：

消息 15396,级别 16,状态 1,第 103 行 名为 '' 的非对称密钥已存在,或已将此非对称密钥添加到该数据库中。

• 在trustworthy关闭状态下，要先在master库创建非对称秘钥，使用非对称秘钥创建登陆名，并赋予[EXTERNAL ACCESS]权限，然后再在指定数据库中创建程序集，顺序不能颠倒，否则会报如下错误：

消息 10327，级别 14，状态 1，第 111 行

针对程序集 'DatabaseKurt' 的 CREATE ASSEMBLY 失败，因为程序集 'DatabaseKurt' 未获授权，不满足 PERMISSION_SET = EXTERNAL_ACCESS。满足以下两个条件之一时将给程序集授权: 数据库所有者(DBO)拥有 EXTERNAL ACCESS ASSEMBLY 权限，且数据库具有 TRUSTWORTHY 数据库属性；或者，程序集已使用其对应登录名具有 EXTERNAL ACCESS ASSEMBLY 权限的证书或非对称密钥加以签名。

• 登陆账户的权限如果和创建程序集PERMISSION_SET选项不一致，即赋予账户[EXTERNAL ACCESS]，PERMISSION_SET=UNSAFE，或者相反时，创建程序集亦会出现如下错误：

消息 10327，级别 14，状态 1，第 111 行

针对程序集 'DatabaseKurt' 的 CREATE ASSEMBLY 失败，因为程序集 'DatabaseKurt' 未获授权，不满足 PERMISSION_SET = UNSAFE。满足以下两个条件之一时将给程序集授权: 数据库所有者(DBO)拥有 UNSAFE ASSEMBLY 权限，且数据库具有 TRUSTWORTHY 数据库属性；或者，程序集已使用其对应登录名具有 UNSAFE ASSEMBLY 权限的证书或非对称密钥加以签名。

最后如果测试发现报如下错误：

消息 10314，级别 16，状态 4，第 74 行

在尝试加载程序集 ID 65544 时 Microsoft .NET Framework 出错。服务器可能资源不足，或者不信任该程序集，因为它的 PERMISSION_SET 设置为 EXTERNAL_ACCESS 或 UNSAFE。请重新运行查询，或检查有关的文档了解如何解决程序集信任问题。有关此错误的详细信息:

System.IO.FileLoadException: 未能加载文件或程序集“databasekurt, Version=0.0.0.0, Culture=neutral, PublicKeyToken=5db4a667503bfd56”或它的某一个依赖项。发生与安全有关的错误。 (异常来自 HRESULT:0x8013150A)

System.IO.FileLoadException:

   在 System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)

   在 System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence assemblySecurity, RuntimeAssembly reqAssembly, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)

   在 System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean forIntrospection)

   在 System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)

   在 System.Reflection.Assembly.Load(String assemblyString)

我们需要从头检查各个步骤是否完成。