Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Linked Sever test connection fails

Author  Topic 

Vack
Aged Yak Warrior

530 Posts

Posted - 2013-02-08 : 13:53:46
I have a linked server setup in SQL 2008.

When logged in as administrator I can test the connection and all is well.

When I log in as a different user I get:
The OLE db provider "Microsoft.ACE.OLEDB.12.0 for linked server "PriceDatabase" reported an error. Authentication failed.
Cannot initialiize the data source object of OLE db PROVIDER "Microsoft.ACE.OLEDB.12.0" for linked server 'PriceDataBase'.
OLE DB provider Microsoft.ACE.OLEDB.12.0 for linked server"PriceDatabase" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user. Error 7399

I have tried all kinds of different settings on the Security tab of the linked server and nothing seems to work. logging in as sa still cannot test the connection successfully.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-02-08 : 15:40:05
see if this helps

Be One with the Optimizer
TG
Go to Top of Page

srimami
Posting Yak Master

160 Posts

Posted - 2013-02-08 : 18:17:35
When you log on with other user, it is clear that user do not have enough privileges to access the database. Make sure the user has all access to the database you are trying to access via linked server.
Go to Top of Page

Vack
Aged Yak Warrior

530 Posts

Posted - 2013-02-09 : 10:51:25
TG:
I went through those steps and get the same error.

srimami
I'm linking to an excel spreadsheet. User has full rights to the folder that it is in. This was originally created on Windows server 2003 SQL 2005. Stopped working when we updated to Server 2008 SQL 2008.

Error after doing steps in TG's link:
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "PriceDataBase" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 10
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "PriceDataBase".
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-02-11 : 11:58:37
looks like you've made progress - 7303 is a different error than the first one you reported (7399). did you try googling "error 7303"? When I did some links suggest you didn't set up your linked server with the correct parameters.

According to this you should be using a different provider for linking to Excel:
Microsoft Jet 4.0 OLE DB Provider

Be One with the Optimizer
TG
Go to Top of Page

Vack
Aged Yak Warrior

530 Posts

Posted - 2013-02-11 : 12:46:36
ACE has to be used with 64 bit. It was originally setup to use Microsoft Jet 4.0. When they moved to 64 bit the script didn't work at all until we changed it to Microsoft.ACE.OLEDB.12.0.

The script works fine when logged in as administrator using ACE. I'm convinced its a rights issue, but I just can't find where to add this user.
Go to Top of Page
   

- Advertisement -