2

OBJECTIVE

Use the OPENROWSET feature to JOIN data in a query against a text file.

ERROR

Leveraging the answer from @gbn on this question I am trying to open a row set just like the OP; though the format of the file is a bit different. However, I'm getting the following error trying to access a shared folder:

Msg 4861, Level 16, State 1, Line 1

Cannot bulk load because the file "\MACHINENAME\Share\EC04.txt" could not be opened. Operating system error code 5(Access is denied.).

BACKGROUND

Please understand, I do not and will not have access to the SQL server and so I cannot place a file there.

The file resides on a Windows 7 x64 machine.

The folder has been shared as Read/Write with Everyone.

QUESTION

Can somebody help me understand what other security I need to give to allow the SQL server to access this folder?

Community
  • 1
  • 1
Mike Perrenoud
  • 66,820
  • 29
  • 157
  • 232

3 Answers3

10

If you are logged in as a SQL login then you must create a credential for this login and this credential must have sufficient privileges to read the share.

If you are logged in as a Windows login then you must enable Kerberos constrained delegation for the SQL Server service account.

Right now it seems you're using a Windows login and because the impersonated context cannot flow through the 'double hop' the authentication resolves to ANONYMOUS LOGON, which is not member of Everyone, hence the access denied. All this is exactly the expected behavior. Consult your network administrator about how to setup constrained delegation for the SQL Server service account targeting your desired share.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • Okay, so that's why I say an `ANONYMOUS LOGON` session on my machine after running the query! *I am in fact using Windows Authentication* to connect to the SQL server. I will contact my DBA with that request. – Mike Perrenoud Feb 05 '13 at 15:08
  • +1, saved me a lot of guessing. One thing to add, if you don't create a credential for a SQL login the SQL service's account is used, which may or may not be sufficient. See this [MSDN page](http://msdn.microsoft.com/en-us/library/ms175915(v=sql.120).aspx) which talks about it in a bit more detail in the Security Considerations section. – Serguei Feb 13 '14 at 17:03
2

I had the same issue which was caused by using a SQL DNS-Alias. With Servername\Instance it worked, with ServerAlias\Instance I get Operating system error code 5(Access is denied.).

Benjamin Freitag
  • 443
  • 5
  • 8
0

These steps are required on SQL Server 2017 to make OPENROWSET ('Microsoft.ACE.OLEDB.16.0','Excel 12.0;..) working with a Shared Folder (UNC file-share):

  1. Install Microsoft Access Database Engine 2016 Redistributable
  2. Configure the Service Principal Name (SPN) in Active Directory (as we use Kerberos authentication):
    1. Configure Computer object Security rights (of the Database Server)
    2. Configure Service account Security rights (the user running the SQL-server process)
    3. Check the SQL Server Log for a message like: The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/SRV-DB-01.domain.local:58089 ] for the SQL Server service.
  3. Allow Ad Hoc Distributed Queries

EXEC sp_configure 'show advanced options', 1 RECONFIGURE GO EXEC sp_configure 'ad hoc distributed queries', 1 RECONFIGURE GO

  1. Configure the Driver "Microsoft.ACE.OLEDB.16" with AllowInProcess and DynamicParameters

USE [master] GO EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.16.0', N'AllowInProcess', 1 GO EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.16.0', N'DynamicParameters', 1 GO

  1. Because we enabled AllowInProcess, the service-account and the actual user must have modify-permission to the Temp-Folder on C:\Users\service-account\AppData\Local\Temp. Otherwise you may recieve The provider reported an unexpected catastrophic failure.
  2. Ensure you have proper permissions on the UNC share itself.
  3. Reboot the server to ensure all changes in AD and temp-folder permissions were applied!
  4. Use the actual Database-Server-Name to connect. Don't use an SQL-Alias name.
Benjamin Freitag
  • 443
  • 5
  • 8