SQL Server Permissions – Service Permissions on Network Share Depending on IntegratedSecurity

openrowsetpermissionssql serversql-server-2005

Our company receives a daily file from one of our customers. It is an EDIFACT file translated to XML and downloaded from an SFTP server.

We have developed one (c# console) application that:

  1. Download the file.
  2. Upload it to one SQL Server (2005) using an stored procedure.
  3. Deletes this file from network share.
  4. Runs another stored procedure to integrate it with our sales order's tables.

SQL-Server & SQL-Server agent service initiates as LocalSystem account. (It will be replaced soon and I'd prefer to let it as is, I know it is not configured properly.)

The procedure to store the file in our SQL-Server is similar to this one: 

CREATE PROCEDURE [dbo].[sp_ProviderName_Download]
(
    -- FQN of XML file
    @file_name nvarchar(260)
)
AS
BEGIN

    DECLARE @xml_data XML,
            @cmd nvarchar(max);

    BEGIN TRY

        SET @cmd = 'SELECT @xmlText = BulkColumn FROM OPENROWSET(BULK '
                    + '''' + @file_name + ''''
                    + ', SINGLE_BLOB) x;';

        EXEC sp_executesql @cmd, N'@xmlText XML OUTPUT', @xmlText = @xml_data OUTPUT;

        INSERT INTO [dbo].[ProviderXML_Register] (..., xml_data)
        VALUES (..., @xml_data);

        RETURN 0;

    END TRY
    BEGIN CATCH

        EXEC [dbo].[vsp_error_handler];
        RETURN -1;

    END CATCH

END

The issue:

Stored procedure works depending on how we set the ConnectionString in our C# application.

If it uses IntegratedSecurity=false:

this.scsb = new SqlConnectionStringBuilder();
this.scsb.ApplicationName = "ProviderXML_Download";
this.scsb.WorkstationID = Environment.MachineName;
this.scsb.DataSource = "xxx.xxx.xxx.xxx";
this.scsb.InitialCatalog = "my_db";
this.scsb.IntegratedSecurity = false;
this.scsb.Password = "*******";
this.scsb.UserID = "SqlServerLogin";

It works properly without errors.

But if it connects using IntegratedSecurity=true (AD user)

this.scsb = new SqlConnectionStringBuilder();
this.scsb.ApplicationName = "ProviderXML_Download";
this.scsb.WorkstationID = Environment.MachineName;
this.scsb.DataSource = "SQL_INSTANCE_NAME";
this.scsb.InitialCatalog = "my_db";
this.scsb.IntegratedSecurity = true;

it fails with error:

Operating system error 5 Access denied.

AD user has MODIFY rights on the network share. And as far as I know, 'LocalSystem' account shouldn't be able to access to the network share.

  • Why I'm getting this error when IntegratedSecurity=true?

Best Answer

The problem is due to how security is handled for bulk operations and the default limitations of Impersonation.

When connecting with a SQL Server Login, there is no external Windows SID / security context, so SQL Server uses the security context of the service account when needing to reach outside of SQL Server (i.e. interact with the OS, network, etc.). So, if the SQL Server service account is the Local System account, then that is the account used for external access. Since your file is on the network, it would have to be that "Local System" has access to that network share. This is possible by giving the server itself access (by specifying <domain_name>\<computer_name>$ for the NTFS permissions). In this case, the SQL Server process is not using Impersonation: the external access is being made in the security context of the process itself.

When connecting to SQL Server using a Windows Login (whether AD account or local account on the server), there is an SID to use, and SQL Server will use that SID (Security ID that points to the Windows / AD account) for external access. This is what happens when connecting with "Integrated Security" / "Trusted_Connection" = true. Now, using the SID of the account logging into SQL Server means that SQL Server is using Impersonation to make it's network request appear to be coming from that other SID instead of from "Local System" (remember, the service account owns the sqlservr.exe process, and it is that process, not the SID logging into SQL Server, that is making the network request). Impersonation, by default, is quarantined to the machine in which the process was started (similar to how using Impersonation via EXECUTE AS user is, by default, quarantined to the Database). So, the BULK INSERT / OPENROWSET(BULK... commands should be able to see files locally on the server that SQL Server is running on, but network shares are not local, and the impersonated security context, by default, does not extend that far. What you are seeing here should be the same thing as the infamous "double-hop" issue.

So, you can do one of the following:

  1. give permission on that network share to EVERYONE

  2. continue logging in via the SQL Server Login and not worry about using Integrated Security / Trusted Connection.

  3. Enable "Delegation", though I am not sure if that will work on a Local System account (you mentioned SQL Server Agent running as "Local System", so this is what would be connecting to SQL Server). It might require setting up an AD account and enabling that account for "Delegation" so that it can go beyond the local server. Either way, "Delegation" is how one configures AD to allow an account (maybe even an account / service combination) permission to go beyond the default quarantine.

    P.S. @StrayCatDBA mentioned that using the Network Service account (i.e. NT AUTHORITY\NETWORK SERVICE allows for Delegation. Also, please see the "Configure Windows Service Accounts and Permissions" link at the bottom as that documentation goes over the various options you have.

Some info here:

Related Question