SQL Server 2008：崩溃安全如何是一个加载非托管库的CLR存储过程

时间：2022-11-02 01:47:24

We've got a regular (i.e. not extended) stored procedure in SQL Server 2000 that calls an external exe. That exe, in turn, loads a .dll that came from an SDK and calls some procedures from it (i.e. Init, DoStuff, Shutdown).

我们在SQL Server 2000中有一个调用外部exe的常规(即非扩展)存储过程。反过来,该exe加载来自SDK的.dll并从中调用一些过程(即Init,DoStuff,Shutdown)。

The only reason we have this external exe thing is because we didn't want to create an extended stored procedure that would call the .dll. We believed that if the dll crashes (an unlikely event but still) then the SQL Server process will crash as well which is not what we wanted. With an external exe, only that exe would crash.

我们有这个外部exe的唯一原因是因为我们不想创建一个可以调用.dll的扩展存储过程。我们相信如果dll崩溃(一个不太可能的事件,但仍然),那么SQL Server进程也将崩溃,这不是我们想要的。使用外部exe,只有exe会崩溃。

Now, we're upgrading to SQL Server 2008 and considering creating a CLR stored procedure that calls the thing and therefore getting rid of the exe. This SP would be marked as UNSAFE, of course. The question therefor is, is it safe (safer, safe enough etc.) to do it that way as compared to the extended SP approach?

现在,我们正在升级到SQL Server 2008并考虑创建一个调用该东西的CLR存储过程,从而摆脱了exe。当然,该SP将被标记为UNSAFE。问题是,与扩展SP方法相比,这样做是否安全(更安全,更安全等)?

The only relevant thing I've hunted down on BOL is:

我在BOL上追捕的唯一相关事情是:

Specifying UNSAFE allows the code in the assembly to perform illegal operations against the SQL Server process space, and hence can potentially compromise the robustness and scalability of SQL Server

指定UNSAFE允许程序集中的代码对SQL Server进程空间执行非法操作,因此可能会损害SQL Server的健壮性和可伸缩性

, but I'm not sure whether it answers my question as I'm not after 'robustness and scalability', rather after stability and keeping the thing up and running.

,但我不确定它是否回答了我的问题,因为我没有追求'稳健性和可扩展性',而是在稳定性和保持运行之后。

PS: We want to get rid of the exe because it causes inconviniences when managing SP permissions (you know, that stuff that suddenly applies to you if you call a SP that contains xp_cmdshell).

PS:我们想要摆脱exe,因为它在管理SP权限时会造成不便(你知道,如果你调用包含xp_cmdshell的SP,这些东西突然适用于你)。

2 个解决方案

#1

Since this code was originally used with extended stored procedures, it sounds like it is unmanaged code. Bugs in unmanaged code can easily crash your process.

由于此代码最初与扩展存储过程一起使用,因此听起来它是非托管代码。非托管代码中的错误很容易导致进程崩溃。

CLR integration is much more robust than extended stored procedures, but the code still runs in-process, so errors can take down or corrupt SQL Server. (For comparison, in theory, a SAFE CLR routine won't be able to corrupt SQL Server although even it could cause problems that reduce your server's availability without totally taking down the SQL Server.)

CLR集成比扩展存储过程更强大,但代码仍然在进程中运行,因此错误可能会导致SQL Server崩溃或损坏。 (为了进行比较,理论上,SAFE CLR例程将无法破坏SQL Server,尽管它可能会导致在不完全关闭SQL Server的情况下降低服务器可用性的问题。)

Basically, the only ways to not crash SQL Server in this scenario are:

基本上,在这种情况下,不使SQL Server崩溃的唯一方法是:

  1. Avoid using the functionality that crashes.

    2. 避免使用崩溃的功能。

  2. Fix the buggy code.

    3. 修复错误的代码。

  3. Run the code in a separate process (launch an executable, call a Windows service, call a web service, etc.). You can write a managed .NET DLL to perform this interaction. Most likely, you will still need to load it UNSAFE, but--if it is written properly--in reality it can be quite safe.

    4. 在单独的进程中运行代码(启动可执行文件,调用Windows服务,调用Web服务等)。您可以编写托管的.NET DLL来执行此交互。最有可能的是,你仍然需要加载它UNSAFE,但是 - 如果写得正确 - 实际上它可以非常安全。

