7

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?

  • Does the CLR code use any type of impersonation? – Mr.Brownstone May 23 at 20:59
  • No impersonation is done. – WillG May 23 at 21:33
12

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 Service\MSSQLSERVER)
  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.

  • @WillG Is the G: drive local or remote? The title of the question states the C: drive. Is this different between the system that works and the system that doesn't? If this is a remote file share, the computer account might need to be given access since I believe NT Service\name is only known locally, hence remote access is done as ComputerName$. – Solomon Rutzky May 23 at 22:22
  • Yes, it is local. There are no network mapped drives in this. – WillG May 23 at 22:25
  • Your are spot on, the files are still set to no access. Looking at "View effective access" I can see that the top level folder (G:) has the permissions I set, but none of the files or folders under that inherited the permissions. – WillG May 23 at 23:03
2

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.

0

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.

  • 2
    Running SSMS "as Administrator" should not have any effect on whether or not SQLCLR code, executed by SQL Server, not by SSMS, has access to the file system. The only exception might be for SQL Server Express LocalDB, as that runs as a user-process and not as a service. – Solomon Rutzky May 24 at 2:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.