none
Problem With OPENDATASOURCE And Microsoft.ACE.OLEDB.15.0 RRS feed

  • Question

  • I have been using OPENATASOURCE and Microsoft.ACE.OLEDB.15.0 to access data from mdb files (ACCESS) for some time now and have never had a problem that I have been unable to address.

    About a week ago I installed a copy of SQL Server 2016 on a new machine running Windows 10 and have been unable to access mdb files any more. I have tried re-installing, re-installing on a Windows Server 2016 machine and even using SQL Server Express but nothing I do cures the problem. I still have a Windows 10 development machine on which everything works without problems but cannot determine what is missing or causing the problem on new installations.

    sp_configure 'show advanced options', 1
    RECONFIGURE
    GO
    sp_configure 'Ad Hoc Distributed Queries', 1
    RECONFIGURE
    GO
    SELECT TableNo, TableName FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.15.0', 'Data Source=C:\Temp\TestSQL.mdb;Jet OLEDB:System database=C:\Temp\System.mdw;User Id=Admin;Password=admin;Jet OLEDB:Database Password=password')...ALocalAList
    GO
    sp_configure 'Ad Hoc Distributed Queries', 0
    RECONFIGURE
    GO
    sp_configure 'show advanced options', 0
    RECONFIGURE
    GO
    

    The code block above should work but unexpectedly results in the following error message:

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

    Microsoft.ACE.OLEDB.15.0 has been properly registered and appears in the Server Objects \ Linked Servers \ Providers.

    If I try and use Microsoft.ACE.OLEDB.12.0 I get the same error!

    I would be most appreciative for any assistance in sorting out this problem.

    Thank you.

    Friday, December 14, 2018 4:14 PM

Answers

  • After two months of looking for a solution to this problem I finally stumbled on the solution quite by accident. I am publishing the solution so that hopefully it will save someone all the aggravation I went through.

    In Microsoft SQL Server Management Studio open your connection to the applicable server, go to Server Objects - Linked Servers - Providers and from the list of providers edit the properties of the relevant provider (in my case Microsoft.ACE.OLEDB.15.0) and enable the option "Allow inprocess" and voila the problem is solved.

    The reason why it took me so long to find is that on my development machine the option is NOT set and for some unknown reason everything still works! On all other machines the option needs to be set.

    I hope this helps someone out there.

    • Marked as answer by infrazee Saturday, January 5, 2019 8:59 AM
    Saturday, January 5, 2019 8:58 AM

