Client unable to establish connection

[Jen]

Hi,
I get the connection as following:

set objCon = CreateObject("ADODB.Connection")
objCon.ConnectionTimeout = 15
objCon.CursorLocation = adCmdTex
objCon.Open "Provider=sqloledb;Data Source=mainDB;Initial
Catalog=config;User Id=me;Password=pwd;"

for i = 0 to objCon.Errors.Count
sErr = sErr & "Opening con Error " & cstr(i + 1) & ": <"
& objCon.Errors(i).Number & "> " & objCon.Errors
(i).Description & vbCrLf
next

if sErr <> "" then
Response.Write sErr
end if

I got:

Opening con Error 1: <-2147467259> [DBNMPNTW]
ConnectionOpen (CreateFile()). Opening con Error 2: <-
2147467259> Client unable to establish connection

what's wrong

[Bob Barrows]

Jen wrote:

> Hi,
> I get the connection as following:
>
> set objCon = CreateObject("ADODB.Connection")
> objCon.ConnectionTimeout = 15
> objCon.CursorLocation = adCmdTex

This is not a valid setting for CursorLocation, although you may have lucked
out. The valid constants are listed here:
[url]http://msdn.microsoft.com/library/en-us/ado270/htm/mdaenumac_13.asp[/url]

> objCon.Open "Provider=sqloledb;Data Source=mainDB;Initial
> Catalog=config;User Id=me;Password=pwd;"
>

<snip>

> I got:
>
> Opening con Error 1: <-2147467259> [DBNMPNTW]

The "DBNMPNTW" means that the Named Pipes network library is being used to
create the connection to SQL

> ConnectionOpen (CreateFile()). Opening con Error 2: <-
> 2147467259> Client unable to establish connection
>
> what's wrong

You must have an older version of MDAC on your machine since it seems that
it is defaulting to Named Pipes to establish the connection. Which leads to
possibility 1:

Your SQL Server is configured to not use Named Pipes.

You have a few options:

1. Reconfigure SQL Server so it supports Named Pipes
2. Upgrade to the latest MDAC ([url]www.microsoft.com/data[/url]), which will default
to using TCP/IP
3. Specify that TCP/IP should be used in the connection string: "...;network
library=DBMSSOCN"

Possibility 2:
Your SQL Server is configured to only use Windows Security. It needs to use
either mixed or SQL Server security to enable connections using username and
password
[url]http://support.microsoft.com?kbid=253500[/url]

If none of the above fix the problem, see these:
[url]http://support.microsoft.com?kbid=306518[/url]
[url]http://support.microsoft.com/default.aspx?scid=/support/sql/content/connect/ClientIP.asp[/url]

HTH,
Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Jen]

Thank you very much. Now I know more about ado.
how can I check my current MDAC version?

>-----Original Message-----
>Jen wrote:

>> Hi,
>> I get the connection as following:
>>
>> set objCon = CreateObject("ADODB.Connection")
>> objCon.ConnectionTimeout = 15
>> objCon.CursorLocation = adCmdTex

>
>This is not a valid setting for CursorLocation, although

you may have lucked

>out. The valid constants are listed here:
>[url]http://msdn.microsoft.com/library/en-[/url]

us/ado270/htm/mdaenumac_13.asp

>

>> objCon.Open "Provider=sqloledb;Data

Source=mainDB;Initial

>> Catalog=config;User Id=me;Password=pwd;"
>>

><snip>
>

>> I got:
>>
>> Opening con Error 1: <-2147467259> [DBNMPNTW]

>
>The "DBNMPNTW" means that the Named Pipes network library

is being used to

>create the connection to SQL
>

>> ConnectionOpen (CreateFile()). Opening con Error 2: <-
>> 2147467259> Client unable to establish connection
>>
>> what's wrong

>
>You must have an older version of MDAC on your machine

since it seems that

>it is defaulting to Named Pipes to establish the

connection. Which leads to

>possibility 1:
>
>Your SQL Server is configured to not use Named Pipes.
>
>You have a few options:
>
>1. Reconfigure SQL Server so it supports Named Pipes
>2. Upgrade to the latest MDAC ([url]www.microsoft.com/data[/url]),

which will default

>to using TCP/IP
>3. Specify that TCP/IP should be used in the connection

string: "...;network

