I'm trying to develop a SSDT sln, where a .csv is parsed. However I get the error:

Operating system error code 5(Access is denied.)

According to this post: https://stackoverflow.com/questions/14555262/cannot-bulk-load-operating-system-error-code-5-access-is-denied it's a question of adding users. That specific user didn't exist on my local machine. I tried adding ALL the security principles from my Windows 10 machine and it worked. So one of the list of about 60 or so principles is the correct user to give access to SQL Server 2016 but I would prefer not to go through one by one...

This answer: https://social.technet.microsoft.com/Forums/Windows/en-US/58992456-ec8a-41e8-825b-1e56a075073a/sqlservermssqlusercomputernameinstancename-account-do-not-create-by-default?forum=sqlsetupandupgrade mentions a different user, but I can't find that user either. There are multiple users in the form MSSQLSERVER0x (I think I have the R server installed). But adding the group these users are part of doesn't help either

==== EDIT ====

It worked when I added the 'Users' group - which has 3 members:

  1. my login user
  2. NT AUTHORITY\Authenticated Users

None of these principles appeared in the list of principles that I could add directly to folder permissions. What are no 2 and 3? And why didn't the security settings tab find ALL the users that were listed in the Local Users and Groups\Users\ folder (because it listed some that were)?

  • nope. excel not open... – Zach Smith May 10 '17 at 20:00
  • How are you running the SSIS package - from SSDT? from dtexec.exe? or from a SQL job? Which user needs access to the file should depend on how the package is being run. In SSDT or dtexec, I'd expect the account you're logged in as to be running the task - if via a SQL job, it's the account that the SQL Server Agent service runs under. – RDFozz May 10 '17 at 20:00
  • I'm running it as a debug from VS2015. So it's probably my windows login. Although this I'm not sure why... my connection manager specifies a SQL Server agent login. So I would expect the file to be accessed by the connected user, not the runner of the debugger – Zach Smith May 10 '17 at 20:13
  • Good point. That's the user I'd target. – RDFozz May 10 '17 at 20:34
  • @Zach Smith This can be a double hop issue. this usually happens when The process is triggered from a PC, calls a procedure or some object on the SQL Server which tries to access a file (read or Write) on a File server... I can tell you that it isn't a walk in the park... I had the issue and managed to fix it with the help of our Network Administrator. An indication if it is a double hop issue is if you can run this Successfully directly from the SQL server. I might be off but I would at least make sure that it isn't a double hop issue. – JohnG May 11 '17 at 20:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.