CODEWITHSUNDEEP
sql-serverrclruser-defined-functionssqlclr
Klark
Klark

Reputation: 8280

Sql Server CLR file system access from UDF

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?

Upvotes: 8

Views: 1178

Answers (3)

Solomon Rutzky
Solomon Rutzky

Reputation: 48874

my first problem is, can I create GUI from an UDF?

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

  • only if the Assembly has a PERMISSION_SET of UNSAFE, and
  • you load the System.Drawing assembly into SQL Server, as UNSAFE

The Second problem is, why an assembly with UNSAFE permission cannot access the filesystem. I am not receiving any error, just nothing happens.

An assembly marked as either EXTERNAL_ACCESS or UNSAFE is 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 a catch block 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.

Upvotes: 0

Dalex
Dalex

Reputation: 3625

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.

Upvotes: 1

Pondlife
Pondlife

Reputation: 16260

  1. You cannot instantiate a GUI from server-side code
  2. UNSAFE is dangerous, EXTERNAL_ACCESS would be better as it still allows filesystem access
  3. If there is no error, there is a good chance that your code is running correctly but it's doing something different from what you expect it to do; can you add some debugging code or attach a debugger?
  4. A procedure is more appropriate here than a UDF because they are much more flexible

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?

Upvotes: 2

Related Questions