Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Mar 14 '13 at 13:19

This question came from our site for professional and enthusiast programmers.

    
Changing from domain account to local account my service account..Fixed my issue. –  user28645 Sep 24 '13 at 21:14
    
Hi have the Fix for this problem, really is easy, you only must to install el SP1 y SP2 of SQL Server 2008 R2 and thas off –  user29513 Oct 17 '13 at 22:35

2 Answers 2

When you set up a linked server, you have the choice to re-use the credentials of the user, use a specific account or be anonymous. When you tested it from the same machine, and SQL Server was running as a local account, it worked because it was able to re-use the admin privileges of you while you were logged-in.

To get it to work in a network scenario, things get tricky. If you are using AD, you need to use AD (principalName) to configure the Kerberos trust between the SQL Server machine and the machine hosting the MSAccess file. This usually takes some fiddling to get it right. This TechNet article is long-winded, but contains a lot of config steps and suggestions: http://support.microsoft.com/kb/319723.

Also, you will need to set the server back to use the NETWORKING SERVICE account again so you can grant it permissions. On the other machines, your SQL Server will be using a hidden account like [domain]\[MachineName]$. If the MS Access DB is on the same machine as your SQL Server, you still need to grant (NTFS) read/write permissions on the MSAccess DB (file) to the accounts for the SQL Server process and to the network users.

share|improve this answer
    
Thanks TimG. I definitely will have a look at this. Hope to get it to work. Oh, and BTW you talk about Ms Access, but in my case I just have to access TXT-files in a map on the SQL-server. –  Stephan Mar 14 '13 at 22:29

Run Management Studio as Administrator.

share|improve this answer
    
Does this really answer the question, @Igor? –  ypercube Dec 24 '13 at 11:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.