SQLOLEDB always uses Named Pipes

[Dougie Brown]

Hi

We're using SQLOLEDB to connect to a SQL Server 2000 database. One server
and the development machine uses TCP/IP if Network Library=dbnetlib is set in
the connection string, but two servers still use Named Pipes.

The Network Client Utility has been used to enable both TCP/IP and Named
Pipes in that order on all machines but still two use Named Pipes!

If Network Library is set to dbmssocn then all machines connect via TCP/IP.

The documentation states that Network Library should be set to the relevant
dll name without the extension. But dbmssocn is an old dll which I thought
was replaced with dbnetlib and no longer shipped as part of MDAC. What
happens on a clean machine where dbmssocn does exist?

What value should Network Library be set to force the use of TCP/IP?

Why do some machines work with dbnetlib whilst others fail?

I've read that dbnetlib will change the protocol if the first one fails, is
there anyway to log these errors?

Any idea how to troubleshoot this problem?

Thanks for your help!

Cheers

Doug

[Erland Sommarskog]

Post by Dougie Brown
The Network Client Utility has been used to enable both TCP/IP and Named
Pipes in that order on all machines but still two use Named Pipes!
...
I've read that dbnetlib will change the protocol if the first one fails,
is there anyway to log these errors?

Well, what happen if you disable Named Pipes on the troublesome machines?

[Dougie Brown]

I disabled named pipes and it connects using tcp/ip if network library is set
to dbnetlib, dbmssocn or is missing i.e. uses the default value.

I also set network library to dbnmpntw and the connection was opened and
sysprocesses showed that it was indeed using named pipes - how is that
possible if the protocol is disabled?

I didn't think disabling named pipes would be an option as we currently have
a mixture of named pipes and tcp/ip and are trying to migrate to tcp/ip in a
controlled manner!

So I don't understand why named pipes still works even when disabled!

I also cannot find any decent information about the difference between
dbnetlib and dbmssocn. My feeling is that dbmssocn forces the use of tcp/ip
whereas dbnetlib will try to use tcp/ip but it may also use a different
protocol. If this is correct how can you see the reason why it didn't use
tcp/ip?

Also what will happen on a box that does not have dbmssocn.dll installed if
you specify dbmssocn in the connection string?

Thanks for your help.

Cheers

Doug

Post by Erland Sommarskog

Post by Dougie Brown
The Network Client Utility has been used to enable both TCP/IP and Named
Pipes in that order on all machines but still two use Named Pipes!
...
I've read that dbnetlib will change the protocol if the first one fails,
is there anyway to log these errors?

Well, what happen if you disable Named Pipes on the troublesome machines?
--
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Erland Sommarskog]

Post by Dougie Brown
I disabled named pipes and it connects using tcp/ip if network library
is set to dbnetlib, dbmssocn or is missing i.e. uses the default value.
I also set network library to dbnmpntw and the connection was opened and
sysprocesses showed that it was indeed using named pipes - how is that
possible if the protocol is disabled?

Certainly a surprise! I guess the answer is that the Client Network
Utility controls what DBNETLIB can select. If you explicitly pick a
the library for a network, enable/disable does not matter.

Post by Dougie Brown
I also cannot find any decent information about the difference between
dbnetlib and dbmssocn. My feeling is that dbmssocn forces the use of
tcp/ip whereas dbnetlib will try to use tcp/ip but it may also use a
different protocol. If this is correct how can you see the reason why
it didn't use tcp/ip?

DBNETLIB is the "super-socket library". If you search for DBNETLIB
in the Books Online for SQL 2000 you will find some interetsing topics.
One is "Controlling Net-Libraries and Communications Addresses". Among
other things, I found that it says:

When Dbnetlib.dll compares the network protocols enabled on the
application computer against those enabled on the instance of SQL Server
2000, the sequence of the comparison is specified using the Client Network
Utility on the application computer.

