This is going to be a long story I'm afraid ;-(
I have to read some text-files into a SQL database. This database is on a SQL 2008 R2 x64 database server. I want to use a linked server so after some research I found that I had to install the Microsoft.ACE.OLEDB.12.0 x64 provider. I did this on my local machine and after I created my linked server I had some trouble reading text files.
The provider for the linked server was set to use Microsoft.ACE.OLEDB.12.0, the datasource pointed to the correct folder and the providerstring was set to "TEXT", but I could not test the connection. It was throwing this error:
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XXXXXXXXX". OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XXXXXXX" returned message "Unspecified error". (Microsoft SQL Server, Error: 7303)
After some research, I found that the SQL Service was running under the NETWORKING SERVICE-account. When I changed this to Local Account, everything was working fine.
Note: I also had to change some settings for the provider, "Allow inprocess" is checked, otherwise it was not possible to browse the files in the configure datasource folder.
But now I try to implement this solution on my customers network and I cannot get it to work. It is also a SQL 2008 R2 x64 Db-server, Microsoft.ACE.OLEDB.12.0 provider is installed, but it still throwing the mentioned error. So I asked them which account is used to run the SQL Service. This is an AD-account belonging tot the local Administrator group of this server and therefore it had enough privileges as far as I can see. The user who is testing is connected to the database engine with a windows AD account, also belonging to the administrator group. But when he tries to test the connection, it throwing the same error.
An other thing I tried is using the OPENROWSET, this also give an unspecified error. And yes I have set the "Ad hoc distributed query" using the next statement:
sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'Ad Hoc Distributed Queries', 1; GO RECONFIGURE; GO
So here I got my development box, where everything is working fine. But when I deploy to the test-server it has these connection-problems. Actually I don't know what to do or check anymore. I have been working and searching for several days now. Does anyone have some other suggestions I can try?