Access to the path 'c:\some\path' is denied for MSSQL CLR
I have permissions to the database, I'm dbo. I'm an admin on the server. I have permissions in the file system.
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:
- For local (on the box) access, it is as simple as either
- the service account (default behavior) for the Database Engine (i.e. MSSQLSERVER or MSSQL$InstanceName) service needing permission, or
- if Impersonation has been implemented in the code
- and a the login executing the code is a Windows Login, not a SQL Server login, then it is that Windows account that needs permission
- but a SQL Server login is being used, the external access is still done as the Database Engine service account
- For remote access (shared drive), constrained delegation might need to be set up (via Active Directory; including SPNs). Good 'ol Kerberos double-hop issue. In this case, you would see a difference between logging into SQL Server from another computer, other than the server it is running on vs logging directly onto the server running SQL Server and then connecting to the local SQL Server instance.
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):
- Go to the "Properties" of the path in question (the specific file or folder reporting the error)
- Go to the "Security" tab
- Click the "Advanced" button
- Go to the "Effective Access" tab
- Click the "select a user" link
- Enter in the fully account name (e.g.
NT Service\MSSQLSERVER
) - Click the "OK" button
- Click the "View effective access" button
- Does that account have access to that resource?
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 be EXTERNAL_ACCESS
. Not too many file system operations should require UNSAFE
. One of them is getting a list of fixed drives, but not sure of what else.
Make sure the service account running SQL server has access to those paths.
That's going to be the account actually interacting with the files on disk.