Accessing the Sql Server CLR File System from UDF

Question

I wrote a simple UDF that needs to draw graphics and save it to disk. In fact, I use UDF as a proxy between SQL SERVER and R, so UDF passes the R script to the R engine from SQL SERVER via DCOM. Everything works fine until I try to build graphics or save it to disk. I created an assembly with UNSAFE permissions.

So, this happens as follows: SQL Engine → UDF → (D) COM SERVER → R → (D) COM SERVER → UDF → SQL Engine.

So, my first problem: can I create a GUI from UDF? Probably not, but worth asking.

The second problem is why an assembly with UNSAFE permission cannot access the file system. I do not get any error, just nothing happens.

The R environment is in a different address space, so I see no reason why SQL Server permissions for the CLR would affect it.

thanks

Edit:

I tried to do the same with the procedures. Now an empty file is created. This is my test code R:

jpeg("C:\\test1.jpg"); x <- rnorm(100); hist(x); dev.off() 

Any idea what is going on here?

Answer 1

• You cannot create a GUI with server code
• UNSAFE is dangerous, EXTERNAL_ACCESS will be better since it still allows access to the file system.
• If there is no error, there is a good chance that your code works correctly, but it does something different from what you expect from it; can you add debug code or connect debugger?
• The procedure here is more appropriate than UDF, because they are much more flexible.

But it’s not clear why you are doing this. It would be much easier to write a small (?) Program outside of SQL Server to retrieve data from a database, invoke your R program, and save the image. SQL Server server code is great for processing data, but very inconvenient for interacting with file systems and external resources in general, even if you use CLR code.

Is there any specific reason why you need to do this from SQL Server?

Answer 2

To access the file system, it is better to use SSIS. You can edit and test the package at any time, do the registration when you need. You can also easily add the GUI in VisualStudio to this package. Accessing the file system from DatabaseEngine is not a good practice due to possible security issues.

Answer 3

My first problem: can I create a GUI from UDF?

You can use System.Drawing to create and / or manage images, but:

• only if the Assembly has PERMISSION_SET of UNSAFE , and
• you load the System.Drawing assembly in SQL Server since UNSAFE

The second problem is why an assembly with UNSAFE permission cannot access the file system. I don't get any errors, just nothing happens.

A node marked EXTERNAL_ACCESS or UNSAFE has access to external resources. Trying to do this and not get an error shows that this is allowed. Although, it is unclear that “nothing happens” means because either you have a catch that swallows the error, or the file was created in a directory that you did not expect, because you used the relative path instead of the absolute path.

Two problems (although they are related to each other) with external access to resources:

• What type of Windows / Active Directory login is used for this access. By default, SQLCLR (like xp_cmdshell ) will access the system in the security context of the Logon account for the MSSQLSERVER process. Or you have the option to enable an impersonation that will take on the security context of the person who executed the SQLCLR code, provided that Login (in SQL Server) is associated with a Windows / Active Directory account. Logging on to SQL Server cannot use impersonation.

• Based on which account accesses the external resource, what are their permissions for this resource? If it is a file system, does this account have write access to the specified path?

In terms of the above example, R (i.e. create C:\test1.jpg ) and assuming that the impersonation is not used: does the account have MSSQLSERVER (or MSSQL $ {InstanceName} ), since they have write permission C: \ ? Keep in mind that this is C: the drive of the server that is running SQL Server, not the local computer, if this instance of SQL Server is not running on your computer.