The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)"

Question

I'm trying to run the following statement but am receiving the error messages just below. I have researched answers to no end and none have worked for me. I'm running Office 365 (64bit). I have loaded the Microsoft Access Database Engine (64bit). This is in Visual Studio 2013 with SSDT as well as SQL Server 2012. I do not have access to changing environment or startup parameters to SQL Server. Any help is appreciated.

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.15.0', 
    'Excel 12.0;Database=C:\Users\UserName\Folder\SomeFile.xlsx;;HDR=NO;IMEX=1', [Table 1$])

• Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "Microsoft.ACE.OLEDB.15.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
• Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.15.0" for linked server "(null)".

Here's what I have tried:

First, I ran...

sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

Followed by...with no love.

EXEC sys.sp_addsrvrolemember @loginame = N'<<Domain\User>>', @rolename = N'sysadmin';
GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.15.0', N'AllowInProcess', 1 
GO 
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.15.0', N'DynamicParameters', 1 
GO 

I have changed the code to read Microsoft.ACE.OLEDB.12.0 as I have seen that as well, still no love.

I have also checked permissions of C:\Users\MSSQLSERVER\AppData\Local\Temp and C:Windows\ServiceProfiles\NetworkService\AppData\Local which have granted Full Control for the following: System, MSSQLSERVER, and Administrators, Network Service (on the latter).

Still no love.

Lastly, I have tried changing to the 32bit version of the Microsoft Access Database Engine which has persisted in not working.

Help, anyone?

Answer 1

This is the only solution that worked for me for the (Access denied) issue. The solution was tested on two of my servers and worked liked magic:

Error:

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. 
  Access denied.

Solution:

1. Open “Component Services” (Administrative Tools, Component Services or “dcomcnfg” from Windows run dialog).
2. Navigate to Console Root - Component Services - Computers - My Computer
3. Right-click "My Computer" and select "Properties"

• In the "Default Properties" tab:

Check the option "Enable Distributed COM on this computer"

Default Authentication Level = Connect

Default Impersonation Level = Identify or Impersonate

4. In the "COM Security" tab: • Click on the "Edit Default..." button for "Access Permissions"

   If you see a list of names in the dialog box then make sure the account used to start the SQL Server service belongs to the list or a group that belongs to this list.

• Click on the "Edit Default..." button for "Launch and Activation Permissions"

If you see a list of names in the dialog box then make sure the account used to start the SQL Server service belongs to the list or a group that belongs to this list

5. In the "Default Protocols" tab:

   You should at least see "Connection-oriented TCP/IP"

6. Restart SQL server instance service

Source:

https://www.sqlservercentral.com/articles/setting-up-linked-servers-with-an-out-of-process-oledb-provider

More tips to follow - written by me - tested twice:

• Make sure the file is not in use (closed).

• Grant 'sysadmin' permission to the database user.

• Enable Ad-Hoc queries. Enable DynamicParameters and disable AllowInProcess for OLEDB.

• Allow SQL Server service logon account (NT Service<InstanceName>) to access the folder where your file is located.

• Download & install MICROSOFT.ACE.OLEDB.12.0 (Microsoft Access Database Engine 2010 Redistributable) linked server (64/32-bit matters).

• Apply General DCOM settings, Skip other configurations metioned there.

Answer 2

I took ownership of the folder: C:\Users\MSSQLSERVER\ and gave logged in user full rights.

Then I copied the spreadsheet to C:\Users\MSSQLSERVER\Downloads.

This worked!

select * 
FROM OPENROWSET( 
  'Microsoft.ACE.OLEDB.12.0', 
  'Excel 12.0;Database=C:\Users\MSSQLSERVER\Downloads\ConvertedFiles.xlsx;HDR=YES', 
  'SELECT * FROM [ConvertedFiles$]')

Answer 3

In my case, I fixed followed by listed points:

1. Make sure the SQL server has full access to the folder where the Excel file is located.

2. Make sure SQL Service is running under the local system account.

3. Make sure HDR=NO in OPENROWSET command.

