I'm having an issue to execute an ad hoc query using opendatasource from a SQL2000(let's call it SQL2000M) to SQL2005(let's call it SQL2005R) server.
Using MS SQL Server Managment connected to the SQL2000M server, i'm executing:
select count(*) from OPENDATASOURCE('SQLOLEDB','Data Source=SQL2005R\MYINSTANCE;User ID=sqluser;Password=sqluserpwd').mydb.dbo.mytable
it's return the error:
[OLE/DB provider returned message: Connection failure]
[OLE/DB provider returned message: [DBNETLIB]]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize returned 0x80004005: ].
Msg 7399, Level 16, State 1, Line 2
OLE DB provider 'SQLOLEDB' reported an error.
However, if i change the userID to "sa", it's work.
Also if i'm executing the same query from another SQL2000 Server (let's call him SQL2000P) it's work , with sqluser account (and sa too of course).
I believe it's permission issue but how set this permission so that i can run this query with sqluser account from SQL2000M server ?
Thanks in advance,