What is this error????

[justastef]

Here is some background on the situation at hand: I'm working on a project with these two servers: MIDT and MIDQ. Those servers were linked together about 2 weeks ago. I am logging into both servers as the system administrator. user name has same permissions on both servers.

Here is a definition of the problem. I need to be able to perform a select statement across servers. Up until two days ago, I was able to perform such a query… yesturday I came in and I could perform a query from MIDQ to MIDT, but not the opposite way around. So for instance, when logged into MIDT, I performed the query select * from midq.master.dbo.syscolumns and the error message below came up…

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IUnknown::QueryInterface returned 0x80004005: The provider did not give any information about the error.].

Well THANK YOU error message for being so EXPLANATIONAL!!!!! At first I thought something was wrong with the linked server connection… it has been dropped and recreated many times since this situation arose to no avail.

Service packs were updated over 2 months ago so its not that either... what is wrong with this situation? Like I said I dropped and recreated the linked server and remote server connections for both servers. Why am I all of a sudden only able to perform a query when logged onto MIDQ to MIDT? Does anyone have any solutions as to how to correct or approach this situation????

[techgnome]

check the login..... when connected to one DB/server, and trying to query another, it passes the authentication along with it... so if the user that you are logged on as doesn't have rights, you'll get errors...
That would be the first thing to look at.

[justastef]

Since it works selecting from one side to the other, would you say that the authentication problem is on MIDQ or MIDT. I can select from MIDQ to MIDT but not from MIDT to MIDQ.... So the problem lies with permissions in MIDT right?

[techgnome]

That's what I was thinking.... although, I could be way off base... but it's something to look at...

[justastef]

I wanted to see if this was a permissions thing... it is not. I set up two identical new users on both sides giving full access to database master along with system administrator status. Attempted to perform a query for MIDT to MIDQ on master yielded the above error message once again.

[justastef]

I also just created another linked server connection from scratch between MIDT and a server name MIDT2. A query from MIDT to MIDT2 yielded the same error message.