Bulk Load SQL 2005 Windows Authentication problem

Question

Bulk Load SQL 2005 Windows Authentication problem

reto

SSC Enthusiast

Points: 138
More actions
November 8, 2007 at 2:05 am

#376751

Hi all
Bulk Load does not work after change from SQL Server 2000 to 2005.
Error:
Cannot bulk load because the file "\\WXPPRO-xxx98\C_Drive\SPAImport.txt" could not be opened. Operating system error code 5(Access is denied.).
If the file is local on the sql server 2005 it works, but that's not what I need.
There are many threads on this but no solution seems to be available.
SP 2 and hotfixes are applyed with no success.
Thanks for any help
reto

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

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

Vidhya Sagar SSCrazy Eights Points: 8216 More actions · Answer 1

Hi,

Provide necessary privilege for SQL service startup account to the network path "\\WXPPRO-xxx98\C_Drive\SPAImport.txt" and then try Bulk Insert.

Regards..Vidhya Sagar
SQL-Articles

reto SSC Enthusiast Points: 138 More actions · Answer 2

Hi Vidhya Sagar

thanks for your replay.

The SQL Server service account is a Windows Domain Administrator account and has all the needed privileges.

If I'm logged in on the sql server with the service account, I'm able to open the file form the client machine.

Looking forward to find a solution...

Have a great day.

Regards

Reto

rajanvarghese_mvk Grasshopper Points: 16 More actions · Answer 3

It is because that the sql server running account and the client running account privileges problem. To easily overcome this, create same user in both server and client machine with same password. In the Server machine change the sql server running account to this newly created account. Remember to add an administrative previlege to the newly created user account. To change the sql server running account go to services->right click mssqlserver --> properties. It will solve your problem.

😀

regards,

Rajan Varghese

reto SSC Enthusiast Points: 138 More actions · Answer 4

Hi Rajan Varghese

thanks for the answer.

It does not help, our mssqlserver 2005 service account uses a Windows domain account with admin privileges. For test, I have setup this account also as local administrator on the client machine.

Still the same problem...

Regards

reto

steve.moore Newbie Points: 9 More actions · Answer 5

Is there still no solution? I have exactly the same problem.

Minaz Amin SSChampion Points: 11052 More actions · Answer 6

The error access denied clearly states that permission issue on the \\WXPPRO-xxx98\C_Drive\SPAImport.txt. Check with your sysadmin person.

"More Green More Oxygen !! Plant a tree today"

Joe Clifford SSCrazy Eights Points: 8739 More actions · Answer 7

Are you trying to do this with a scheduled job? If yes, you need to take a look at the SQL Server Agent account not the SQL Server account... you may also want to take a look and see if any proxies have been set up for the Agent or xp_cmdshell...

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 8

If the SQL Server user is logged in using Windows Authentication, the user can read only the files accessible to the user account, independent of the security profile of the SQL Server process.

To resolve this error, use SQL Server Authentication and specify a SQL Server login that uses the security profile of the SQL Server process account, or configure Windows to enable security account delegation.

--Ramesh

reto SSC Enthusiast Points: 138 More actions · Answer 9

Hi Joe and Ramesh

if I use a SQL server account with the needed privileges it works fine!

So there is a problem using Windows Accounts.

Ramesh, could you please explain how to enable security account delegation.

Do I have to do this on the SQL Server or at the Domain Server for each account?

Thanks a lot for your help.

Reto

Programming is just a small world of unknown unboundedness..

so keep on happy programming!

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 10

You need to create a credential of a windows account having the required privileges and then you have to map this credential to the sql server login account.

--Ramesh

reto SSC Enthusiast Points: 138 More actions · Answer 11

Hi Ramesh

Thanks a lot for your fast answer.

I will try to do this today.

Regards

Reto