Post by Dougie Brown
Also what will happen on a box that does not have dbmssocn.dll installed
if you specify dbmssocn in the connection string?

It did a test and it appears that SQLOLEDB falls back to DBNETLIB. At
least it connects.

[Charles Wang[MSFT]]

Hi Doug,
You may check the ProtocolOrder value in the following registry key to see
if the order is "tcp np":
HKLM\Software\Microsoft\MSSQLServer\Client\SuperSocketNetLib
If the order is right, but your clients still prefer Named Pipes, there
might be a protocol cache in Network Library.

In this case, you may try the following three methods:
1. Disable the Named Pipes protocol in the client machine by cliconfg.exe;
2. Explicitly specify tcp: prefix to your server instance name.
For example:
--specify TCP/IP as the communication protocol
osql /Stcp:CharlesSQL-01 /E

--specify Named Pipes as the communication protocol
osql /Snp:CharlesSQL-01 /E

3. Add an alias with TCP/IP protocol by using cliconfg.exe and use the
alias to connect to your SQL Server.

Hope this helps. Please feel free to let me know if you have any questions
or concerns.
Have a good day!

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications

If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.


Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

[Dougie Brown]

Hi Charles

The order is tcp np.

Disabling Named Pipes in the client configuration utility allowed dbnetlib
to connect via tcp/ip. But if you specified dbnmpntw for the network library
then that also worked and used Named Pipes to connect!

So what exactly does disabling a protocol achieve, what does it do?!

Is prefixing the server with tcp: the preferred means to request a tcp/ip
connection? Or should we be specifying a network library?

Both servers that were using Named Pipes are now happy to use tcp/ip so I
guess it was some kind of cache. But I need a solution that will always use
tcp/ip, so I'm leaning towards the tcp: prefix.

I think the books online is a little weak in this area especially as whether
a network library should be used or avoided and also the value to use to
create a tcp/ip connection. Whilst it mentions that dbnetlib will revert to
another protocol if an error is encountered, it doesn't tell you how you can
find out what error occurred.
Also there's no mention of connection caching - how do you find out this
information?

How do you clear/disable the cache?

This article http://support.microsoft.com/default.aspx?scid=kb;en-us;Q315159
recommends setting the network library to dbmssocn - what happens if that dll
is not there as it's not shipped with later versions of MDAC? As I
understood it had been replaced with dbnetlib. Will this still be a valid
connection string?

Thanks for your help, and sorry for so many questions!

Cheers

Doug

Post by Charles Wang[MSFT]
Hi Doug,
You may check the ProtocolOrder value in the following registry key to see
HKLM\Software\Microsoft\MSSQLServer\Client\SuperSocketNetLib
If the order is right, but your clients still prefer Named Pipes, there
might be a protocol cache in Network Library.
1. Disable the Named Pipes protocol in the client machine by cliconfg.exe;
2. Explicitly specify tcp: prefix to your server instance name.
--specify TCP/IP as the communication protocol
osql /Stcp:CharlesSQL-01 /E
--specify Named Pipes as the communication protocol
osql /Snp:CharlesSQL-01 /E
3. Add an alias with TCP/IP protocol by using cliconfg.exe and use the
alias to connect to your SQL Server.
Hope this helps. Please feel free to let me know if you have any questions
or concerns.
Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

[Charles Wang[MSFT]]

Hi Doug,
Please refer to the following answers.

Post by Dougie Brown
Is prefixing the server with tcp: the preferred means to request a tcp/ip
connection? Or should we be specifying a network library?

Yes, if a client use tcp:<servername\instancename> to connect to your SQL
Server instance. It means that it uses TCP/IP protocol to connect to the
server. The network library is DBNETLIB.

Post by Dougie Brown
Also there's no mention of connection caching - how do you find out this
information?

