从 UDF 访问 Sql Server CLR 文件系统
我编写了一个简单的 UDF，它应该绘制图形并将其保存在磁盘上。 实际上，我使用 UDF 作为 SQL SERVER 和 R 之间的代理，因此 UDF 仅通过 DCOM 将 R 脚本从 SQL SERVER 传递到 R 引擎。一切正常，直到我尝试绘制图形或将其保存到磁盘上。我使用不安全的权限创建了程序集。
所以，事情是这样的： SQL引擎-> UDF-> (D)COM服务器-> R-> (D)COM服务器-> UDF-> SQL 引擎。
所以，我的第一个问题是，我可以从 UDF 创建 GUI 吗？我想不是，但值得一问。
第二个问题是，为什么具有 UNSAFE 权限的程序集无法访问文件系统。我没有收到任何错误，只是什么也没发生。
R 环境位于不同的地址空间中，因此我看不出 SQL Engine 的 CLR 权限会影响它的任何原因。
谢谢
编辑：
我尝试用程序做同样的事情。现在创建了一个空文件。这是我的 R 测试代码：
jpeg("C:\\test1.jpg"); x <- rnorm(100); hist(x); dev.off()
知道这里发生了什么吗？
I wrote a simple UDF that should plot a graphic and save it on disk.
Actually, I am using an UDF as a proxy between SQL SERVER and R, so UDF only passes the R script to the R engine from SQL SERVER via DCOM. Everything works fine until I try to plot a graphic or save it to the disk. I created the assembly with UNSAFE permissions.
So, it goes like this:
SQL Engine -> UDF -> (D)COM SERVER -> R -> (D)COM SERVER -> UDF -> SQL Engine.
So, my first problem is, can I create GUI from an UDF? I guess not, but it is worth asking.
The Second problem is, why an assembly with UNSAFE permission cannot access the filesystem. I am not receiving any error, just nothing happens.
The R environment is in the different address space so I don't see any reasons why permissions from SQL Engine for CLRs would affect it.
Thanks
Edit:
I tried to do the same thing with procedures. Now an empty file is created. This is my R test code:
jpeg("C:\\test1.jpg"); x <- rnorm(100); hist(x); dev.off()
Any idea what is happening here?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
但是目前还不清楚为什么你要这样做。在 SQL Server 之外编写一个小（？）程序来从数据库获取数据、调用 R 程序并保存图像可能会容易得多。 SQL Server 中的服务器端代码非常适合处理数据，但通常在与文件系统和外部资源交互时非常尴尬，即使您使用 CLR 代码也是如此。
您需要从 SQL Server 内部执行此操作有什么具体原因吗？
But it's not clear why you're doing things this way. It would probably be much easier to write a small (?) program outside SQL Server to get the data from the database, call your R program, and save the image. Server-side code in SQL Server is great for processing data, but very awkward for interacting with filesystems and external resources in general, even when you use CLR code.
Is there any specific reason why you need to do this from within SQL Server?
要访问文件系统，最好使用 SSIS。您可以随时编辑和测试包，在需要时进行记录。您还可以轻松地将 VisualStudio 中的 GUI 添加到此包中。由于可能存在安全问题，从 DatabaseEngine 访问文件系统不是最佳实践。
To access filesystem it is better to use SSIS. You can edit and test package at any time, make logging when you need. Also you can easily add GUI in VisualStudio to this package. Access filesystem from DatabaseEngine is not best practise due possible security issues.
您可以使用 System.Drawing 来创建和/或操作图像，但是：
PERMISSION_SET为UNSAFE时，并且System.Drawing程序集导入 SQL Server，作为UNSAFE标记为
EXTERNAL_ACCESS或UNSAFE的程序集允许访问外部资源。尝试这样做并且没有收到错误表明这是允许的。尽管如此，目前还不清楚“什么也没发生”意味着什么，因为要么您有一个“吞掉”错误的catch块，要么该文件是在您不期望的目录中创建的，因为您使用了相对路径而不是绝对路径。外部资源访问的两个问题（尽管它们是紧密相连的）是：
该访问使用哪个 Windows/Active Directory 登录名。默认情况下，SQLCLR（就像
xp_cmdshell）将在MSSQLSERVER进程的“登录身份”帐户的安全上下文下访问系统。或者，您可以启用模拟，该模拟将假定执行 SQLCLR 代码的人员的安全上下文，并假设登录名（在 SQL Server 中）与 Windows/Active Directory 帐户关联。 SQL Server 登录名无法使用模拟。根据哪个帐户正在访问外部资源，他们对该资源的权限是什么？如果是文件系统，该帐户是否具有对指定路径的写入权限？
根据给出的 R 示例（即创建
C:\test1.jpg），并假设未使用模拟：MSSQLSERVER（或 < strong>MSSQL${InstanceName}) 服务运行时是否具有对 C:\ 的写入权限？请记住，这是运行 SQL Server 的服务器的 C: 驱动器，而不是您的本地计算机，除非此 SQL Server 实例正在您的计算机上运行。You can use
System.Drawingto create and/or manipulate images, but:PERMISSION_SETofUNSAFE, andSystem.Drawingassembly into SQL Server, asUNSAFEAn assembly marked as either
EXTERNAL_ACCESSorUNSAFEis allowed to access external resources. Attempting to do so and not getting an error shows that it is allowed. Although, it is unclear what "nothing happens" means because either you have acatchblock that is "swallowing" the error, or the file was created in a directory that you weren't expecting because you used a relative path instead of an absolute path.Two issues (though they are tied together) with external resource access are:
Which Windows / Active Directory login is being used for that access. By default, SQLCLR (just like
xp_cmdshell) will access the system under the security context of the "Log On As" account for the MSSQLSERVER process. Or, you have the ability to enable Impersonation which will assume the security context of whoever executed the SQLCLR code, assuming that Login (in SQL Server) is associated with a Windows / Active Directory account. SQL Server logins cannot use Impersonation.Based on which account is accessing the external resource, what are their permissions for that resource? If it's the file system, does that account have write access to the specified path?
In terms of the R example given (i.e. create
C:\test1.jpg), and assuming that Impersonation is not being used: Does the account that the MSSQLSERVER (or MSSQL${InstanceName}) service run as have write permission to C:\ ? Keep in mind that is the C: drive of the server where SQL Server is running, not your local computer, unless this instance of SQL Server is running on your computer.