Calling stored procedure to bulk import from SSIS causes access denied

Question

Calling stored procedure to bulk import from SSIS causes access denied

markdummen

Old Hand

Points: 334
More actions
October 3, 2022 at 11:55 am

#4094803
Hi,
I'm working on building a data validation engine, which is supposed to check a number of items, such as existence of fields in CSV files before they are imported by SSIS. This is done with the bulk import command via a stored procedure.
What I'm trying to do is the following
1. [SSIS] Gather filenames *.csv in network folder (Works)
2. [SSIS] Call the stored procedure EXEC Dval.sp_datavaliation "Testset_BBS_Orders", ?, ? output.
  The first ? holds the filename that was gathered in step 1.
  Calling the stored procedure works, the execution does not.
  The code that is being called is below, under "----- What is being executed"
3. <Update> : Note that running the script ON the database server itself works (direct called procedure). When running the script from ANOTHER server, also running sql management service, the error is the same as below
4. The following error is returned, while executing inside Visual Basic / SSIS
  SSIS package "D:\~snip~\ETL_ValidationEngine_Import_Detail_BBS_SO.dtsx" starting.
  Information: 0x40016042 at ETL_ValidationEngine_Import_Detail_BBS_SO: The package is attempting to configure from the parent variable "ETLRunID".
  Warning: 0x8001201A at ETL_ValidationEngine_Import_Detail_BBS_SO: Configuration from a parent variable "ETLRunID" did not occur because there was no parent variable collection.
  Information: 0x40016042 at ETL_ValidationEngine_Import_Detail_BBS_SO: The package is attempting to configure from the parent variable "PackageName".
  Error: 0xC002F210 at CF_Validation, Execute SQL Task: Executing the query "EXEC DVal.sp_datavalidation 'Test_BBS_Orders', ?, ..." failed with the following error: "Cannot bulk load because the file "\\ce\~snip~\Sales\BI-BB200202.csv" could not be opened. Operating system error code 5(Access is denied.).". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
  Task failed: CF_Validation
---- Question
When I try executing the stored procedure directly from management studio, it executes fine.
When I try calling the stored procedure from SSIS it does not work and I get the error above.
I do not understand why. Please help!
---- What I tried
1. I've read somewhere that the combination SSIS > Stored procedure and Bulk import may give problems when the SQL server is not using a dedicated server account.
I've setup a new domain service account, which is now running the SQL Server, SQL Server Agent, SQL Server CEIP service and SQL Server Integration Services. The account has only been given extensive database rights and has no further network rights

----- What is being executed (Background)
The stored procedure itself is indeed trying to execute a bulk load, because that can be executed without knowing fieldnames.
1. Get FirstRow via BulkLoad
2. Get Fieldnames, based on the first line and the | (Pipe) token.
3. Do validations on field names, field order etc.
```
BULK INSERT MetaData.Dval.T100_202210031300571390460_TEST_BBS_ORDERS_COUNT FROM '\\ce\~snip~\in\Orders\BI-BB200202.csv' WITH
(
ROWTERMINATOR = '0x0A'
,FIRSTROW=1
,LASTROW=1
)
```
This results in :
select @outputFromExec = (LEN(BIGFIELD) - LEN(REPLACE(BIGFIELD, '|', ''))) from MetaData.Dval.T100_202210031300571390460_TEST_BBS_ORDERS_COUNT
fieldcount is : 23
headerrow is : SVA-RF|SVNURF|PDPDFC03|IDLINE|PDCDCL|CLNOMB|CLTPCL|CLCDPA|PDCLRF|CLNOMB01|CLTPCL01|CLCDPA01|SVFAM|SVCDGN|SVCDES|SVCDPR|SVCDVR|VRDESC|PEENFC03|PDREPR|SVTTPN01|MNCDMA|SVIMMO01|SVIMEU01
- This topic was modified 2 years, 2 months ago by markdummen.
October 6, 2022 at 9:36 am

This was removed by the editor as SPAM

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246988 More actions · Answer 1

I do not know the answer to your question, but I do have a suggestion. Rather than calling a stored proc to do the column-name checks, keep things inside SSIS by calling a Script Task instead. The following code reads the first row of a CSV file

