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.
SOLUTION/WORKAROUND
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)
19 Comments. Leave new
Open SQL Server with Run as administrator option, then also we can avoid this error.
Hi Pinal
I have seen this error before , usually I grant sql server service account permissions to this file and it works, in your case however this account was not a windows account so you could have tried to give the computer account permissions to this file. Its always recommended to run SQL server with a windows account if interacting with other SQL servers or the file system.
Why does it work with “Local System” account and not with “NT Service\MSSQLServer” account.
Network Service Account
The Network Service account is a built-in account that has more access to resources and objects than members of the Users group. Services that run as the Network Service account access network resources by using the credentials of the computer account in the format \$. The actual name of the account is NT AUTHORITY\NETWORK SERVICE.
Local System Account
Local System is a very high-privileged built-in account. It has extensive privileges on the local system and acts as the computer on the network. The actual name of the account is NT AUTHORITY\SYSTEM
“NT Service\MSSQLServer” is a virtual account it would not have access to the file system
Hi,
I want to create a Linked server from SQP SERVER 2005 to SQL SERVER 2016,
Can it will be possible to create a link server from 2005 to 2016..
Thanks
thank you! I waste few hours with this error and then I saw your article which save me.
I am late to the party but I just wanted to add that your solution works!
This has worked in my case and no changing account needed:
USE [master]
GO
EXECUTE master.dbo.sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′, N’AllowInProcess’, 1
After this update try OPENROWSET on fresh xls file or restart SQL Server service.
Hi
I did all settings as described above article & followed all comments by all users and I am Exporting data from Sqlserver to Excel doc. I am using the below query inside a stored procedure and I have millions of rows to export to Excel. The below query works good but some times it fails with no exception logged in DB server , I am not sure what caused this failure but after restarting SQL server all works fine. Any Help please.
INSERT INTO OPENROWSET(‘Microsoft.ACE.OLEDB.12.0′,’Excel 12.0;
Database=g:\Tuning\trial.xlsx;’,’SELECT * FROM [Sheet1$]’)
select * from mytable]
This is such a bizarre behavior. In our organization, we have two (2) SQL Server 2016 environments (Production, and Development). The Development environment is a VM clone of Production. The Development environment should be exactly the same as Production settings-wise except for the instance name and server name adjustment. The crazy thing is that the OPENROWSET query works in Development, but not in Production. This is the syntax I’ve been using:
SELECT *
FROM OPENROWSET ( ‘Microsoft.ACE.OLEDB.12.0′
,’Excel 12.0; Database=C:\Temp\Testing\Test.xls; HDR=No; IMEX=1’
,[Sheet1$]
);
GO
I’ve been pulling my hairs out and I’ve tried almost all solutions I could find online (including activating Dynamic Parameters and Allow Inprocess), but none of them work. Any insight on this is much appreciated. I keep getting this error in Production:
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 123
Cannot obtain the required interface (“IID_IDBCreateCommand”) from OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)”.
In the Development environment (which is a VM clone of Production (just a reminder)), I get no errors (it just works fine).
I forgot to mention that both Production and Development environments use the SQL Server Service virtual account (NT Service\MSSQLSERVER).
Is there any way to prevent an error from an excel workbook linked server when the workbook is open? I want to just read any the data without any concerns as to whether or not the workbook is open?
Thank you so much. I have spent so many hours trying so many different things. This did the trick.
Thank you, It worked like a charm
I am getting the error, when i try to run the store proc created on oracle server. I am able to execute all the query except for store proc
Thank you. The import of excel files by any other means had been tried without success – but this works!