Mdthbs

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?

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:

1. For local (on the box) access, it is as simple as either
   
   
   
   
   1. the service account (default behavior) for the Database Engine (i.e. MSSQLSERVER or MSSQL$InstanceName) service needing permission, or
   
   
   2. if Impersonation has been implemented in the code
      
      
      
      
      1. 
         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
      
      
      2. 
         but a SQL Server login is being used, the external access is still done as the Database Engine service account
      
      
      
      
   
   
   
   


2. 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):

1. Go to the "Properties" of the path in question (the specific file or folder reporting the error)


2. Go to the "Security" tab


3. Click the "Advanced" button


4. Go to the "Effective Access" tab


5. Click the "select a user" link


6. Enter in the fully account name (e.g. NT ServiceMSSQLSERVER)


7. Click the "OK" button


8. Click the "View effective access" button


9. 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.

In case that you did all over the ways mentioned above, but it didn't work.
From my experience so far, you may try to open SSMS as Administrator.