SSRS 2017 The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

Question

SSRS 2017 The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

Brandt Smith 96

I've seen numerous articles about this but nothing seems to work on my 64bit SQL 2017 server with SSRS installed. I am using Report Builder on that machine and can create the Data Source but the query returns that message. The Excel file is local xlsx file. It is a 32 bit excel file exported from a SP2010 site list. Using ODBC and DSN seemed not to work , so I am trying to use OLEDB. This should be easy!

Joyzhao-MSFT 15,636 Reputation points

2021-04-01T02:07:24.303+00:00

Hi @Brandt Smith ，
I am glad to hear that your issue has been resolved. If you have any questions, please feel free to let me know.
Best Regards,
Joy

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Brandt Smith 96 Reputation points

2021-05-21T13:48:08.973+00:00

OK - so new issue...I can only access the Excel data source as an Administrator on the SSRS machine (which of course I am)...normal users get a cannot connect to data source message - but they CAN run the report from the Report Designer on their desktop...so I have to make them a local admin on the SSRS box then they can run the report from SSRS...and all the users have access to the Excel file through sharing the folder and can open and edit the spreadsheet manually. This is insanely hard. I have even used ODBC with the DSN created on the SSRS box and again I (Admin) can run those reports but not nonAdmins...I am thinking about using a Flow to stuff the Excel data into a SQL table where of course everything works.

3 answers

Your answer

Joyzhao-MSFT 15,636 Reputation points

2021-04-01T02:07:24.303+00:00

Hi @Brandt Smith ，
I am glad to hear that your issue has been resolved. If you have any questions, please feel free to let me know.
Best Regards,
Joy

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Brandt Smith 96 Reputation points

2021-05-21T13:48:08.973+00:00

OK - so new issue...I can only access the Excel data source as an Administrator on the SSRS machine (which of course I am)...normal users get a cannot connect to data source message - but they CAN run the report from the Report Designer on their desktop...so I have to make them a local admin on the SSRS box then they can run the report from SSRS...and all the users have access to the Excel file through sharing the folder and can open and edit the spreadsheet manually. This is insanely hard. I have even used ODBC with the DSN created on the SSRS box and again I (Admin) can run those reports but not nonAdmins...I am thinking about using a Flow to stuff the Excel data into a SQL table where of course everything works.

Answer 1

There is no such thing as a 32-bit Excel file. Office files don't have bitness. You probably meant you created the Excel file using the 32-bit version of Office. But that doesn't matter for opening the Office document.

Data sources are either x86 or x64 and you use 2 different UIs to set them up. If you simply typed "Data Source" into Windows search then it likely brought up the 32-bit version. You need to use the x64 version to set up the data source if you are using SSRS to connect to it. However the driver has to be installed first. Open the x64 version of data sources and ensure the ACE driver shows up. If it doesn't then you need to install the x64 version of the driver first.

But here's the thing, SSRS is just the server side. If you are building a report then you'll be using a separate tool, likely the VS Shell which is 32-bit. Therefore when you are designing the report you'll be using the 32-bit version of the driver. However when you deploy the report to your SSRS server you'll need to configure the data source in the SSRS Report Manager UI to use the x64 driver. This basically just means opening the data source in the UI and ensuring it properly connects to the source. If both x86 and x64 versions of the driver are installed it should just work.

Please clarify when you are getting the error: When you go into preview mode in the designer, when you attempt to deploy and run the report on the server, etc?

What designer are you using to test with (VS Shell's preview, SSRS Report Manager, etc)?

Have you confirmed in the Data Sources of Windows for both x86 and x64 that the driver is installed?

If you can see the data source while designing/previewing but not when trying to run from the server can you confirm if you can see the data source on the SSRS server?

Answer 2

Brandt Smith 96

OK...thanks...I am using the Report Builder on the 64 bit sql server to design the report. I cannot get the report to run using Report Builder...I have try installing the 32 & 64 bit versions of the AccessDatabaseEngine .
So I'd like to be able to have users upload an Excel file to a shared folder (on the SQL Server machine) and then run the report from SSRS...OLEDB?

When I use the OLEDB string (Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\ExcelDataSSRS\AttendeeList.xlsx;
Extended Properties="Excel 12.0 Xml;HDR=YES";) it connects OK. But when I set up the query I get an unhandled exception...there is so much conflicting steps to solve that by installing AccessDatabaseEngine(s)...I have installed and uninstalled them multiple times and either hit that exception or an architecture mismatch.

Michael Taylor 60,346 Reputation points

2021-03-31T15:12:08.917+00:00

Wait a second. You said you're uploading Excel files. But then you say you're using the Access Database Engine. That is for Access databases, not Excel files.

For Excel files you should be using the Microsoft Excel Driver under ODBC. Access files can use the ACE.OLEDB driver.

Answer 3

Brandt Smith 96

OK...so I just devd it on my machine and created a matching folder & file on my machine...and it ran...then uploaded to SSRS and it ran! SO - dont develop on the SSRS machine is only thing I can come up with! I didnt even have to fiddle with the data source...it is embedded...PITA for sure figuring this out! Your post helped though got me thinking about the separation of data sources

Michael Taylor 60,346 Reputation points

2021-03-31T15:44:13.037+00:00

Glad to hear you got it working now.
Brandt Smith 96 Reputation points

2021-05-21T20:52:33.98+00:00

OK - so new issue...I can only access the Excel data source as an Administrator on the SSRS machine (which of course I am)...normal users get a cannot connect to data source message - but they CAN run the report from the Report Designer on their desktop...so I have to make them a local admin on the SSRS box then they can run the report from SSRS...and all the users have access to the Excel file through sharing the folder and can open and edit the spreadsheet manually. This is insanely hard. I have even used ODBC with the DSN created on the SSRS box and again I (Admin) can run those reports but not nonAdmins...I am thinking about using a Flow to stuff the Excel data into a SQL table where of course everything works.

Share via

SSRS 2017 The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

3 answers

Your answer