Cant Connect to Linked Server

  

Good afternoon,

Right now I have configured a linked server in SQL Server 2008 to link to an access database using the provider "12.0 OLE DB".

When I connect directly from the SQL Server using my admin account I can successfully query the DB.

I used Integration Studio to make an integration with this Linked Server from my project.

My problem is:

If I use a network path in the linked server url ( eg:  "\\SOMETHING.mycompany.intern\somefolder\file.mdb" )

I get the following error in Service Center:

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

If I use a physical address ( eg: C:\temp\.file.mdb ) it works!


The thing is that I must use a network address.

I have these options enabled:

Dynamic Parameter
Allow inprocess
Disallow adhoc access


My guess is this is a issue with user privileges/rights,


Do you know how can I investigate it further? Since Service Center is not helping me much with this error,

If you need any info about any configuration feel free to ask,

I really need to get this working as I'm around it for 2 days and still found no solution to it,

Thanks in advance

VC

Hi Vincent,

I don't have much experience connecting to Access DB's using Outsystems, but I found this an interesting problem. After some searching, it seemed like a 'bug' (special feature) in the OLE DB Provider.

I think you need to set Disallow AdHoc  Access to false, but you cannot do it by unchecking the value in the configuration. You need to edit the registry key.

I found some info here:
https://support.microsoft.com/en-us/help/327489/how-to-use-the-disallowadhocaccess-setting-to-control-access-to-linked

https://blogs.msdn.microsoft.com/docast/2015/07/21/opendatasource-query-execution-error-while-using-sql-public-account/

Goodluck solving you issue! 

Hello Vincent,

If you are able to access the file "locally" but not in a network share, I would say that the first thing to do is to check if the user the SQL server is using is a DOMAIN user, and if it is, if it has access to the path.

Only DOMAIN users can access network paths, and even so, only if they have rights to do it.

Hope you find a solution.

Cheers,
Eduardo Jauch