string line1 = File.ReadLines("SomeFile.csv").First();

Once you have that, go ahead and push the column headers into an array using String.Split.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

markdummen Old Hand Points: 334 More actions · Answer 2

Hi @Phil, that's an interesting tip. I will do a test with that. But I am trying to keep everything in 1 script task though, because we wish to implement the solution on all our import tasks in the same way. This might make it (slightly) more labour intensive to implement across the entire datawarehouse

I've also done some additional testing via management server directly.

On the SQL Server (Machine), when I run the stored procedure, all runs correctly.

On another machine (my own), when I run the stored procedure via management studio, even management studio gives an error on running the identical script call

DECLARE @CASESET_RETURN_VALUE VARCHAR(100)
EXEC [DVal].[sp_datavalidation] 'Test_BBS_Orders', '\\ce\~snip~\200202.csv', @CASESET_RETURN_VALUE OUTPUT
PRINT CONCAT('RETURN VALUE:',@CASESET_RETURN_VALUE)

Leading to

BULK INSERT MetaData.Dval.T100_202210031438232613232_TEST_BBS_ORDERS_COUNT FROM '\\ce\~snip~\200202.csv' WITH
(
ROWTERMINATOR = '0x0A'
,FIRSTROW=1
,LASTROW=1
)

Msg 4861, Level 16, State 1, Line 12

Cannot bulk load because the file "\\ce\~snip~\200202.csv" could not be opened. Operating system error code 5(Access is denied.).

This reply was modified 2 years, 2 months ago by markdummen.

frederico_fonseca SSCoach Points: 16167 More actions · Answer 3

SQL Server Agent job account needs access to the share/file - that is where it is failing now.

Or the Proxy account used to run SSIS packages if you defined one for that.

when running through another server (e.g. you login in SSMS to a server and then access the file share) your credentials (assuming you use windows authentication) are passed to the share - in order to work correctly the SQL Server should have SPN's setup and the server must allow kerberos credential delegation if not mistaken. On this case YOUR account also needs access to the share and file.

markdummen Old Hand Points: 334 More actions · Answer 4

Hello @Frederico,

On both systems (the sql server and my own system) I'm logged in under the same (my) account. On both machines I have access to the folder and can see the file that is supposed to be imported.
We have, just now, increased the sql agents access to include access to the folder, but the access denied notice remains when running from another system than the server itself.
I've also tried logging in under that service account on the SQL server to verify access to the folder. The service account does see the file & folder correctly. When logging ths service account in on the development server, the access denied notice remains.

This reply was modified 2 years, 2 months ago by markdummen.

Justin Terry Grasshopper Points: 18 More actions · Answer 5

Hi @markdummen,

I think the key may be point 3. in your op: -

<Update> : Note that running the script ON the database server itself works (direct called procedure). When running the script from ANOTHER server, also running sql management service, the error is the same as below

You have the SQL services executing as a domain account. What may be missing is a service account AD delegation right to the server that hosts the file share \\ce. These need to be in place for this to work: -

SPNs (Service Principal Names) registered in AD for the SQL service account.
Delegation enabled in AD on the SQL service account to the 'cifs' service on the 'ce' server.
The executing domain account (in SSMS) needs to be enabled for delegation. Most standard AD users are enabled, but there is an AD account setting 'Account is sensitive and cannot be delegated' that must be unchecked.

In the successful case, where you execute from SSMS on the database server itself, there is no delegation. You are reaching out to the file share having already authenticated locally on the SQL server. In the case where you run SSMS remotely, the SQL service requires the right to delegate your remote login to the file server.

Please ask if you need help with some examples of how to set this up. It does depend somewhat on details of your environment and your local security requirements/policy.

markdummen Old Hand Points: 334 More actions · Answer 6

Hello Justin; Sorry for the delay. Some corona got in the way.

In Active Directory, while selecting the active directory machine, we've enabled:

- Trust this computer for delegation to any service (Kerberos only)

<Use Kerberos only>.

In Active Directory, under the user properties for the service account

- The do not trust this account for delegation tag has been unchecked.

Concerning:

