Here is another blog which explains a situation I was in while reading data from excel. In this blog, we would talk about error “Access Denied” error while using The OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server.
I was using the following query to read the data from excel. Filename “SQLAuthority.xlsx” and sheet name was “Sheet1”.
SELECT * FROM OPENROWSET( 'Microsoft.ACE.OLEDB.12.0' ,'Excel 12.0;Database=F:\Backup\SQLAuthority.xlsx;HDR=YES' ,'SELECT * FROM [Sheet1$]')
The error message while running the query is as follows.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)” reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)”.
If you observe in the image, the path, file name etc. are correct.
Here is the content of the excel file.
Since we can see “Access Denied” in the message, I tried all possible permissions to file, folder, everyone, full control etc. but none helped. I also tried explicit permission to service account NT Service\MSSQLSERVER.
As I mentioned, I checked service account and it was set as NT Service\MSSQLSERVER.
I changed that to Local System as shown below and restarted the service.
And then it started working.
Have you seen this error and found some other solution? If yes, please write in the comment box below to help others.
Reference: Pinal Dave (https://blog.sqlauthority.com)