cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Bulk insert failure due to denied access

olga_wall
Participant

Hello,

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!


8 REPLIES 8

Hi @Olga W. based on the bulk insert documentation, the shared bulkinsert folder must reside on the database server host, not the NAM report server host.

NAM server writes to the share e.g. \\db_server_name\bulkinsert and DB server reads from the local folder e.g. D:\bulkinsert


olga_wall
Participant

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.


john_leight
Dynatrace Pro
Dynatrace Pro

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.


Hi John,

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 belive adding the computer for access requires the systems to be in AD. When you mentioned they were in a workgroup, I've never tested that. The systems I've worked with have always been in a Domain.

I'm glad the suggestions pointed you in the right direction.

Regards....



If the hosts are in a workgroup, you can get around that by creating an identical user account e.g. 'bulkinsert_user' on both hosts, with the exact same password on both too.


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.

That sounds like one way to get around it. I think there is another where you change group policies to allow anonymous access to the share so you don' t have to change service accounts. It's been a while since I had to do that.