[OLE/DB provider returned message: Connection failure] [DBNETLIB]

Hi,

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

Open in new window

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. 

Open in new window


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,
Laurent .Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
"However, if i change the userID to "sa", it's work." - looks like whatever <<userID>> you refer to does not have sufficient rights to perfrom the operation and please compare this <<userID>> with the sqluser account from SQL2000M server.

If that sqluser account from SQL2000M server IS part of the sysadmins Server role then you must make the <<userID>> account from SQL2000M server as sysadmin as well.
0
Eugene ZCommented:
make sure you have latest Sql SP installed

and try to login directly on this SQL2005R\MYINSTANCE server -db mydb.dbo.mytable

using :User ID=sqluser;Password=sqluserpwd'

---
Did you try to use linked server instead?

please check

Remarks from this link https://msdn.microsoft.com/en-us/library/ms179856.aspx
OPENDATASOURCE can be used to access remote data from OLE DB data sources only when the DisallowAdhocAccess registry option is explicitly set to 0 for the specified provider, and the Ad Hoc Distributed Queries advanced configuration option is enabled. When these options are not set, the default behavior does not allow for ad hoc access.
The OPENDATASOURCE function can be used in the same Transact-SQL syntax locations as a linked-server name. Therefore, OPENDATASOURCE can be used as the first part of a four-part name that refers to a table or view name in a SELECT, INSERT, UPDATE, or DELETE statement, or to a remote stored procedure in an EXECUTE statement. When executing remote stored procedures, OPENDATASOURCE should refer to another instance of SQL Server. OPENDATASOURCE does not accept variables for its arguments.
Like the OPENROWSET function, OPENDATASOURCE should only reference OLE DB data sources that are accessed infrequently. Define a linked server for any data sources accessed more than several times. Neither OPENDATASOURCE nor OPENROWSET provide all the functionality of linked-server definitions, such as security management and the ability to query catalog information. All connection information, including passwords, must be provided every time that OPENDATASOURCE is called.
1
Laurent .Author Commented:
Hi Guys,

Thanks for your feedback.
All 2 servers had SQL 2000 SP3.

The problem has been solved after remove the server from the domain and re-register it again.
Not sure what went wrong exactly, but no permission has been modified, no other server configuration has been done or else.

After make it work, i seen that a SP4  has been released so i upgrade it, and it still working, the The solution work for SP3 and SP4.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Laurent .Author Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for lolodu's comment #a41296684

for the following reason:

The solution, was related to OS/windows AD , not to SQL.
0
Eugene ZCommented:
I still see no connection to your "solution"  " remove the server from the domain and re-register it again."

you are using Sql login: it means your server could be in "workgroup" instead of AD and it would work
The sql 2000 sp4 was released in 2012
and it is the step #1 to address this issue
thus, I do not see this as solution for your case "remove the server from the domain and re-register it again"
but sql 2000 sp4 as solution
0
Laurent .Author Commented:
Dear EugeneZ,

As i said, i stop having the connection error "[OLE/DB provider returned message: Connection failure] [DBNETLIB]" right after rebooting the server which is required after remove and add it again to the domain.

I did the upgrade to SP4 several hours after the connection error has been solved.

My understanding of the solution, if i may have one, is that it was pure network issue, not an AD nor SQL permission one.
Either the network setting at OS level has been damaged (registry) either the SQL Client and/or Server Network setting may also been the source of the trouble.
The fact to remove and register again the host to the domain may reset properly some of those network settings.

Regards
Laurent
0
Eugene ZCommented:
thanks for your post:  it may be right if not this part "However, if i change the userID to "sa", it's work.'

if you had issues with network ..etc -- it would not work

thus, solution could be one of the posted above..  Only 1 of your activities may help to "fix"
it is  "reboot"..
As it was said "whatever works"

and one more about ":[OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].
PRB: Error 7399 When You Run a Linked Server Query That Uses the OLE DB Provider for Microsoft Jet
https://support.microsoft.com/en-us/kb/814398


Cause
This problem occurs because the login account does not have full access to the temporary folder of the SQL Server startup account. The linked server query runs in the context of the login account. If you run a linked server query, SQL Server tries to create a temporary file data source name (DSN) in the temporary folder of the SQL Server startup account.
Workaround
To work around this problem, follow these steps:
Log on to the computer by using the SQL Server start up account.
Create a folder named Temp in the operating system installation directory.
Permit full access to a non-administrator account on the Temp folder.
Set the value of the TEMP and TMP user variables of the SQL Server startup account to the newly created Temp folder. To do so, follow these steps:
Right-click My Computer, and then click Properties.
Click the Advanced tab, and then click Environmental Variables.
In the User variables for Logon User list, click TEMP, and then click Edit.
In theVariable Value box, type C:\Temp as the location of the new Temp folder, and then click OK.
Repeat steps c and d to set the value of the TMP variable.
Click OK two times.
Log off, and then log on to the computer by using SQL Server startup account.
Restart the SQL Server services.
0
Laurent .Author Commented:
"it may be right if not this part "However, if i change the userID to "sa", it's work.'
if you had issues with network ..etc -- it would not work "
I do recon that and totally agree with you. That's why this solution looks weird and i can't found a precise cause to this error.

But the fact is, believe me or not, I didn't get this error message anymore right after the reboot performed after the re-registration of the server.

About your link to "Provider 'Microsoft.Jet.OLEDB.4.0'" , not sure it can be from any help since my linked server and the Ad Hoc connection (opendatasource and openquery) used SQLOLEDB provider.

If you think that this solution can't be from any help to other, please feel free to delete the whole post.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Laurent .Author Commented:
no other comment
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.