Question: OBJECTIVE
---------

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

ERROR
-----

Leveraging the answer from @gbn on [this question](https://stackoverflow.com/questions/4562911/using-input-from-a-text-file-for-where-clause) 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?

Answer: 
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.).`
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](https://www.microsoft.com/en-us/download/details.aspx?id=54920)
2. [Configure the Service Principal Name (SPN) in Active Directory](https://www.alexandreviot.net/2014/09/30/sql-server-could-not-register-the-service-principal-name-spn/) (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`](https://stackoverflow.com/a/14544245/4049371)

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

4. Configure the Driver "Microsoft.ACE.OLEDB.16" with [`AllowInProcess`](https://www.sqlservercentral.com/articles/setting-up-linked-servers-with-an-out-of-process-oledb-provider) 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`
> 
> 
> 

5. 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*.
6. Ensure you have proper permissions on the UNC share itself.
7. Reboot the server to ensure all changes in AD and temp-folder permissions were applied!
8. Use the actual Database-Server-Name to connect. Don't use an SQL-Alias name.