#2

The question therefor is, is it safe (safer, safe enough etc.) to do it that way as compared to the extended SP approach?

问题是,与扩展SP方法相比,这样做是否安全(更安全,更安全等)?

Generally yes. I mean, if you are shelling out to an OS process, then you are shelling out to an OS process. I don't see how using the Extended Stored Procedure API to do that would necessarily be safer than the SQLCLR API, especially when the thing that might crash is an OS process, sitting outside of the database.

一般是的。我的意思是,如果您正在进行操作系统进程,那么您正在进行操作系统进程。我没有看到使用扩展存储过程API如何做到这一点必然比SQLCLR API更安全,特别是当可能崩溃的东西是一个OS进程,坐在数据库之外时。

Of course, I am not certain about the XP API since I have not used it, but I do know the following:

当然,我不确定XP API,因为我没有使用它,但我知道以下内容:

  • The XP API is deprecated and the recommendation is that all new projects that could be done in either of those technologies should be done in SQLCLR.

    • 不推荐使用XP API,建议所有可以在这两种技术中完成的新项目都应该在SQLCLR中完成。

  • The SQLCLR does allow for more granular permissions than those other two, including the ability to do Impersonation (if the Login executing the SQLCLR objects is a Windows Login).

    • SQLCLR允许比其他两个更细化的权限,包括执行模拟的能力(如果执行SQLCLR对象的登录是Windows登录)。

  • The SQLCLR API is separated process/memory-wise by both Database and Assembly Owner (i.e. the User specified by the AUTHORIZATION clause). Hence you can have a problem with an Assembly in one DB without it affecting SQLCLR objects in other DBs (or even in the same DB if there are Assemblies owned by another User, though in practice this probably rarely is ever the case as most people just use the default which is dbo).

    • SQLCLR API由数据库和程序集所有者(即AUTHORIZATION子句指定的用户)分离进程/内存。因此,您可以在一个数据库中遇到一个程序集的问题,而不会影响其他数据库中的SQLCLR对象(如果有另一个用户拥有的程序集,甚至在同一个数据库中,尽管在实践中这可能很少出现,因为大多数人只是使用默认值dbo)。

I'm not sure whether it answers my question as I'm not after 'robustness and scalability', rather after stability and keeping the thing up and running.

我不确定它是否回答了我的问题,因为我没有追求“稳健性和可扩展性”,而是在稳定性和保持运行之后。

Well, there are certainly things you can do within SQLCLR when the Assembly is set to UNSAFE:

那么,当大会设置为UNSAFE时,你可以在SQLCLR中做些事情:

  • potentially write to the Registry (depending on the access granted to the Log On As account running the SQL Server process, or the Login executing the SQLCLR function IF Impersonation is enabled and it is a Windows Login).

    • 可能写入注册表(取决于授予运行SQL Server进程的登录身份帐户的访问权限,或执行SQLCLR功能的登录,如果启用了模拟,则它是Windows登录)。

  • potentially write to the file system

    • 可能写入文件系统

  • potentially interact with processes running on the system

    • 可能与系统上运行的进程交互

  • share memory with other SQL Server SPIDs (i.e. Sessions) executing functions from the same Assembly (meaning that specific Assembly, in that DB, owned by that User). This probably eludes people the most as it is unexpected when you are used to Console apps and Windows apps having their own individual memory spaces, yet here, because it is a single AppDomain per Assembly per DB per Owner, all sessions executing that code do share all static variables. A lot of code is written with the assumption that the AppDomain is private and so storing values in static variables is efficient as it caches the value, but in SQLCLR, you can get unexpected behavior if two processes are overwriting each other's values and reading the other session's value.

    • 与来自同一程序集的其他SQL Server SPID(即Sessions)共享内存(意味着该DB中的特定程序集,由该用户拥有)。当您习惯使用具有各自内存空间的控制台应用程序和Windows应用程序时,这可能会使人们最不可思议,因为这是因为每个所有者每个数据库每个程序集是一个AppDomain,所有执行该代码的会话都会共享所有静态变量。编写大量代码时假设AppDomain是私有的,因此将值存储在静态变量中是有效的,因为它缓存了值,但是在SQLCLR中,如果两个进程覆盖彼此的值并读取其他值,则会出现意外行为会话的价值。

  • potential memory leaks. The Host Protection Attributes attempt to prevent you from using built-in .NET functionality that could do this, such as using TimeZoneInfo to convert times between TimeZoneIDs, but Host Protection Attributes are not enforced on UNSAFE Assemblies.

    • 潜在的内存泄漏。主机保护属性试图阻止您使用可执行此操作的内置.NET功能,例如使用TimeZoneInfo在TimeZoneID之间转换时间,但不在UNSAFE程序集上强制执行主机保护属性。

  • It is possible that the thread running the SQLCLR method is handled differently when executing UNSAFE / FullTrust code (Cooperative Multitasking vs Preemptive). I thought I had read that UNSAFE threads are managed differently, but am not sure where I read it and am looking for the source.

    • 执行UNSAFE / FullTrust代码(Cooperative Multitasking vs Preemptive)时,运行SQLCLR方法的线程可能会有不同的处理方式。我以为我已经读过UNSAFE线程的管理方式不同,但我不确定我在哪里阅读它并且正在寻找源代码。

