2

I have a setup with 2 machines (client on win2012r2, sql server on win2016) which are both not part of a domain. The client machine has a share, accessible to Everyone. I'm using SQL Server authentication. I'm able to browse the share from the SQL server

I have an application which attempt to do a BULK INSERT, but it's complaining that the bulk insert encountered an error code 1326 (username or password incorrect) when accessing the share.

This does not make sense to me; there is no password required to access the files, so how can it be incorrect? I've also tried to configure the application to upload to a share on the SQL Server, and do the bulk insert using a local path, but that triggers an access denied during the BULK INSERT.

Koert
  • 133
  • 1
  • 5
  • 2
    It is very likely that your SQL instance is using the computer account to access the file, not your user account. Try adding "domain computers" to the NTFS permissions on the folder (not the share settings) and see if it helps. – Tim Brigham Mar 24 '17 at 19:29
  • 2
    Since you are using SQL Auth, it is going to use the SQL Server service account... but the servers don't belong to a domain, so most likely a virtual account is being used. this will cause the authentication across the network to use the computer account to authenticate. – Sean Gallardy - Mostly Retired Mar 24 '17 at 23:00

2 Answers2

2

What Sean and Tim said in their comments. If your SQL Server, which I'll call SQLServer2016, is running as local system, it's accessing the share as the machine account, which I'll call $SQLServer2016.

Your share is available to EVERYONE. EVERYONE is a built-in group that includes Authenticated Users (another built-in group that includes everyone with an account on that machine) and Guest. Guest is a built-in account for people who don't have an account on the machine that houses your share, which I'll call Fileserver2012R2.

SQLServer2016's system account, $SQLServer2016, is, by definition, not a valid Fileserver2012R2 account. SQLServer2016 would access the share on Fileserver2012R2 via the Guest account.

My guess would be that Guest is disabled on Fileserver2012R2.

Katherine Villyard
  • 18,510
  • 4
  • 36
  • 59
  • 1
    That was exactly what happened. Thank you for explaining how the EVERYONE group works; not only is my problem solved, but TIL. – Koert Mar 27 '17 at 06:54
0

I have had this problem several times during BCP's. I usually end up putting the file in the C:\ root of the drive (on the sql server machine) and it works every-time, even if I am running the bCP on a remote machine. It may have to do with the fact the BCP utility is running under a different user or account. I have had many problems reading and writing from shared network folders in recent versions of windows.

chongo2002
  • 15
  • 8