4. Make sure brackets[] applied in SELECT command as sheetName

EXAMPLE:

SELECT * 
FROM
OPENROWSET(
      'Microsoft.ACE.OLEDB.12.0',
      'Excel 12.0;Database=c:\Temp\MyFile.xlsx; HDR=No',
      'SELECT * FROM [Sheet1$]')

Answer 4

I tried all the options mentioned in Stackoverflow. But still I had the same Issue . But it resolved by inserting into table first and then using it.

From :

select *  from OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
'Excel 8.0;Database=C:\Bharathi\Working\31012023_SSD\SSD.xlsx', 
'SELECT * FROM [Programs$]')

To : Just inserting to temp table first

 select * into #temp from OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 8.0;Database=C:\Bharathi\Working\18112022_Consoleforlob\data.xlsx',
'SELECT * FROM [Programs$]')

now you can use #temp table to see results

Select * from #temp

Answer 5

3 things that gives error -File is open -Need full read/open access to the share folter -File is PassWord protected.

File name is case sensitive E.G. IMEX=1;Database=\FPTsite.mywebsite.com\E$\FTP\Folder1\FoldDer2\202202_MyFile.xlsx' is not the same as IMEX=1;Database=\FPTsite.mywebsite.com\E$\FTP\Folder1\FoldDer2\202202_myfile.xlsx'

Answer 6

There are two crucial nonobvious settings that I've discovered when tuning linked servers on Excel under SQL Server 2014. With those settings, ' FROM OPENDATASOURCE(''Microsoft.ACE.OLEDB.16.0'', ...)' as well as '... FROM [' + @srv_name + ']...data AS xl ...' function properly.

Linked server creation

This is just for context.

DECLARE @DB_NAME NVARCHAR(30) = DB_NAME();
DECLARE @srv_name nvarchar(64) = N'<srv_base_name>@' + @DB_NAME; --to distinguish linked server usage by different databases

EXEC sp_addlinkedserver
    @server=@srv_name,
    @srvproduct=N'OLE DB Provider for ACE 16.0',
    @provider= N'Microsoft.ACE.OLEDB.16.0',
    @datasrc= '<local_file_path>\<excel_workbook_name>.xlsx',
    @provstr= N'Excel 12.0;HDR=YES;IMEX=0'
;

1. @datasrc: Encoding is crucial here: varchar instead of nvarchar.
2. @provstr: Version, settings and sytax are important!
3. @provider: Specify the provider installed in your SQL Server environment. Available providers are enumerated under Server Objects::Linked Servers::Providers in SSMS's Object Explorer.

Providing access to the linked server under specific SQL Server logins

This is the first crucial setting. Even for SA like for any other SQL Server login:

EXEC sp_addlinkedsrvlogin @rmtsrvname = @srv_name, @locallogin = N'sa', @useself = N'False', @rmtuser = N'admin', @rmtpassword = N''
;

1. @rmtuser: It should be admin. Actually, there is no any admin in Windows logins on the system at the same time.
2. @rmtpassword: It should be an empty string.

Providing access to the linked server via ad hoc queries

This is the second crucial setting. Setting Ad Hoc Distributed Queries to 1 is not enough. One should set to 0 the DisallowAdhocAccess registry key explicitly for the driver specified to @provider:

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.DEF_INST\Providers\Microsoft.ACE.OLEDB.16.0]
"DisallowAdhocAccess"=dword:00000000

Answer 7

On top of the previously mentioned error numbers combinations, error numbers 7399 and 7303 can mean both that the file is open and the file isn't even an Excel file. So keep that in mind aswell.

Answer 8

For future reference, granting SQL Server service account Full Control for the "C:Windows\ServiceProfiles\NetworkService\AppData\Local\Temp" solved the following issue for me.

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".

Answer 9

I had same problem and the reason was "This copy of Microsoft Office is not activated" on remote side. (XLSX linked from the other machine)

Answer 10

Removing Read-only CheckBox from C:\Users\SQL Service account name\AppData\Local\Temp worked for me.