But all of the above being said, if you are calling an external EXE, it has its own AppDomain.

但是上述所有内容都说,如果你调用外部EXE,它有自己的AppDomain。

So, what you can do is either:

那么,你能做的是:

  1. continue to call the EXE using a SQLCLR wrapper to Process.Start(). This gives you both the process/memory separation and the ability to more easily control permissions to a single Stored Procedure that will only ever call this EXE and nobody can change it (at least not without changing that SQLCLR code and reinstalling the Assembly).

    继续使用SQLCLR包装器调用EXE到Process.Start()。这为您提供了进程/内存分离以及更轻松地控制对单个存储过程的权限的能力,该存储过程只会调用此EXE而没有人可以更改它(至少在不更改SQLCLR代码并重新安装程序集的情况下)。

  2. install an instance of SQL Server Express on the same machine, load the SQLCLR objects there, and create Linked Servers in both directions (from current SQL Server instance to and from the new SQL Server Express instance) so you can communicate easily between them. This will allow you to quarantine the SQLCLR execution and keep it away from the main SQL Server process.

    在同一台机器上安装SQL Server Express实例,在那里加载SQLCLR对象,并在两个方向创建链接服务器(从当前SQL Server实例到新SQL Server Express实例),以便您可以在它们之间轻松进行通信。这将允许您隔离SQLCLR执行并使其远离主SQL Server进程。

Of course, that all being said, how much of a concern is this really? Meaning, how likely is it that a process fully crashes and takes down everything with it? Sure, it's not impossible, but usually a crash would take down just the AppDomain and not the CLR host itself. I would think it far more likely that code that doesn't crash but is written poorly and consumes too much memory and/or CPU would be the problem people run into.

当然,总而言之,这真的是多少关注?意思是,一个进程完全崩溃并用它取下所有东西的可能性有多大?当然,这并非不可能,但通常崩溃只会导致AppDomain而不是CLR主机本身。我认为更有可能的是,代码不会崩溃,但写得很差并且消耗太多内存和/或CPU将成为人们遇到的问题。

#1

Since this code was originally used with extended stored procedures, it sounds like it is unmanaged code. Bugs in unmanaged code can easily crash your process.

由于此代码最初与扩展存储过程一起使用,因此听起来它是非托管代码。非托管代码中的错误很容易导致进程崩溃。

CLR integration is much more robust than extended stored procedures, but the code still runs in-process, so errors can take down or corrupt SQL Server. (For comparison, in theory, a SAFE CLR routine won't be able to corrupt SQL Server although even it could cause problems that reduce your server's availability without totally taking down the SQL Server.)

CLR集成比扩展存储过程更强大,但代码仍然在进程中运行,因此错误可能会导致SQL Server崩溃或损坏。 (为了进行比较,理论上,SAFE CLR例程将无法破坏SQL Server,尽管它可能会导致在不完全关闭SQL Server的情况下降低服务器可用性的问题。)

