none
Access Denied Loading Assembly from Network Share RRS feed

  • Question

  • in SQL Server 2017 (CU12) running on Windows Server 2016 (1607) I try to:

    CREATE ASSEMBLY [MyAssy]
    AUTHORIZATION [dbo]
    FROM @AssyPath
    ...;

    When @AssyPath has a network share file I get:
    CREATE ASSEMBLY failed because it could not open the physical file '\\MyFileServer\MyShare\MyDLL.dll': 5(Access is denied.).
    When @AssyPath has a local file, it works. This shows that the assembly is properly signed and associated with an asymmetric key based login with proper permissions granted.

    I can log into the SQL Server computer via remote desktop logged in as either:
    • My domain account, which has sysadmin in SQL Server and "Full Control" on the file share.
    • The SQL Data Engine service account, which is not a SQL login and has "Full Control" on the file share.
    In both cases I can connect to \\MyFileServer\MyShare and copy MyDLL.dll to the desktop

    So I try something else that requires the SQL Data Engine service account to have access to \\MyFileServer\MyShare:
    BACKUP DATABASE [MyDatabase] TO
    DISK = N'\\MyFileServer\MyShare\MyDatabase.BAK'
    , ...;
    It works, as does the corresponding restore:
    RESTORE DATABASE [MyDatabase2] FROM
    DISK = N'\\MyFileServer\MyShare\MyDatabase.BAK'
    WITH  FILE = 1, ...;

    Just to make sure I was not getting misleading error messages, I tried
    CREATE ASSEMBLY [MyAssy]
    AUTHORIZATION [dbo]
    FROM @AssyPath
    ...;
    With @AssyPath set to the '\\MyFileServer\MyShare\MyDatabase.BAK' file, I got:
    CREATE ASSEMBLY failed because it could not open the physical file '\\MyFileServer\MyShare\MyDatabase.BAK': 5(Access is denied.).
    With @AssyPath set to a copy of MyDatabase.BAK on the SQL Server computer local disk drive, I got (as expected):
    CREATE ASSEMBLY for assembly 'MyAssy' failed because assembly 'MyAssy' is malformed or not a pure .NET assembly.
    This shows that the "could not open the physical file... Access is denied" error message is really a network permissions error, and not based on whatever might be in the file.

    Another SQL Server computer (on the same same subnet, same SQL Data Engine service account, SQL Server 2014, Windows Server 2008 R2) is able to load the assembly from the network share.

    So, what's going on that the SQL Data Engine can access \\MyFileServer\MyShare to backup and restore files, but not to load an assembly?


    Dan Jameson
    Associate Director of IT/DBA
    Children's Oncology Group
    www.ChildrensOncologyGroup.org

    Thursday, November 15, 2018 12:30 AM

Answers

  • I found that I did have SPN problems, and I used the Kerberos Configuration Manager for SQL Server:

    http://www.microsoft.com/en-us/download/details.aspx?id=39046

    I fixed the SPN problem, and the Assembly still would not load from the network share.

    Then I noticed the delegation tab in the Kerberos Configuration Manager for SQL Server, and it reported that the SQL Server service account was flagged as "Do not trust for delegation in Active Directory." I had my system admin set it, temporarily, to Full Delegation. I restarted SQL Server and reloaded the Kerberos Configuration Manager. It then reported "Full delegation may compromise security. Consider Constraining delegation to only those services actually needed." I tried the loading the Assembly, and still would not load from the network share.

    Then I had my "duh!" moment, and I tried a query window logged in as a SQL Server login that is in the sysadmin server role. The Assembly then loaded right away from the network share.


    Dan Jameson
    Associate Director of IT/DBA
    Children's Oncology Group
    www.ChildrensOncologyGroup.org

    • Marked as answer by JediSQL Wednesday, December 12, 2018 1:07 AM
    Wednesday, December 12, 2018 1:07 AM

