Resolve Bulk Load Error While Using SQL Server Authentication In Windows Application

I could not bulk load because the file "C:\Users\XXXX\YYYY\test.txt" could not be opened. Operating system error code 5(Access is denied.)

It is not recommended by Microsoft to use SQL Server and Active Directories in same Windows Server. But in small enterprises and startups they used all in one server.

When facing this kind of error, initially check the permissions of the specific SQL user.

1. In the SQL Management Studio, in the left pane go to the security tab, click login and select the required login and get properties by right clicking on it.
   
   
   
   
2. After that Login Properties window appears, in that window select Server Roles option and ensure that bulkadmin, diskadmin and public options are checked and click OK.
   
   

In that Bulk Load Process, what happens is when the client accesses the files through bulk load command, it initially goes to the SQL Server and then through the SQL server it accesses the files. The process is explained here:



So it is necessary for the SQL Server has the permission to access the files in that particular machine. Even though that particular user has its own window username and password. We have to add permission for SQL user in the particular file/folder permissions. Here are the steps:

1. Navigate to that particular folder to which you are going to give access rights.
   
   
2. Right click on that particular folder and select the properties and go to the security tab in the properties window and click on the edit button.
   
   
3. In the Select Users, Computer, Service Account, or Groups dialog box, click Locations, at the top of the location list, select your computer name, and then click OK.
   
   
   
   
4. In the “Enter Object Name to Select Box” use NT SERVICE\MSSQLSERVER for a default instance, or NT SERVICE\MSSQL$InstanceName for a named instance.).
   
   
5. Click Check Names to validate the entry. The validation often fails, and might advise you that the name was not found. When you click OK, a Multiple Names Found dialog box appears.
   
   
6. Now Select either MSSQLSERVER or NTSERVICE/ MSSQLSERVER$InstanceName.
   
   
7. Click OK again to return to the Permissions dialog box.
   
   
8. In the Group or user names box, select the per-service SID, and then in the Permissions for <name> box, select the Allow check box for full control.
   
   
9. Click Apply, and then click OK twice to exit.

Now you can use the bulk load command to access the files and folders from your database through SQL Authentication.