MSDAINITIALIZE minimum required permissions

I was setting up a new SQL Server 2014 (x64) instance and wanted to read some spreadsheets using SQL. This is fairly easy to do:

• Install the Microsoft Access Database Engine 2010 Redistributable.

• Enable Ad Hoc Distributed Queries

  Exec	sp_configure 'show advanced options', 1  
  Reconfigure
  Exec	sp_configure 'Ad Hoc Distributed Queries', 1  
  Reconfigure
  Exec	sp_configure 'show advanced options', 0
  Reconfigure
  

• Determine whether you want to run it in-process or not; I wrote previously in ODBC imports crashing SQL Server with Exception 0xc0000005 that in-process is a bad idea.

  Exec	sys.sp_MSset_oledb_prop 'Microsoft.ACE.OLEDB.12.0'
  Exec	sys.sp_MSset_oledb_prop 'Microsoft.ACE.OLEDB.12.0', 'AllowInProcess', 0
  

Now I attempted to select data from a spreadsheet:

Select	* 
From	Opendatasource('Microsoft.ACE.OLEDB.12.0', 
		'Data Source=C:\Samples\Category.xls;Extended Properties="Excel 12.0 Xml;HDR=YES"')...[Sheet1$]

And I received the below range of success and failure conditions dependent on whether I was using Windows or SQL authentication, and whether the provider was in-process or out-of-process.

Msg 7399, Level 16, State 1, Line 2  
The OLE DB provider "Microsoft.ACE.OLEDB.12.0″ for linked server "(null)" reported an error. Access denied.
Msg 7301, Level 16, State 2, Line 2
Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB provider "Microsoft.ACE.OLEDB.12.0″ for linked server "(null)".

Msg 7302, Level 16, State 1, Line 2
Cannot create an instance of OLE DB provider "MSDASC" for linked server "(null)".

OLE DB provider "Microsoft.ACE.OLEDB.12.0″ for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0″ for linked server "(null)".

So I went about following the instructions linked previously about setting up permissions for this but paused because it doesn't provide any detail about the minimum permissions required. Some experimentation was needed.

I determined that if you're running outside-of-process and only use Windows authentication, you only need to enable this setting on MSDAINITIALIZE (for your SQL Server service account):

• Access Permissions -> Local Access -> Allow

If you are using outside-of-process and need to allow SQL authenticated accounts to work as well, you need to enable the above plus:

• Launch and Activation Permissions -> Local Launch -> Allow
• Launch and Activation Permissions -> Local Activation -> Allow

Oddly, when using the provider in-process I was not able to find a combination of permissions that would allow the query to work using Windows authentication, even after enabling everything.

What I also found was when enabling these permissions I did not always need to restart the SQL Server service for the settings to take effect. It was necessary to ensure there were no "COM Surrogate" processes left running in Task Manager; these sometimes appear to be left behind when the first access attempt fails, and prevent further access attempts from succeeding even when the permissions have been fixed.