Linked Server on SQL Cluster?

GoTroppo
GoTroppo used Ask the Experts™
on
We are linking a SQL2K server to an Access DB to source data from a third party application. We have set this up no problem on a normal SQL2K server - works like a treat.

However, when we attempted to set this up on a SQL 2K Cluster (our Production Environment) any attempt to connect to the AccessDB fails with what appears to be a permissions error (unfortunately I don't have the error on me as I'm currently offsite).

Does anyone know of any special problems with Linked Servers under SQL Clusters?

Cheers, Mark B

29th November, 2002 - I recently updated the question with the latest information. Any ideas folks?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
pegasysIT, System Admin, Development and Stack Development

Commented:
Permissions? Are the 2 servers on the same domain? If not create a trust relationship for the 2 servers between the domains. and tada...

Other than that, please be a little mroe specific about the error. It's kinda like calling up the doctor, saying I'm sick, and not explaining the problen :/

Regards
P

Commented:
Cluster or not is the same... You must look in the SQL configuration or in the network architecture. Something is different in your Production Environment.

Max

Commented:
Cluster or not is the same... You must look in the SQL configuration or in the network architecture. Something is different in your Production Environment.

Max
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Does this happen during setup?Or when the cluster failsover? Are you running an active/passive or active/active config? Is the linked Access database on the one drives of the server? It could be a driving mapping affected by the drive config of the cluster. There are certain drives on our active\active cluster that can not be seen depending on which instance you are working with.
Melissa

Author

Commented:
Ok, I finally have some more information regarding the problem.

The error we receive is as follows:

     Error 7399: OLEDB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.

Nothing more. No errors can be found anywhere on the system and certainly nothing in the Event Log.

Now, we've also run some additional checks. On the same SQL Cluster, we added an additional linked server pointing at a different AccessMDB on a different host - worked fine.

I also configured a DTS job that used the MDB in question (the one that is not working as a linked server) as a source and then opened a Transformation. This allows you to see the list of Tables in the Database - and yes, they are all visible.

So I am at a loss. Linked Servers work ok to a different MDB, I can see the MDB and communicate to it using DTS, and we have a test SQL server that happily sees and talks to the MDB in question as a linked server.

It has to be something simple we've missed. Any ideas folks?
GoTroppo:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
Commented:
Sorry for the delay but we ended up finding the problem. Microsoft actually has this listed as a known problem and basically it has to do with security - you have to use the "Be made using this security context" and the username = Admin with a NULL password. Apparently this is the norm for Access DB's.

Once we did this it worked just fine.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial