sql clr_SQL Server 2017中CLR严格安全性配置设置的影响

sql clr

Every seasoned SQL Server developer will tell you that no matter how hard you try, there are just operations in SQL Server better implemented elsewhere than relying on native Transact-SQL language (T-SQL). Operations such as performing complex calculations, implementing regular expression checks and accessing external web service applications can easily lead to your SQL Server instance incurring significant performance overhead. Thankfully, through its common language runtime (CLR) feature, SQL Server provides developers with a platform to address some of the inconveniences of native T-SQL by supporting an import of assembly files produced from projects written in. Net programming languages (i.e. C#, VB.NET). I have personally found CLR to be very useful when it comes to splitting string characters into multiple delimited lines.

每个经验丰富SQL Server开发人员都会告诉您，无论您多么努力，SQL Server中的操作都比依赖于本机Transact-SQL语言（T-SQL）更好地在其他地方实现。 执行复杂的计算，执行正则表达式检查以及访问外部Web服务应用程序之类的操作很容易导致您SQL Server实例产生大量的性能开销。 庆幸的是，SQL Server通过其公共语言运行时（CLR）功能，为开发人员提供了一个平台，可以通过支持导入用.Net编程语言（例如C＃， VB.NET）。 我个人发现，在将字符串字符分成多行分隔的行时，CLR非常有用。

Unfortunately – with all its benefits – the recently launched SQL Server 2017 introduces security changes in its support for the creation of CLR assemblies which could leave you at a risk of no longer continuing to enjoy the benefits of CLR within a SQL Server 2017 environment. In other words, if you don’t take any actions, your existing CLR-dependent objects (i.e. CLR functions, CLR Stored Procedures etc.) are bound to break as soon as you upgrade your database engine to SQL Server 2017. In this article, we go through the CLR security changes in SQL Server 2017 and provide several options that you could utilise to ensure that your CLR-dependent objects continue to execute in SQL Server 2017 without incurring any significant production downtime.

不幸的是-尽管具有所有优点-最近发布SQL Server 2017在其对CLR程序集创建的支持中引入了安全更改，这可能使您面临无法继续在SQL Server 2017环境中享受CLR好处的风险。 换句话说，如果您不执行任何操作，则在将数据库引擎升级到SQL Server 2017时，现有的依赖CLR的对象（即CLR函数，CLR存储过程等）必然会中断。 ，我们经历了SQL Server 2017中的CLR安全更改，并提供了几个选项，您可以利用这些选项来确保依赖CLR的对象在SQL Server 2017中继续执行，而不会导致任何重大的生产停机。

SQL Server 2017中CLR严格安全性功能的影响 (Impact of CLR Strict Security feature in SQL Server 2017)

SQL Server 2017 introduces CLR Strict Security configuration option that – unless signed with a certificate or asymmetric keys – basically treats CLR assemblies as UNSAFE, thus preventing them from being registered into SQL Server 2017. The CLR Strict Security option is an advanced configuration setting that is enabled by default as can be verified by running the command in Script 1.

SQL Server 2017引入了CLR Strict Security配置选项-除非使用证书或非对称密钥进行签名-否则基本上将CLR程序集视为UNSAFE，从而防止将它们注册到SQL Server 2017中。CLR Strict Security选项是一种高级配置设置，即默认情况下已启用，可以通过运行脚本1中的命令进行验证。

SELECT * FROM sys.configurations WHERE name LIKE 'clr strict security';

The value of 1 shown in Figure 1 means that the CLR Strict Security option is switched on.

图1中的值1表示CLR Strict Security选项处于打开状态。

As you might recall that prior to SQL Server 2017 you could get away from having to sign a CLR assembly by simply granting it the SAFE permission. Say for instance we have successfully written and produced a CLRStringSplit.dll assembly file used to split a string and that we are now just tasked with importing the assembly into a SQL Server 2016 instance, creating a CLR function and calling that CLR function in a T-SQL query. We could achieve all of these tasks through the following steps:

您可能还记得在SQL Server 2017之前，您可以通过简单地授予其SAFE权限而不必签署CLR程序集。 举例来说，我们已经成功编写并生成了用于拆分字符串的CLRStringSplit.dll程序集文件，现在我们的任务是将程序集导入到SQL Server 2016实例中，创建CLR函数并在T中调用该CLR函数。 -SQL查询。 我们可以通过以下步骤完成所有这些任务：

1. USE SampleDB;
   GO
   CREATE ASSEMBLY CLRStringSplit FROM 'C:\sqlclr\CLRStringSplit.dll' WITH PERMISSION_SET = SAF