I did not find any document talking about this issue. It is my guess.
I also guess that the cache may be the value in the registry key:
HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib\LastConnect
Since SQL Native Client has been installed on my computer, I could not
perform simple test by using DBNETLIB; but you may test on your computer by
running "osql /Stcp:<servername\instancename> /E". After you run the
command, you should find that the value of <servername\instancename> in the
registry key should be changed to something like '0:tcp:Charles-03,1433';
then if you connect the server again without specifying tcp: prefix, check
if the registry value would change. If it is still something like
'0:tcp:Charles-03,1433' , it should be the "Cache". Of course, you can
verify that by using named pipes connections.

Post by Dougie Brown
This article

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q315159

Post by Dougie Brown
recommends setting the network library to dbmssocn - what happens if that dll
is not there as it's not shipped with later versions of MDAC? As I
understood it had been replaced with dbnetlib. Will this still be a valid
connection string?

If MDAC was installed on your computer, the DLL should be in the directory
%WINDIR%\system32. If it does not exist on your computer, the usage of
dbmssocn does not work. Actually SQL Server allows you to use the following
network libraries when establishing a connection. They are:
• dbnmpntw - Win32 Named Pipes
• dbmssocn - Win32 Winsock TCP/IP
• dbmsspxn - Win32 SPX/IPX
• dbmsvinn - Win32 Banyan Vines
• dbmsrpcn - Win32 Multi-Protocol (Windows RPC)
You can refer to:
How To Set the SQL Server Network Library in an ADO Connection String
http://support.microsoft.com/kb/238949

Yes, it is still a valid connection string; but I believe that the KB
article that you mentioned is just providing one way to use TCP/IP protocol
by specifying one specific network library, since by default DBNETLIB will
be prior used.

Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Have a good day!

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications

If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.


Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

[Dougie Brown]

Thanks for the information Charles.

I'll be using the tcp: prefix and removing network library from my
connectionstrings.

Cheers

Doug

Post by Charles Wang[MSFT]
Hi Doug,
Please refer to the following answers.

Post by Dougie Brown
Is prefixing the server with tcp: the preferred means to request a tcp/ip
connection? Or should we be specifying a network library?

Yes, if a client use tcp:<servername\instancename> to connect to your SQL
Server instance. It means that it uses TCP/IP protocol to connect to the
server. The network library is DBNETLIB.

Post by Dougie Brown
Also there's no mention of connection caching - how do you find out this
information?

I did not find any document talking about this issue. It is my guess.
HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib\LastConnect
Since SQL Native Client has been installed on my computer, I could not
perform simple test by using DBNETLIB; but you may test on your computer by
running "osql /Stcp:<servername\instancename> /E". After you run the
command, you should find that the value of <servername\instancename> in the
registry key should be changed to something like '0:tcp:Charles-03,1433';
then if you connect the server again without specifying tcp: prefix, check
if the registry value would change. If it is still something like
'0:tcp:Charles-03,1433' , it should be the "Cache". Of course, you can
verify that by using named pipes connections.

Post by Dougie Brown
This article

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q315159

Post by Dougie Brown
recommends setting the network library to dbmssocn - what happens if that

dll

Post by Dougie Brown
is not there as it's not shipped with later versions of MDAC? As I
understood it had been replaced with dbnetlib. Will this still be a valid
connection string?

If MDAC was installed on your computer, the DLL should be in the directory
%WINDIR%\system32. If it does not exist on your computer, the usage of
dbmssocn does not work. Actually SQL Server allows you to use the following
• dbnmpntw - Win32 Named Pipes
• dbmssocn - Win32 Winsock TCP/IP
• dbmsspxn - Win32 SPX/IPX
• dbmsvinn - Win32 Banyan Vines
• dbmsrpcn - Win32 Multi-Protocol (Windows RPC)
How To Set the SQL Server Network Library in an ADO Connection String
http://support.microsoft.com/kb/238949
Yes, it is still a valid connection string; but I believe that the KB
article that you mentioned is just providing one way to use TCP/IP protocol
by specifying one specific network library, since by default DBNETLIB will
be prior used.
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================