• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 642
  • Last Modified:

Windows Security and SSIS packages

I am trying to create a bulk insert task in SQL Server 2005 (MS visual studio 2005).
I create the package fine but when I execute it I get the following error:

[Bulk Insert Task] Error: An error occurred with the following error message: "Cannot bulk load because the file "C:\data\massaged.csv" could not be opened. Operating system error code 1346(Either a required impersonation level was not provided, or the provided impersonation level is invalid.).".

I have my database conection set for Windows auth and I can login to the SQL Server ok.
I have added my windows login to the C: drive with full control.
I tried running the bulk insert query from the following with same error.
     a) SQLCmd and get the same error.
     b) Studio manager

How do I check which login is being used to run the task and which to give access too. (running on Windows server 2003 standard sp2
I can open the .csv file and save changes to it ok so why can't I open it from the
0
Bitadmin
Asked:
Bitadmin
  • 2
  • 2
1 Solution
 
BitadminAuthor Commented:
Oh I forgot I also tried to do it with the import wizard in MS Visual Studio
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> Cannot bulk load because the file "C:\data\massaged.csv" could not be opened. Operating system error code 1346(Either a required impersonation level was not provided, or the provided impersonation level is invalid.)

This means that the SQL Server startup account doesn't have privileges to access the folder C:\data\.
Change SQL Server startup account to Local System or a domain account and make sure that the domain account has privilege to access that folder.

Also make sure that the login under which that package is being executed has privileges to that folder.
And the csv should not be opened up by some others while the package is being run.
0
 
Alpesh PatelAssistant ConsultantCommented:
Please make sure current user has permission to the .CSV File location for Read  if you are executing using BIDS.

Other than this executing from SQL Server then please give folder permission to SQL Agent user.
0
 
BitadminAuthor Commented:
Sorry it has taken me a while to post on this. I have verfied the following:

SQL Agent running under builtin Local system acct.
SQLService running under a domain account ( which is in the local sysadmin group)
SQL Integration running under builtin network service acct.
I am logged with a domain acct (with local admin permission)  

I want to run the bulk insert from BIDS as part of a package; However I have tried to run it from SQL Management Studio as a query with the same error message.

I have verified that the folder and the file that I am specifiying in the bulk insert statement has my domain account in it w/full control and I can open the file and save or change as I see fit. This is crazy.  I am rdp into the computer so not sure if that has anything to do with it.  I could really use some more input.  Thanks

The error message again is:

Msg 4861, Level 16, State 1, Line 2
Cannot bulk load because the file "C:\data\massaged.csv" could not be opened. Operating system error code 1346(Either a required impersonation level was not provided, or the provided impersonation level is invalid.).
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> Operating system error code 1346(Either a required impersonation level was not provided, or the provided impersonation level is invalid.)

Then it means that the impersonation level was not done properly.
Just make sure that the procedure / script is run under your domain account to confirm that.
Open profiler trace and check under which account the script is being actually executed and make sure that the account have permissions to open that C:\data\massaged.csv file.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now