Link to home
Start Free TrialLog in
Avatar of Laurent .
Laurent .Flag for Viet Nam

asked on

[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,
Avatar of lcohan
lcohan
Flag of Canada image

"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.
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.
Avatar of Laurent .

ASKER

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.
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.
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
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
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.
ASKER CERTIFIED SOLUTION
Avatar of Laurent .
Laurent .
Flag of Viet Nam image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
no other comment