Answer 11

I had the same issues even after running all those EXEC stored procedure commands that you see on every answer/blog, but when I ran SSMS as administrator, the problems went away. Since, I didn't want to do that, I opened Services, went to SQL Server (MyServer), right clicked, selected properties. On the Log On tab, "This account:" was selected with and listed my SQL Server account/password. I instead checked "Local System account" and ticked the "Allow service to interact with desktop". Then I stopped and started the service. Now, I can run the same query as the original poster mentions without running as administrator in SSMS. This probably worked because I am running SQL Server on my home desktop.

I'd also like to point out that you can use GUI in SSMS to enable adhoc access (and many other options). In SSMS, go to Linked Servers>Providers, right click on the provider, and select properties. Then you can just check/uncheck the ones you want.

Answer 12

For me it was the permission on the file's folder. I had to add all permissions for user "Everyone". Folder/properties/security -> add user everyone and set all permissions.

Answer 13

This is for my reference, as I encountered a variety of SQL error messages while trying to connect with provider. Other answers prescribe "try this, then this, then this". I appreciate the other answers, but I like to pair specific solutions with specific problems

---

Error

...provider did not give information...Cannot initialize data source object...

Error Numbers

7399, 7303

Error Detail

Msg 7399, Level 16, State 1, Line 2 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. 
  The provider did not give any information about the 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)".

Solution

File was open. Close it.

Credit

• https://stackoverflow.com/a/29369868/1175496

---

Error

Access denied...Cannot get the column information...

Error Numbers

7399, 7350

Error Detail

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 7350, Level 16, State 2, Line 2 Cannot get the column information 
  from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Solution

Give access

Credit

• https://stackoverflow.com/a/27509955/1175496

---

Error

No value given for one or more required parameters....Cannot execute the query ...

Error Numbers

???, 7320

Error Detail

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "No value given for one or more required parameters.".
Msg 7320, Level 16, State 2, Line 2
Cannot execute the query "select [Col A], [Col A] FROM　$Sheet" against OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)". 

Solution

Column names might be wrong. Do [Col A] and [Col B] actually exist in your spreadsheet?

---

Error

"Unspecified error"...Cannot initialize data source object...

Error Numbers

???, 7303

Error Detail

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)".

Solution

Run SSMS as admin. See this question.

---

Other References

Other answers which suggest modifying properties. Not sure how modifying these two properties (checking them or unchecking them) would help.

• https://stackoverflow.com/a/31605038/1175496
• http://www.aspsnippets.com/Articles/The-OLE-DB-provider-Microsoft.Ace.OLEDB.12.0-for-linked-server-null.aspx
• https://social.technet.microsoft.com/Forums/lync/en-US/bb2dc720-f8f9-4b93-b5d1-cfb4f8a8b1cb/the-ole-db-provider-microsoftaceoledb120-for-linked-server-null-reported-an-error-access?forum=sqldataaccess#3fcc14f4-420e-4544-be74-eea1e0e78462

Answer 14

This is my error code:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "The Microsoft Access database engine could not find the object 'ByStore$'. Make sure the object exists and that you spell its name and the path name correctly. If 'ByStore$' is not a local object, check your network connection or contact the server administrator.".

Msg 7350, Level 16, State 2, Procedure PeopleCounter_daily, Line 26

Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

My problem was the excel file was missing at the path. Just put the file with the correct sheet will do.

Answer 15

I had the exact same error message, and tried the suggested solutions in this thread but without success.

what solved the problem for me is opening the .xlsx file and saving it as .xls (excel 2003) file.

maybe the file was corrupted or in a different format, and saving it again fixed it.

Answer 16

With SQL 2014, I changed the SQL Server Service (MSSQL) to run as LocalSystem. This solved the problem for me.

It used to work as NT_SERVICE\MSSQL$MSSQL fine under 2008, from what I remember.

Answer 17

Exec sp_configure 'show advanced options', 1;
RECONFIGURE;
GO

Exec sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1; 
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1;
GO

