SQL server bulkinsert errors

Question

When I run this query to bulkinsert a file on a shared drive to SQL server 2008 with username and password (not Windows authentication), I get these errors. DBA, system admins and network guys are all denying these errors are related to their teams and I am lost... Can anyone please help me to identify where the issue is? When I run bulkinsert with database username and password, what authentication does SQL server use to open the file?

Run this on MS Management Studio

BULK INSERT DatabaseName.dbo.TableName
FROM '\\shared_server\parent\child\file_name.txt' 
WITH(FIRE_TRIGGERS, DATAFILETYPE='char', FIELDTERMINATOR='\t',ROWTERMINATOR='\n', FIRSTROW=2);

and I get

Cannot bulk load because the file  "\\shared_server\parent\child\file_name.txt" could not be opened. Operating system error code 5(Access is denied.).

Run this on python

import pyodbc

database = 'DatabaseName'
username = 'username'
password = 'password'
server = 'server_name'
failover = 'failover_server_name'
cnxn_string = 'DRIVER={SQL Server Native Client 10.0};SERVER=%s;FAILOVER_PARTNER=%s;DATABASE=%s;UID=%s;PWD=%s;CHARSET=UTF8' % (server, failover, database, username, password)
cnxn = pyodbc.connect(cnxn_string)
cursor = cnxn.cursor()

query = r"""
BULK INSERT Estimates.dbo.FundamentalsIS 
FROM '\\shared_server\parent\child\file_name.txt' 
WITH(FIRE_TRIGGERS, DATAFILETYPE='char', FIELDTERMINATOR='\t',ROWTERMINATOR='\n', FIRSTROW=2);
"""
cursor.execute(query)
cursor.commit()"

and I get

ProgrammingError: ('42000', '[42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot bulk load because the file "\\shared_server\parent\child\file_name.txt" could not be opened. Operating system error code 1326(Logon failure: unknown user name or bad password.). (4861) (SQLExecDirectW)')

score 1 · Accepted Answer · edited May 23 '17 at 10:26

Could the MS SQL server 2008 possibly be on a different security group (or have different settings) than the shared drives, where the file is located?

Because the bulk insert operation is run on the MS Management studio server side, it might not have access to the file, the 'access denied' leads me to believe DB server cannot get to shared file drive, and possibly does not have permission to access it. Likewise, even if using python to execute the BULK INSERT statement, the DB server still needs to have access to where ever the file is located.

I had a similar issue in the past, because the DB server could not get to the shared file, located elsewhere. My workaround was to use local computer to read in the file and run the insert queries using python. It sounds like the local environment has access to both and can be used as the central communication hub. You might have to do something similar to

Thanks for your response. Yes executemany of pyodbc is one workaround. For my case, it seems like bulkinsert with sql username/password uses corp\sqlexec, which is a dedicated bulkinsert account to access the file on the shared drive. Reviewing the permission for this profile. — E.K., Jun 04 '15 at 02:37

SQL server bulkinsert errors

1 Answers1