I'm testing a setup where the shared bulk insert folder (called DBShare) isn't located on the SQL Server but on the NAM Server. The bulk.write points to C:\DBShare\ and bulk.read to \\NAM_SERVER_IP\DBShare\ .
However, bulk insert continues to fail and I'm getting this error message in the log:
Cannot bulk load because the file "\\NAM_Server_IP\DBShare\BulkInsertTest_0_0_1.txt" could not be opened. Operating system error code 5(Access is denied.).
I've granted full control on the DBShare folder to "Everyone" and to "VMUser" on the NAM Server side. I also can access DBShare and create subfolders from the SQL Server. I've set the user role on the SQL Server to "bulkadmin". I can't add NT Service\MSSQLSERVER as suggested by Frans (https://answers.dynatrace.com/answers/118237/view.html) though because I can't select any other location but the NAM Server and therefore can't grant NT SERVICE\MSSQLSERVER permission on this folder.
Any suggestions how to proceed further?
Thanks in advance!
Solved! Go to Solution.
Hi @Andre V.,
thanks for the reply. I know the usual setup from the documentation. However I've read in https://answers.dynatrace.com/comments/150708/view.html that this setup is also possible and that's exactly the deployment scenario I'm looking into for a customer.
When placing the bulk share on the NAM server a couple of things have to happen. It's easy enough for the NAM server to write the file - but the SQL server needs to be able to read it.
Permissions for SQL to access the share depend on how the SQL service is running. If it is running as a domain user - assign the domain user read rights to the share permissions AND the folder permissions.
If the SQL service is running as system - then grant the SQL Server "computer" read permissions on the share and folder. When you go to assign the permissions by default it is only for users and groups. Select the Object type button and select computers. Now you can grant the SQL Server computer access to the share and folder. Again, only read is needed.
Now - that being said - if the SQL server is using Kerberos authentication - this will not work. The only way I have ever enabled bulk in this scenario is when the share is on the SQL server
Hope that helps.
thanks for your suggestion. The SQL service runs as a system. Both SQL Server and NAM Server run on Windows Server 2012 and are part of the same workgroup.
However I can't see the "computer" object type when I edit permissions for the shared folder. After a quick research this seems to be a common issue in Windows Server 2012. Some users seem to have solved this by granting permission to "Everyone" but this doesn't work in my case.
edit: I've been able to read and create files in the shared folder from the SQL Server manually, which means the SQL Server must have some sort of access rights. It just doesn't work with bulk insert.
Just an update: we tested this setup in different environment. There it was possible to select "computer" as the object type and it worked. However, it still required full control for "Everyone" but the biggest obstacle is gone,.
So thanks, your reply helped a lot.
I'm glad the suggestions pointed you in the right direction.
When you then have to set permissions, service start-up accounts, etc. pick the user which exist on both systems; even though they are local accounts, that should still resolve the issue of not having the hosts in a domain. This is how we used to get around permission issues with AppMon Collectors not having access to workgroup hosts.