Basically, the only ways to not crash SQL Server in this scenario are:

基本上,在这种情况下,不使SQL Server崩溃的唯一方法是:

  1. Avoid using the functionality that crashes.

    2. 避免使用崩溃的功能。

  2. Fix the buggy code.

    3. 修复错误的代码。

  3. Run the code in a separate process (launch an executable, call a Windows service, call a web service, etc.). You can write a managed .NET DLL to perform this interaction. Most likely, you will still need to load it UNSAFE, but--if it is written properly--in reality it can be quite safe.

    4. 在单独的进程中运行代码(启动可执行文件,调用Windows服务,调用Web服务等)。您可以编写托管的.NET DLL来执行此交互。最有可能的是,你仍然需要加载它UNSAFE,但是 - 如果写得正确 - 实际上它可以非常安全。

#2

The question therefor is, is it safe (safer, safe enough etc.) to do it that way as compared to the extended SP approach?

问题是,与扩展SP方法相比,这样做是否安全(更安全,更安全等)?

Generally yes. I mean, if you are shelling out to an OS process, then you are shelling out to an OS process. I don't see how using the Extended Stored Procedure API to do that would necessarily be safer than the SQLCLR API, especially when the thing that might crash is an OS process, sitting outside of the database.

一般是的。我的意思是,如果您正在进行操作系统进程,那么您正在进行操作系统进程。我没有看到使用扩展存储过程API如何做到这一点必然比SQLCLR API更安全,特别是当可能崩溃的东西是一个OS进程,坐在数据库之外时。

Of course, I am not certain about the XP API since I have not used it, but I do know the following:

当然,我不确定XP API,因为我没有使用它,但我知道以下内容:

  • The XP API is deprecated and the recommendation is that all new projects that could be done in either of those technologies should be done in SQLCLR.

    • 不推荐使用XP API,建议所有可以在这两种技术中完成的新项目都应该在SQLCLR中完成。

  • The SQLCLR does allow for more granular permissions than those other two, including the ability to do Impersonation (if the Login executing the SQLCLR objects is a Windows Login).

    • SQLCLR允许比其他两个更细化的权限,包括执行模拟的能力(如果执行SQLCLR对象的登录是Windows登录)。

  • The SQLCLR API is separated process/memory-wise by both Database and Assembly Owner (i.e. the User specified by the AUTHORIZATION clause). Hence you can have a problem with an Assembly in one DB without it affecting SQLCLR objects in other DBs (or even in the same DB if there are Assemblies owned by another User, though in practice this probably rarely is ever the case as most people just use the default which is dbo).

    • SQLCLR API由数据库和程序集所有者(即AUTHORIZATION子句指定的用户)分离进程/内存。因此,您可以在一个数据库中遇到一个程序集的问题,而不会影响其他数据库中的SQLCLR对象(如果有另一个用户拥有的程序集,甚至在同一个数据库中,尽管在实践中这可能很少出现,因为大多数人只是使用默认值dbo)。

I'm not sure whether it answers my question as I'm not after 'robustness and scalability', rather after stability and keeping the thing up and running.

我不确定它是否回答了我的问题,因为我没有追求“稳健性和可扩展性”,而是在稳定性和保持运行之后。

Well, there are certainly things you can do within SQLCLR when the Assembly is set to UNSAFE:

