Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Bulk Load Permission Issue

Author  Topic 

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-11-11 : 13:33:48
Hi All,
I am trying to bulk load a text file on a shared drive into sql server.
However this executes successfully,when I run the code on sql server.
But,fails from client machine SSMS(error code 5:cannot open file).
The Sql service account has read permissions on the shared folder.
The folder has permissions set to everyone read.

please help

Javeed Ahmed
https://www.linkedin.com/pub/javeed-ahmed/25/5b/95

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-11 : 14:04:20
Does your ssms user account have permission to read the file?
Go to Top of Page

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-11-11 : 14:05:38
yes,it does.
Its a domain account

Javeed Ahmed
https://www.linkedin.com/pub/javeed-ahmed/25/5b/95
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-11 : 14:49:30
Try adding the machine account to the folder's permissions. machinename$ is the format.

I recall needing to do something similar last year, but my memory is fuzzy so I could be wrong on the machine account.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-11-11 : 15:05:01
i was searching on the net and found that it might have something to do with sql service account delegation on the AD.
any ideas on this?


Javeed Ahmed
https://www.linkedin.com/pub/javeed-ahmed/25/5b/95
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-11 : 15:18:31
quote:
Originally posted by ahmeds08

i was searching on the net and found that it might have something to do with sql service account delegation on the AD.
any ideas on this?


Javeed Ahmed
https://www.linkedin.com/pub/javeed-ahmed/25/5b/95



Show us the article.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-11-11 : 15:23:14
http://blogs.msdn.com/b/dataaccesstechnologies/archive/2010/10/29/sql-bulk-copy-error-operating-system-error-code-5-access-is-denied.aspx

Javeed Ahmed
https://www.linkedin.com/pub/javeed-ahmed/25/5b/95
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-11 : 15:28:56
You need to check the SPNs. Work with your network/server admin on this. SETSPN -L and add the SQL Server service account. You'll need to check the SQL Server SPNs as well as the file share server's SPN.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-11-11 : 15:31:26
quote:
Originally posted by tkizer

You need to check the SPNs. Work with your network/server admin on this. SETSPN -L and add the SQL Server service account. You'll need to check the SQL Server SPNs as well as the file share server's SPN.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/



Thanks Tara,
I will start working on this.

Javeed Ahmed
https://www.linkedin.com/pub/javeed-ahmed/25/5b/95
Go to Top of Page
   

- Advertisement -