All replies

  • "Access is denied" can also be due to that the file is locked.

    But it could also be a Kerberos issue. That is, SQL Server is not able to access the file share as you.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, November 15, 2018 9:47 PM
  • But it could also be a Kerberos issue. That is, SQL Server is not able to access the file share as you.

    Yes, this part of Erland's answer is what the issue should be.

    The documentation for BACKUP states:

    SQL Server must be able to read and write to the device; the account under which the SQL Server service runs must have write permissions.

    And the documentation for CREATE ASSEMBLY states both:

    When attempting to access the assembly specified in <client_assembly_specifier>, SQL Server impersonates the security context of the current Windows login. If <client_assembly_specifier> specifies a network location (UNC path), the impersonation of the current login is not carried forward to the network location because of delegation limitations. In this case, access is made using the security context of the SQL Server service account.

    and:

    The Windows login of the user that executes CREATE ASSEMBLY must have read permission on the share and the files being loaded in the statement.

    The part that I bolded in the first quote for CREATE ASSEMBLY is at best misleading, or most likely entirely incorrect. The behavior you are seeing suggests that the documentation for BACKUP is correct: the security context of the service account is being used. And since we know that the service account does indeed have access, then it would seem that CREATE ASSEMBLY is not using the service account. Instead, CREATE ASSEMBLY is impersonating the executing Login's Windows security context. And because impersonation is being used, the security context is limited to the local server. That is the default behavior, of course. There are two ways to fix this:

    1. Enable delegation for your Windows Login so that it can go beyond the local context when being impersonated. This is set up via Active Directory domain administration.
    2. Stop referencing external files in install scripts. This is by far the preferred approach. Your install script should ideally be 100% self-contained. This allows it to be portable / versionable / reliable. Not only will you avoid this permissions issue, but you also will never run into the situation where you do have permissions but the DLL at that path is either missing or a different version. In order to facilitate creating the hex / assembly bits needed for using CREATE ASSEMBLY [name] FROM 0x... as easily as possible, either manually or via automated process, I create an open source utility called BinaryFormatter (hosted on GitHub). And, if you are wanting to automate the process via Visual Studio / SSDT / MSBuild, please see my two posts with instructions on how to accomplish this, even on SQL Server 2017 or newer (and even without VS / SSDT):

      SQLCLR vs. SQL Server 2017, Part 2: "CLR strict security" - Solution 1 (strong name key / Asymmetric Key + Certificate-based solution)

      SQLCLR vs. SQL Server 2017, Part 3: "CLR strict security" - Solution 2 (Certificate-only based solution)

    OR, I just re-read the question and noticed that a SQL Server 2014 instance with the same service account is able to read this DLL from the share. While it is possible that something security-wise was changed in SQL Server 2017 (maybe CREATE ASSEMBLY used to fall-back to using the service account if impersonating the caller's Windows login didn't work), it is also possible that there is a configuration difference between the two servers / instances, such as:

    1. are either set up for delegation? (i.e. do they have valid SPNs, etc)
    2. what is the TRUSTWORTHY setting for the database's where CREATE ASSEMBLY is being executed for each instance?

    Still, all of this could easily be avoided simply by converting the DLL into the string hex bytes (using BinaryFormatter) and not messing with external dependencies in a release script :-)

    Take care,
    Solomon....

    • Edited by Solomon Rutzky Saturday, December 1, 2018 4:03 AM added info
    Saturday, December 1, 2018 12:06 AM
  • Solomon,

    Thank you for the suggestions. I'll give them a try and post back what happens.

    And, the permissions could be a Windows difference, too.


    Dan Jameson
    Associate Director of IT/DBA
    Children's Oncology Group
    www.ChildrensOncologyGroup.org


    • Edited by JediSQL Thursday, December 6, 2018 12:49 AM Windows difference
    Thursday, December 6, 2018 12:43 AM
  • I found that I did have SPN problems, and I used the Kerberos Configuration Manager for SQL Server:

    http://www.microsoft.com/en-us/download/details.aspx?id=39046

    I fixed the SPN problem, and the Assembly still would not load from the network share.

    Then I noticed the delegation tab in the Kerberos Configuration Manager for SQL Server, and it reported that the SQL Server service account was flagged as "Do not trust for delegation in Active Directory." I had my system admin set it, temporarily, to Full Delegation. I restarted SQL Server and reloaded the Kerberos Configuration Manager. It then reported "Full delegation may compromise security. Consider Constraining delegation to only those services actually needed." I tried the loading the Assembly, and still would not load from the network share.

    Then I had my "duh!" moment, and I tried a query window logged in as a SQL Server login that is in the sysadmin server role. The Assembly then loaded right away from the network share.


    Dan Jameson
    Associate Director of IT/DBA
    Children's Oncology Group
    www.ChildrensOncologyGroup.org

    • Marked as answer by JediSQL Wednesday, December 12, 2018 1:07 AM
    Wednesday, December 12, 2018 1:07 AM
  • I found that I did have SPN problems, and I used the Kerberos Configuration Manager for SQL Server.

    Then I noticed the delegation tab in the Kerberos Configuration Manager for SQL Server, and it reported that the SQL Server service account was flagged as "Do not trust for delegation in Active Directory." I had my system admin set it, temporarily, to Full Delegation. 

    Hi Dan. Yes, this is exactly what I was talking about in Item #1 (in both numbered lists, actually). The reason that it didn't work until you used a SQL Server Login is that CREATE ASSEMBLY is impersonating the caller's Windows Login, and you didn't enable delegation for your Windows Login, just the Login being used as the service account. But when the caller is a SQL Server Login, then there is no Windows Login to impersonate, so the process uses the service account (which is the one here that was enabled for delegation).

    Glad you got it working. Take care, Solomon..



    • Edited by Solomon Rutzky Wednesday, December 12, 2018 1:25 AM added explanation.
    • Proposed as answer by Solomon Rutzky Wednesday, December 12, 2018 1:26 AM
    • Unproposed as answer by JediSQL Wednesday, December 12, 2018 2:44 AM
    Wednesday, December 12, 2018 1:17 AM
  • 1. I'll try it with my delegation enabled.

    2. I guess I missed a detail. At the time that I was able to create the assembly with the SQL Server login, the service account had been switched back to "do not trust for delegation."


    Dan Jameson
    Associate Director of IT/DBA
    Children's Oncology Group
    www.ChildrensOncologyGroup.org

    Wednesday, December 12, 2018 2:46 AM
  • Solomon,

    I tried to have my sysadmin set my AD user account to have "Trust this user for delegation to any service (Kerberos only)," but there was no Delegation tab. I Web searched for "active directory user delegation tab," and I found this:

    https://blogs.msdn.microsoft.com/mattlind/2010/01/13/delegation-tab-in-aduc-not-available-until-a-spn-is-set/

    Do you know how we might change my user account's delegation permissions without making my a server principal?


    Dan Jameson
    Associate Director of IT/DBA
    Children's Oncology Group
    www.ChildrensOncologyGroup.org

    Wednesday, December 12, 2018 6:40 PM
  • BTW, I tried the SQL 2014 on Win 2008 R2 machine logged in as my AD account, and I get the permission denied error.

    Dan Jameson
    Associate Director of IT/DBA
    Children's Oncology Group
    www.ChildrensOncologyGroup.org

    Wednesday, December 12, 2018 6:44 PM
  • I've reading more about this "delegation" thing. I found this article:

    https://blogs.msdn.microsoft.com/sqlupdates/2014/12/05/sql-server-kerberos-and-spn-quick-reference/

    The gist I get from it is that when the SQL Server service account has been given an SPN and granted delegation privilege, a service running under it has been granted the power to use my login to connect to network resources. Thus, that is why my AD user account does not have a delegation tab. Delegation is something an account can be given permission to do with other accounts, not a permission to have something done to it.

    Maybe after granting the service account delegation, I needed to reboot all three computers (which I did not do), my workstation, the SQL Server computer, and the file server to get the delegation to work. At this point, it is a moot point - I'll just be using a SQL Server login to deploy stuff as I always have done. I just got caught off guard by this test server that I was connecting to by Windows authentication.


    Dan Jameson
    Associate Director of IT/DBA
    Children's Oncology Group
    www.ChildrensOncologyGroup.org

    Wednesday, December 12, 2018 9:43 PM