那么,当大会设置为UNSAFE时,你可以在SQLCLR中做些事情:

  • potentially write to the Registry (depending on the access granted to the Log On As account running the SQL Server process, or the Login executing the SQLCLR function IF Impersonation is enabled and it is a Windows Login).

    • 可能写入注册表(取决于授予运行SQL Server进程的登录身份帐户的访问权限,或执行SQLCLR功能的登录,如果启用了模拟,则它是Windows登录)。

  • potentially write to the file system

    • 可能写入文件系统

  • potentially interact with processes running on the system

    • 可能与系统上运行的进程交互

  • share memory with other SQL Server SPIDs (i.e. Sessions) executing functions from the same Assembly (meaning that specific Assembly, in that DB, owned by that User). This probably eludes people the most as it is unexpected when you are used to Console apps and Windows apps having their own individual memory spaces, yet here, because it is a single AppDomain per Assembly per DB per Owner, all sessions executing that code do share all static variables. A lot of code is written with the assumption that the AppDomain is private and so storing values in static variables is efficient as it caches the value, but in SQLCLR, you can get unexpected behavior if two processes are overwriting each other's values and reading the other session's value.

    • 与来自同一程序集的其他SQL Server SPID(即Sessions)共享内存(意味着该DB中的特定程序集,由该用户拥有)。当您习惯使用具有各自内存空间的控制台应用程序和Windows应用程序时,这可能会使人们最不可思议,因为这是因为每个所有者每个数据库每个程序集是一个AppDomain,所有执行该代码的会话都会共享所有静态变量。编写大量代码时假设AppDomain是私有的,因此将值存储在静态变量中是有效的,因为它缓存了值,但是在SQLCLR中,如果两个进程覆盖彼此的值并读取其他值,则会出现意外行为会话的价值。

  • potential memory leaks. The Host Protection Attributes attempt to prevent you from using built-in .NET functionality that could do this, such as using TimeZoneInfo to convert times between TimeZoneIDs, but Host Protection Attributes are not enforced on UNSAFE Assemblies.

    • 潜在的内存泄漏。主机保护属性试图阻止您使用可执行此操作的内置.NET功能,例如使用TimeZoneInfo在TimeZoneID之间转换时间,但不在UNSAFE程序集上强制执行主机保护属性。

  • It is possible that the thread running the SQLCLR method is handled differently when executing UNSAFE / FullTrust code (Cooperative Multitasking vs Preemptive). I thought I had read that UNSAFE threads are managed differently, but am not sure where I read it and am looking for the source.

    • 执行UNSAFE / FullTrust代码(Cooperative Multitasking vs Preemptive)时,运行SQLCLR方法的线程可能会有不同的处理方式。我以为我已经读过UNSAFE线程的管理方式不同,但我不确定我在哪里阅读它并且正在寻找源代码。

But all of the above being said, if you are calling an external EXE, it has its own AppDomain.

但是上述所有内容都说,如果你调用外部EXE,它有自己的AppDomain。

So, what you can do is either:

那么,你能做的是:

  1. continue to call the EXE using a SQLCLR wrapper to Process.Start(). This gives you both the process/memory separation and the ability to more easily control permissions to a single Stored Procedure that will only ever call this EXE and nobody can change it (at least not without changing that SQLCLR code and reinstalling the Assembly).

    继续使用SQLCLR包装器调用EXE到Process.Start()。这为您提供了进程/内存分离以及更轻松地控制对单个存储过程的权限的能力,该存储过程只会调用此EXE而没有人可以更改它(至少在不更改SQLCLR代码并重新安装程序集的情况下)。

  2. install an instance of SQL Server Express on the same machine, load the SQLCLR objects there, and create Linked Servers in both directions (from current SQL Server instance to and from the new SQL Server Express instance) so you can communicate easily between them. This will allow you to quarantine the SQLCLR execution and keep it away from the main SQL Server process.

    在同一台机器上安装SQL Server Express实例,在那里加载SQLCLR对象,并在两个方向创建链接服务器(从当前SQL Server实例到新SQL Server Express实例),以便您可以在它们之间轻松进行通信。这将允许您隔离SQLCLR执行并使其远离主SQL Server进程。

Of course, that all being said, how much of a concern is this really? Meaning, how likely is it that a process fully crashes and takes down everything with it? Sure, it's not impossible, but usually a crash would take down just the AppDomain and not the CLR host itself. I would think it far more likely that code that doesn't crash but is written poorly and consumes too much memory and/or CPU would be the problem people run into.

当然,总而言之,这真的是多少关注?意思是,一个进程完全崩溃并用它取下所有东西的可能性有多大?当然,这并非不可能,但通常崩溃只会导致AppDomain而不是CLR主机本身。我认为更有可能的是,代码不会崩溃,但写得很差并且消耗太多内存和/或CPU将成为人们遇到的问题。

标签：

相关文章