I think this is a permissions problem, but I'm having trouble locating it.
I have a group of CLRs on one server (SQL Server 2016) and they work as they should. All are marked UNSAFE and they do various types of file I/O (read, write, copy, move, rename, etc.). I can run them via SSMS or from a job with equal ease.
I need to install them on another server (also SQL Server 2016). Using the original Visual Studio Project I have deployed them to the new sever. They show up in SSMS. That part looks fine.
When I, from SSMS, try to run one I get the following error: "Access to the path 'whatever path I passed in' is denied."
I'm logged into SSMS under my windows login. I have permissions to the database, I'm dbo. I'm an admin on the server. I have permissions in the file system.
What else could I be missing?
Best Answer
None of that matters, typically. Unless you (or whoever coded the SQLCLR methods) implemented Impersonation, then the security context used for external operations is that of the service account running SQL Server (similar to
xp_cmdshell
behavior). It is that account that needs permission to the path(s) that you are trying to access.For the sake of completeness regarding file access permissions:
Keep in mind that "DENY"s take precedence over "GRANT"s (just like with SQL Server permissions).
In order to determine if the account used for external access actually has the necessary permission to the folder(s) and/or file(s):
NT Service\MSSQLSERVER
)Are there any DENY permissions anywhere in the path that you are trying to access?
ALSO If all the code is doing is file system stuff, then most likely you don't need to have the assembly marked as
UNSAFE
and it should instead beEXTERNAL_ACCESS
. Not too many file system operations should requireUNSAFE
. One of them is getting a list of fixed drives, but not sure of what else.