SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Linked Sever test connection fails
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Vack
Constraint Violating Yak Guru

USA
484 Posts

Posted - 02/08/2013 :  13:53:46  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
5914 Posts

Posted - 02/08/2013 :  15:40:05  Show Profile  Reply with Quote
see if this helps

Be One with the Optimizer
TG
Go to Top of Page

srimami
Posting Yak Master

160 Posts

Posted - 02/08/2013 :  18:17:35  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

USA
484 Posts

Posted - 02/09/2013 :  10:51:25  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
5914 Posts

Posted - 02/11/2013 :  11:58:37  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

USA
484 Posts

Posted - 02/11/2013 :  12:46:36  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000