3

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?
  • 1
    BTW there's no reason to have SQL Server read the file. Just read it in C# and push it to the stored procedure. – David Browne - Microsoft Mar 3 '18 at 0:31
  • @DavidBrowne-Microsoft I know, if fact usually we process XML files using C# applications. But this time I wanted to learn how to save documents using T-SQL. Tanks for your comment. – McNets Mar 3 '18 at 15:57
  • You can still process the XML inside SQL. Just pass it to the stored procedure using a parameter of type xml, or run INSERT INTO [dbo].[ProviderXML_Register] (..., xml_data) VALUES (..., @xml_data); directly from C#. – David Browne - Microsoft Mar 3 '18 at 17:26
  • @McNets I just updated with some clarification regarding "Local System" and an additional link at the bottom to documentation regarding options for service accounts. – Solomon Rutzky Mar 7 '18 at 15:48
4

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:

  • Instead of Local System, use Network Service and delegation can work. You end up assigning permissions to the computer account of the server. – StrayCatDBA Mar 6 '18 at 22:40
  • @StrayCatDBA ah, yes, "network service", and there is one other "built in" account, is what I was thinking of when I mentioned giving permission to the computer itself. Still, I think the O.P. indicated towards the top of the question that they were to be replacing "Local System" soon with something else, probably a domain account? – Solomon Rutzky Mar 6 '18 at 22:44
  • @StrayCatDBA I just updated with a link to the documentation that goes over the various options for service accounts, including using NT AUTHORITY\NETWORK SERVICE :) – Solomon Rutzky Mar 7 '18 at 15:46

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.