Insert into OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source=C:\upload_test.xlsx;Extended Properties=Excel 12.0')...[Sheet1$]
SELECT ColumnNames FROM Your_table -- Sheet Should be already Present along with headers

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 0;
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 0;
GO

Exec sp_configure 'Ad Hoc Distributed Queries', 0;
RECONFIGURE;
GO

Exec sp_configure 'show advanced options', 0
RECONFIGURE;
GO

Answer 18

For me, these two things helped on different occasions:

1) If you've just installed the MS Access runtime, reboot the server. Bouncing the database instance isn't enough.

2) As well as making sure the Excel file isn't open, check you haven't got Windows Explorer open with the preview pane switched on - that locks it too.

Answer 19

Here is specifically what worked for me only when the Excel file being queried was not open and when running the SQL Server Service as me [as a user that has access to the file system]. I see pieces of my answer already given elsewhere, so I apologize for any redundancy, but for the sake of a more succinct answer:

USE [master]
GO

EXEC sp_configure 'Show Advanced Options', 1
RECONFIGURE
GO

EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE
GO

EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO

EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO


SELECT * 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                'Excel 12.0;Database=C:\MyExcelFile.xlsx',
                'SELECT * FROM [MyExcelSheetName$]')

Answer 20

In my case this problem was occuring because i was accessing the file from shared folder by using computerName. OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;Database='\ntpc0100\MysharedFolder\KPI_tempData\VariablePayoutDetails.xls';IMEX=1;'','.....) Insted of using computerName ntpc0100(whatever your machine name) you should specify IPaddress of your machine. for example:- OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0; Database='\193.34.23.200\MysharedFolder\KPI_tempData\KPIVariablePayoutDetails.xls'....);

Answer 21

I'm running SQL Server 2014 64 bit on Windows 10. I tried everything, the thing that made it work was:

EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 0

I don't know why the AllowInProcess turned off makes it work but that was the key in my case. Thank you for the suggestion of turning all the options off on the linkserver.

Answer 22

Instead of changing the user, I've found this advise:

https://social.technet.microsoft.com/Forums/lync/en-US/bb2dc720-f8f9-4b93-b5d1-cfb4f8a8b1cb/the-ole-db-provider-microsoftaceoledb120-for-linked-server-null-reported-an-error-access?forum=sqldataaccess

This might help someone else out - after trying every solution to trying and fix this error on SQL 64..

Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

..I found an article here...

http://sqlserverpedia.com/blog/sql-server-bloggers/too-many-bits/

..which suggested I give Everyone full permission on this folder..

C:\Users\SQL Service account name\AppData\Local\Temp

And hey presto! My query suddenly burst into life. I punched the air in delight.

Edwaldo

Answer 23

In our case, it helped to add a parameter for SQL Server service:

1. Go to Services.msc, select SQL Server Service and open Properties.
2. Choose Startup Parameters and add new parameter –g512
3. Restart SQL server service.

Answer 24

1. Close SQL Server Management Studio. Type Services.msc in the run command to open the services window.

2. Search for SQL Server Service and right click it and select properties.

3. In the Log On Tab, select system account/or select your domain ID and Account and password.

4. Once it finds your login name press OK.

5. Now type your login’s passwords in both the fields.

6. Restart the services so that the new changes are applied as shown in figure below.

7. Now start SQL Server Management Studio and try to run the query if still not working try a system restart.

... or execute the following query:

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

Answer 25

In the SQL Server, try these steps:

1. Open one database.
2. Click in the option Server Object.
3. Click in Linked Servers.
4. Click in Providers.
5. Right click on Microsoft.ACE.OLEDB.12.0 and click Properties.
6. Uncheck all the options and close.

Answer 26

Make sure the Excel file isn't open.

Answer 27

http://www.aspsnippets.com/Articles/The-OLE-DB-provider-Microsoft.Ace.OLEDB.12.0-for-linked-server-null.aspx

This solves the issue. For some reason SQL Server does not like the default MSSQLSERVER account. Switching it to a local user account resolves the issue.