All replies

  • Hi infrazee,

    Thanks for posting here.

    According to the error message above, have you installed Microsoft Access Database Engine?

    If not, please download it from this link(https://www.microsoft.com/en-us/download/details.aspx?id=54920), then follow install instructions to solve your problem.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, December 17, 2018 7:26 AM
    Moderator
  • Hi Will,

    Thank you very much for your reply.

    Unfortunately, installation of the Microsoft Access Database Engine did not resolve the problem.

    As the URL in your response sent me to the download site for the Access 2016 version of the software, I also installed the Access 2016 runtime so that Microsoft.ACE.OLEDB.16.0 would be registered and appear in the provider list in SSMS.

    I do not think that the problem is anything to do with Access. I say this because I have a development machine on which there are no problems. If I remove and re-install different versions of Access I encounter no problems.

    However, if I remove and re-install SQL Server I get the problem. I get the problem with SQL Server 2016, SQL Server 2017 and SQL Server 2017 Express.

    I look forward to your response.

    Thank you.

    Monday, December 17, 2018 10:15 AM
  • Check the provider properties on both the machines and compare

    ----------------------------
    Please Mark This As Answer if it solved your issue 
    Please Vote This As Helpful if it helps to solve your issue 
    Yuvraj Singh Bais 
    ----------------------------

    Monday, December 17, 2018 10:29 AM
  • Hi Yuvraj,

    The provider properties are identical on both machines.

    Thank you.

    Monday, December 17, 2018 10:45 AM
  • Hi Will,

    Thank you very much for your reply.

    Unfortunately, installation of the Microsoft Access Database Engine did not resolve the problem.

    As the URL in your response sent me to the download site for the Access 2016 version of the software, I also installed the Access 2016 runtime so that Microsoft.ACE.OLEDB.16.0 would be registered and appear in the provider list in SSMS.

    I do not think that the problem is anything to do with Access. I say this because I have a development machine on which there are no problems. If I remove and re-install different versions of Access I encounter no problems.

    However, if I remove and re-install SQL Server I get the problem. I get the problem with SQL Server 2016, SQL Server 2017 and SQL Server 2017 Express.

    I look forward to your response.

    Thank you.

    Besides, have you granted the file access permission of the file path "C:\Temp\TestSQL.mdb" to the SQL Server Database Engine?

    Configure File System Permissions for Database Engine Access

    One more point, for the installation of Microsoft Access Database Engine 2016 Redistributable, the Provider argument of the ConnectionString property should be "Microsoft.ACE.OLEDB.12.0", not "Microsoft.ACE.OLEDB.16.0". And follow statement format of this article to rewrite the query statement.

    https://docs.microsoft.com/en-us/sql/t-sql/functions/openrowset-transact-sql?view=sql-server-2017#b-using-the-microsoft-ole-db-provider-for-jet

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, December 18, 2018 9:51 AM
    Moderator
  • Hi Will,

    File access permissions have been properly granted for the file C:\Temp\TestSQL.mdb.

    I have been unable to rewrite the query using OPENROWSET because I have been unable to find a way of using OPENROWSET with 'Jet OLEDB:Systemdatabase' using the Microsoft.ACE.OLEDB12.0 provider. I get an error message about credentials!

    The code I detailed in my original question has worked and currently still works on my development machine and all I need to do is find what has changed for new installations. I suspect there is something different somewhere in the registry settings.

    Kindest regards,

    Julian

    Tuesday, December 18, 2018 7:33 PM
  • Hi Will,

    File access permissions have been properly granted for the file C:\Temp\TestSQL.mdb.

    I have been unable to rewrite the query using OPENROWSET because I have been unable to find a way of using OPENROWSET with 'Jet OLEDB:Systemdatabase' using the Microsoft.ACE.OLEDB12.0 provider. I get an error message about credentials!

    The code I detailed in my original question has worked and currently still works on my development machine and all I need to do is find what has changed for new installations. I suspect there is something different somewhere in the registry settings.

    Kindest regards,

    Julian

    Hi Julian,

    Please follow the following detailed steps, it works on my side:

    Step 1, Check if the provider "[Microsoft.ACE.OLEDB.12.0]"  installs on your SQL Server machine.

    Step 2, make sure the database engine could access the access database file path, which is mentioned in my previous reply.

    Step 3, execute the following script and see if it works, not add extra properties.

    SELECT TableNo, TableName FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Data Source=C:\Temp\TestSQL.mdb;User Id=Admin;Password=admin')...ALocalAList

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 20, 2018 9:16 AM
    Moderator
  • Hi Will,

    Thank you so much for your response.

    I have carried out your three steps on the machine that currently works and also on the new installation that doesn't.

    On BOTH machines, just as I expected, I received the error message

    Msg 15281, Level 16, State 1, Line 1

    SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online.

    I then went and added the first 6 lines and last 6 lines that I detailed in my original post.

    On the first machine I received the error message:

    Msg 7399, Level 16, State 1, Line 7

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

    Msg 7303, Level 16, State 1, Line 7

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

    On the second machine I received the error message:

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

    The error message on the first machine is just as I expected because the "extra properties" are required for proper Authentication. The error message on the second machine is unexpected as if the provider is not properly registered!

    You say that, "it works on my side". If you do the same thing on a new installation of Windows 10, SQL Server and the Access database engine does it still work? I ask this because my machine that does not give problems has an old installation on it. All new installations give problems.

    Kind Regards,

    Thursday, December 20, 2018 6:49 PM
  • Hi infrazee,

    Thanks for your reply.

    Per your reply, which machine's error messages you need to solve?  It makes me confused that I could n't determine which machine you're doing that operation on?

    >>Cannot create an instance of OLE DB provider "MSDASC" for linked server "(null)".

    For such error message, please try this command and see if it works.

    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  

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, December 24, 2018 8:04 AM
    Moderator
  • Hi Will,

    I am afraid that we are getting further away from a solution.

    I have two machines, one an old installation (2017) and a new installation. Everything works fine on the old installation but not on the new installation. In your response of 20th December, you said "it works on my side" by which I understand that you tried the operation on your machine and it works. Would you be so kind as to try the operation on a new installation of Windows 10, SQL Server and Access Database Engine and let me know whether it still works?

    Thank you

    Monday, December 24, 2018 10:34 AM
  • First, I don't use Access and I have no experience of the ACE provider. However, I have some experience of building systems and solutions. And I have had my fair share of fighting configuration problems. And I have seen more than one thread like this.

    If you really want to pursue this, first check that you have the right bit-size of the provider, so that you are not trying to use a 32-bit provider. Next, check file permissions in Windows until you are blue in the face. Most likely this where the problem lies. And check not only your own permission, but the permissions for the SQL Server account.

    But most of all,  you should ask yourself: am I really on the right solution. Even if you get it to work, you may run into the same issue on the next machine you migrate. Or, if the issue is file permissions, it may start to fail again, because some gets the idea to harden the machine and removes permissions that seems to be out of place. So maybe you should consider a different solution that does not rely on a linked server or OPENDATASOURCE/ROWSET. Maybe SSIS is an option. Maybe just an external program in C# or Powershell that accesses both the database and Access.

    Finally, keep in mind that when Will says that it works on his side, all this tells you that this is overall possible. But Will does not see your machine, and don't know what issues that might be hiding there.

    By the way, do you have an anti-virus running on the problematic machine?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, December 24, 2018 1:55 PM
  • After two months of looking for a solution to this problem I finally stumbled on the solution quite by accident. I am publishing the solution so that hopefully it will save someone all the aggravation I went through.

    In Microsoft SQL Server Management Studio open your connection to the applicable server, go to Server Objects - Linked Servers - Providers and from the list of providers edit the properties of the relevant provider (in my case Microsoft.ACE.OLEDB.15.0) and enable the option "Allow inprocess" and voila the problem is solved.

    The reason why it took me so long to find is that on my development machine the option is NOT set and for some unknown reason everything still works! On all other machines the option needs to be set.

    I hope this helps someone out there.

    • Marked as answer by infrazee Saturday, January 5, 2019 8:59 AM
    Saturday, January 5, 2019 8:58 AM
  • The reason it took me as long to discover is that on my growth device the choice is NOT established and also for some unidentified factor every little thing still functions! On all various other equipments the alternative requires to be established.

    I wish this aids somebody available.

    In Microsoft SQL Web server Administration Workshop open your link to the appropriate web server, most likely to Web server Items - Connected Web Servers - Carriers and also from the checklist of suppliers modify the residential properties of the appropriate carrier (in my instance Microsoft.ACE.OLEDB.15.0) as well as allow the alternative "Enable inprocess" as well as voila the issue is resolved.
    • Proposed as answer by Jits Arora Saturday, January 5, 2019 9:13 AM
    Saturday, January 5, 2019 9:12 AM