BCP Cannot access a network share
-
I have SQL Server 2014 (Enterprise) on Server A. The service runs under DomainA\Admin.
The Client machine is B, User credentials DomainB\User.
DomainB\User has a share on B that contains a BCP data file. DomainA\Admin has full access to this file.
If I log onto A (the server machine) with either DomainA\Admin or DomainB\User credentials, and run SQL Server Management Studio with Windows Authentication I can run BCP sucessfully using the following:
BULK INSERT [MyTable] from '\\B\share\datafle.tsv' WITH ( KEEPNULLS , KEEPIDENTITY )However, if I log onto B, (the client machine and the machine hosting the share) and try to run the same bcp command, I get "Access is Denied".
What's going on?
Thanks in advance
-
Ok, so for starters, you keep saying BCP and this is not BCP. BCP is a separate cmdline program. You're using Bulk Insert in tsql. Just wanted to be clear.
So clearly what's happening here is that no matter what you think or assume, the acct doesn't have access to the folder. It's really as simple as that.
Check that you're using the acct you think you are and make sure it has rights.
I think you may find that it's the SQL service acct that needs the rights. But something's clearly not getting handed off properly at the OS level. So either it's a trust issue, or simple folder perms.
These issues can be tricky to pin down, but I don't hear any evidence that anything's being hidden from you.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
Thanks for the reply and sorry for the confusion over BCP.
I'm really quite sure its not as simple as file share permissions. I've been looking at this all afternoon my assertion is supported by the fact that if I log into the Server machine and run the BULK INsert from there, everything works fine. This surely indicates that the DB Server is able to access the shares and so there are no (simple) file permission issues.
The only time I see the problem is when the client is NOT on the Server.
-
Ron.James (1/5/2015)
Thanks for the reply and sorry for the confusion over BCP.I'm really quite sure its not as simple as file share permissions. I've been looking at this all afternoon my assertion is supported by the fact that if I log into the Server machine and run the BULK INsert from there, everything works fine. This surely indicates that the DB Server is able to access the shares and so there are no (simple) file permission issues.
The only time I see the problem is when the client is NOT on the Server.
It's most likely the infamous Kerberos Double-Hop "problem". I have "problem" in quotes because I don't think of it as a problem. Rather, I think of it as an extra layer of security.
The "problem" is that when you log into the server via a remote connection, like you normally do for an SSMS session, your credentials don't make it out to the remote server that you're trying to BULK INSERT from. That's the nature of BULK INSERT. When you RDC directly into the server, the problem goes away because you don't have the extra "hop" in there.
As a side bar, if you actually were using xp_CmdShell to call BCP, you won't have the same problem.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hmmm, why should my credentials need to make it out to the Server. The credentials that SQL Server is running under are already sufficient to read the file on the share?
Thanks
-
Ron.James (1/5/2015)
Hmmm, why should my credentials need to make it out to the Server. The credentials that SQL Server is running under are already sufficient to read the file on the share?Thanks
Because BULK INSERT doesn't use SQL Server's credentials unless you're using an SQL Server login instead of a Windows AD login... and you should always be using Windows AD logins for humans that connect to the server.
From BOL on the subject of permission for BULK INSERT...
[font="Arial Black"]Security Considerations[/font]
If a user uses a SQL Server login, the security profile of the SQL Server process account is used. In contrast, if a SQL Server user logs on by using Windows Authentication, the user can read only those files that can be accessed by the user account, regardless of the security profile of the SQL Server process.
For example, consider a user who logged in to an instance of SQL Server by using Windows Authentication. For the user to be able to use BULK INSERT or OPENROWSET to import data from a data file into a SQL Server table, the user account requires read access to the data file. With access to the data file, the user can import data from the file into a table even if the SQL Server process does not have permission to access the file. The user does not have to grant file-access permission to the SQL Server process.
SQL Server and Microsoft Windows can be configured to enable an instance of SQL Server to connect to another instance of SQL Server by forwarding the credentials of an authenticated Windows user. This arrangement is known as impersonation or delegation. Understanding how SQL Server 2005 and later versions handle security for user impersonation is important when you use BULK INSERT or OPENROWSET. User impersonation allows the data file to reside on a different computer than either the SQL Server process or the user. For example, if a user on Computer_A has access to a data file on Computer_B, and the delegation of credentials has been set appropriately, the user can connect to an instance of SQL Server that is running on Computer_C, access the data file on Computer_B, and bulk import data from that file into a table on Computer_C. For more information, see Understanding Impersonation.
Note:
The way in which SQL Server 2005 and later versions control access to files addresses a security issue that was present in Microsoft SQL Server 2000 and earlier versions. Formerly, after a user was authenticated, access to external files was based on the security profile of the SQL Server process. When the SQL Server process had read access to a file, for a user that did not have access to the file but was a member of the bulkadmin fixed server role, the user could import the file by using BULK INSERT and access the contents of the file.
The only time I allow such "impersonation" is within a stored procedure. It makes life simpler for me and denies potential hackers that manage to get into one box from having privs to another (unless they get in as someone with "SA" privs... then you're mostly dead).
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply