MSSQL 2017 Dev版中的错误 [英] error in MSSQL 2017 Dev edition

问题描述

为汇编创建或更改汇编"PhoneticServices"使用SAFE或EXTERNAL_ACCESS选项失败，因为sp_configure的'clr strict security'选项设置为1.  Microsoft建议您使用证书
或具有相应登录权限的非对称密钥对程序集进行签名，并使用UNSAFE ASSEMBLY权限。

解决方案

启动SQL 2017 Microsoft引入了一个名为"clr strict security"的新sp_configure选项，默认情况下设置为1（打开）。启用此设置后，SQL Server会将所有程序集（SAFE，EXTERNAL_ACCESS，UNSAFE）视为
标记为UNSAFE。  

有几种方法可以解决这个问题：

• 禁用"clr strict security"选项{不推荐的方法}
• 将数据库设置为可信任{再次建议不要使用安全选项}
• 使用具有相应登录权限的证书对程序集进行签名，使用UNSAFE ASSEMBLY权限{recommended}
• 使用非对称密钥对程序集进行签名，该密钥具有相应的登录权限，具有UNSAFE ASSEMBLY权限{recommended}

<我以这种方式解决了这个问题：

1. 创建自签名证书
2. 使用此证书签署程序集
3. 使用步骤（1）中生成的证书创建SQL Server证书
4. 使用SQL Server证书创建登录并授予不安全的程序集权限
5. 编译[签名]汇编代码

希望这会有所帮助！

CREATE or ALTER ASSEMBLY for assembly "PhoneticServices" with the SAFE or EXTERNAL_ACCESS option failed because the 'clr strict security' option of sp_configure is set to 1.  Microsoft recommends that you sign the assembly with a certificate or asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission.

解决方案

Starting SQL 2017 Microsoft has introduced a new sp_configure option called "clr strict security", which by default is set to 1 (on). When the setting is on, SQL Server treats all assemblies (SAFE, EXTERNAL_ACCESS, UNSAFE) as if they were marked UNSAFE.  

There are few ways to go around this:

• Disable "clr strict security" option {Not a recommended approach}
• Set Database to Trustworthy {again not recommended security option}
• Sign the assembly with a certificate that has a corresponding login with UNSAFE ASSEMBLY permission {recommended}
• Sign the assembly with an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission {recommended}

I had dealt with this issue in this way:

1. Create a self signed certificate
2. Sign the assembly using this certificate
3. Create SQL Server certificate using cert generated in step (1)
4. Create login using SQL Server certificate and grant unsafe assembly permission
5. Compile [signed] assembly code

Hope this helps!

这篇关于MSSQL 2017 Dev版中的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！