>library=DBMSSOCN"
>
>Possibility 2:
>Your SQL Server is configured to only use Windows

Security. It needs to use

>either mixed or SQL Server security to enable connections

using username and

>password
>[url]http://support.microsoft.com?kbid=253500[/url]
>
>If none of the above fix the problem, see these:
>[url]http://support.microsoft.com?kbid=306518[/url]
>[url]http://support.microsoft.com/default.aspx?[/url]

scid=/support/sql/content/connect/ClientIP.asp

>
>HTH,
>Bob Barrows
>
>--
>Microsoft MVP - ASP/ASP.NET
>Please reply to the newsgroup. This email account is my

spam trap so I

>don't check it very often. If you must reply off-line,

then remove the

>"NO SPAM"
>
>
>.
>

[Bob Barrows]

Jen wrote:

> Thank you very much. Now I know more about ado.
> how can I check my current MDAC version?
>

There's a tool called Component Checker that is available from the MDAC
site: [url]www.microsoft.com/data[/url]

Bob Barrows


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Jen]

OK, I checked the Properties of the db server. Under
Security tab, the "SQL Server and Windows" is selected. so
possibility 2 is out. Also under General, the version is
7.00.1077.

For possibility 1, where can i configure the server to
allow the Named Pipe?
And I check the MDAC on the web server, it's MDAC 2.5 sp3.


Thanks

>-----Original Message-----
>Jen wrote:

>> Hi,
>> I get the connection as following:
>>
>> set objCon = CreateObject("ADODB.Connection")
>> objCon.ConnectionTimeout = 15
>> objCon.CursorLocation = adCmdTex

>
>This is not a valid setting for CursorLocation, although

you may have lucked

>out. The valid constants are listed here:
>[url]http://msdn.microsoft.com/library/en-[/url]

us/ado270/htm/mdaenumac_13.asp

>

>> objCon.Open "Provider=sqloledb;Data

Source=mainDB;Initial

>> Catalog=config;User Id=me;Password=pwd;"
>>

><snip>
>

>> I got:
>>
>> Opening con Error 1: <-2147467259> [DBNMPNTW]

>
>The "DBNMPNTW" means that the Named Pipes network library

is being used to

>create the connection to SQL
>

>> ConnectionOpen (CreateFile()). Opening con Error 2: <-
>> 2147467259> Client unable to establish connection
>>
>> what's wrong

>
>You must have an older version of MDAC on your machine

since it seems that

>it is defaulting to Named Pipes to establish the

connection. Which leads to

>possibility 1:
>
>Your SQL Server is configured to not use Named Pipes.
>
>You have a few options:
>
>1. Reconfigure SQL Server so it supports Named Pipes
>2. Upgrade to the latest MDAC ([url]www.microsoft.com/data[/url]),

which will default

>to using TCP/IP
>3. Specify that TCP/IP should be used in the connection

string: "...;network

>library=DBMSSOCN"
>
>Possibility 2:
>Your SQL Server is configured to only use Windows

Security. It needs to use

>either mixed or SQL Server security to enable connections

using username and

>password
>[url]http://support.microsoft.com?kbid=253500[/url]
>
>If none of the above fix the problem, see these:
>[url]http://support.microsoft.com?kbid=306518[/url]
>[url]http://support.microsoft.com/default.aspx?[/url]

scid=/support/sql/content/connect/ClientIP.asp

>
>HTH,
>Bob Barrows
>
>--
>Microsoft MVP - ASP/ASP.NET
>Please reply to the newsgroup. This email account is my

spam trap so I

>don't check it very often. If you must reply off-line,

then remove the

>"NO SPAM"
>
>
>.
>

[Bob Barrows]

Jen wrote:

> OK, I checked the Properties of the db server. Under
> Security tab, the "SQL Server and Windows" is selected. so
> possibility 2 is out. Also under General, the version is
> 7.00.1077.
>
> For possibility 1, where can i configure the server to
> allow the Named Pipe?

You need to run the SQL Setup program, but don't bother. Use TCP/IP to
connect, using option 3 below.

> And I check the MDAC on the web server, it's MDAC 2.5 sp3.

I would upgrade it to at least 2.7.

>> 3. Specify that TCP/IP should be used in the connection string:
>> "...;network library=DBMSSOCN"

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.