SPNs (Service Principal Names) registered in AD for the SQL service account.
Delegation enabled in AD on the SQL service account to the 'cifs' service on the 'ce' server.

Could you make you reply a bit more idiot proof; I do not know what SPN's you mean here. Where/How would I be able to check this?
What are 'cifs' and the 'ce' server. What do these abbreviations mean? Google does not tell me

Thanks for your help! It feels like you will be able to fix this 🙂

This reply was modified 2 years, 2 months ago by markdummen.

Justin Terry Grasshopper Points: 18 More actions · Answer 7

Hi @markdrummen,

In Active Directory, while selecting the active directory machine, we've enabled:
- Trust this computer for delegation to any service (Kerberos only)
<Use Kerberos only>.
In Active Directory, under the user properties for the service account
- The do not trust this account for delegation tag has been unchecked.

Neither of the above changes should be necessary if you are running SQL services under a domain account. I'd recommend that you undo those changes, at least initially.

The first move is to register the required SQL SPNs on that domain SQL Service account. There is documentation from Microsoft here: Register a Service Principal Name for Kerberos Connections

You'll see there are various ways to do the SPN registration. The right way depends on whether this is a default or named SQL instance. If it is a named instance on a dynamic TCP port then the only safe way is to enable the service to register its own SPN. I have always worked with fixed ports and manually registered SPNs. The documentation section Manual SPN Registration gives example setspn commands to execute.

Once the SPNs are registered, you should see that connections to SQL from a remote machine as a domain account are using Kerberos authentication. Microsoft give the query required in the introduction:

SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@spid ;

If you can get to a state where Kerberos is authentication is working, then we can look at what delegated permissions are required.

markdummen Old Hand Points: 334 More actions · Answer 8

Hello @justin,

I've been working with our IT support to walk through the steps of SPN activation. We followed the steps, but I'm not getting the required results. Please review below

A screenshot of our management studio & Services. Perhaps I'm running the services wrong?
A screenshot of the manual SPN efforts we took. We also did the automatic steps.
A screenshot of the result of the SQL query. As far as I understand I should be seeing Kerberos here, but I'm still seeing NTLM

OURSETUP

Above: Management studio showing OurMachine02 and the DEV setup. Showing 2 databases.

The service account OURDOMAIN\SVC_ACCOUNT has been connected to a number of services

Are there any mistakes here?

COMMANDPROMPT

Above: We've tried a number of manual SPN items. (Always removing the earlier attempt with -D). Have we done it wrong here ?

NLTM

I'm pretty clueless on a next step to take. Where did we go wrong?

Thanks again for your expertise.

Justin Terry Grasshopper Points: 18 More actions · Answer 9

Hi @markdummen,

From the info you posted there are some points to address: -

The SPN service names you are using. This prefix designates the service type only, so it is fixed and always MSSQLSvc for an SQL database service. It's the FQDN and port number/instance name that specify the particular instance of the MSSQLSvc.
Your SQL OURMACHINE02\DEV is a named instance. In order to reliably pre-register an SPN, you will need to fix the port that the named instance listens on. This can be done through SQL Server Configuration Manager. By default a named instance will grab a dynamic port at start-up and the port can change at future service restarts. This could invalidate the pre-registered SPN.
The sys.dm_exec_connections result is from SSMS on the OURMACHINE02 server (it's using Shared Memory transport). Within the local machine you will still see NTLM connections. From a remote SSMS you should see Kerberos, once the SPNs are registered correctly.

From the info you have given these are the setspn commands. You will need to address the <fixed_tcp_port>. If Kerberos doesn't work immediately from a remote SSMS, it's worth allowing a while for background replication and refresh. My experience has varied from immediate up to an hour wait for Kerberos to kick in.

:: Register SPNs for the OURMACHINE02\DEV SQL server instance. Todo: <fixed_tcp_port>.
setspn -S MSSQLSvc/ourmachine02.domain.local:<fixed_tcp_port> -U OURDOMAIN\SVC_ACCOUNT
setspn -S MSSQLSvc/ourmachine02.domain.local:DEV -U OURDOMAIN\SVC_ACCOUNT

:: List all registered SPNs for the service account.
setspn -L -U OURDOMAIN\SVC_ACCOUNT