SEO

In the process of doing some routine monitoring/clean-up we've
discovered that several (many?) users are apparently set to access our
SQL Server 2000 database instances via the Named Pipes protocol. In
readings and recommendations we've decided that our WAN would be best
served if we use the less "chatty" TCP/IP.

As such we've also decided to try to enforce this decision to use
TCP/IP exclusively using the domain login script used by all of our
end-users.

Question: does anyone know what registry entries are created/used to
indicate that TCP/IP is enabled and is the default protocol for SQL
Server 2000? Our environment is: XP Pro SP2 and SQL Server 2000
(typically SP3).

TIA
Glenn - newbie DBA

(glenn.owens@gmail.com) writes:
> In the process of doing some routine monitoring/clean-up we've
> discovered that several (many?) users are apparently set to access our
> SQL Server 2000 database instances via the Named Pipes protocol. In
> readings and recommendations we've decided that our WAN would be best
> served if we use the less "chatty" TCP/IP.
>
> As such we've also decided to try to enforce this decision to use
> TCP/IP exclusively using the domain login script used by all of our
> end-users.
>
> Question: does anyone know what registry entries are created/used to
> indicate that TCP/IP is enabled and is the default protocol for SQL
> Server 2000? Our environment is: XP Pro SP2 and SQL Server 2000
> (typically SP3).

The brutal variant would be to open the Server Network Utility on
the server box, and disable Named Pipes (server restart needed, as I
recall.)

On the clients it could be a good thing to use the Client Network
Utility to disable Named Pipes, or put it lower in priority than
TCP/IP.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Erland, I appreciate the input but we're not looking at wiping out the
use of named pipes all-together (there may be background processes and
legacy applications which use/require the protocol). We're hoping only
to disable Named Pipes for End-Users who login via the default domain.
So, the question still stands...

Question: does anyone know what registry entries are created/used to
indicate that TCP/IP is enabled and is the default protocol for SQL
Server 2000? Our environment is: XP Pro SP2 and SQL Server 2000
(typically SP3).


Erland Sommarskog wrote:
> (glenn.owens@gmail.com) writes:
> > In the process of doing some routine monitoring/clean-up we've
> > discovered that several (many?) users are apparently set to access
our
> > SQL Server 2000 database instances via the Named Pipes protocol. In
> > readings and recommendations we've decided that our WAN would be
best
> > served if we use the less "chatty" TCP/IP.
> >
> > As such we've also decided to try to enforce this decision to use
> > TCP/IP exclusively using the domain login script used by all of our
> > end-users.
> >
> > Question: does anyone know what registry entries are created/used
to
> > indicate that TCP/IP is enabled and is the default protocol for SQL
> > Server 2000? Our environment is: XP Pro SP2 and SQL Server 2000
> > (typically SP3).
>
> The brutal variant would be to open the Server Network Utility on
> the server box, and disable Named Pipes (server restart needed, as I
> recall.)
>
> On the clients it could be a good thing to use the Client Network
> Utility to disable Named Pipes, or put it lower in priority than
> TCP/IP.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp

(gowens@nixonpeabody.com) writes:
> Erland, I appreciate the input but we're not looking at wiping out the
> use of named pipes all-together (there may be background processes and
> legacy applications which use/require the protocol). We're hoping only
> to disable Named Pipes for End-Users who login via the default domain.
> So, the question still stands...
>
> Question: does anyone know what registry entries are created/used to
> indicate that TCP/IP is enabled and is the default protocol for SQL
> Server 2000? Our environment is: XP Pro SP2 and SQL Server 2000
> (typically SP3).

I'm still not sure what you are asking for. Your question make it
seem like you want to do this on the server level, but this appears
to be impossible, since you want to keep Named Pipes for some clients.

Thus, your only option is to change this at the clients. The supported
way to do this, is to use the Client Network Utility.

If you want to hack the registry yourself, it appeats that the relevant
values and keys are under
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Client\SuperSocketNetLib
The value Protocol Order appears to be the priority order, and should
thus have "tcp" in the beginning. Under the subkey Tcp, there should
be a REG_DWORD value DefaulPort with the value of 0x00000599 (1433.)

I recommend you to play around from the Client Network Utility a little
more, before you start any remote registry hacking or whatever you are
planning to do.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Erland, what I'm suggesting is that we do NOT do this at the server
level but rather on a per user basis using our standard login script.

We have several hundred end-users in geographically different
locations. Virtually none of the users have access to, or have
knowledge of, the SQL Client Utility. So, the idea is that when they
logon to their PCs a scriptlet will be run which will disable Named
Pipes and/or insure that TCP/IP is the default protocol.

I was hoping that someone could suggest/provide such a script or
specify the WIN XP registry key/values that define the protocol, etc.

I hope that this clears thing up.

Thanks

Erland Sommarskog wrote:
> (gowens@nixonpeabody.com) writes:
> > Erland, I appreciate the input but we're not looking at wiping out
the
> > use of named pipes all-together (there may be background processes
and
> > legacy applications which use/require the protocol). We're hoping
only
> > to disable Named Pipes for End-Users who login via the default
domain.
> > So, the question still stands...
> >
> > Question: does anyone know what registry entries are created/used
to
> > indicate that TCP/IP is enabled and is the default protocol for SQL
> > Server 2000? Our environment is: XP Pro SP2 and SQL Server 2000
> > (typically SP3).
>
> I'm still not sure what you are asking for. Your question make it
> seem like you want to do this on the server level, but this appears
> to be impossible, since you want to keep Named Pipes for some
clients.
>
> Thus, your only option is to change this at the clients. The
supported
> way to do this, is to use the Client Network Utility.
>
> If you want to hack the registry yourself, it appeats that the
relevant
> values and keys are under
>
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Client\SuperSocketNetLib
> The value Protocol Order appears to be the priority order, and should
> thus have "tcp" in the beginning. Under the subkey Tcp, there should
> be a REG_DWORD value DefaulPort with the value of 0x00000599 (1433.)
>
> I recommend you to play around from the Client Network Utility a
little
> more, before you start any remote registry hacking or whatever you
are
> planning to do.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp

(gowens@nixonpeabody.com) writes:
> I was hoping that someone could suggest/provide such a script or
> specify the WIN XP registry key/values that define the protocol, etc.

My post contained some hints in this direction, and my hope was that
you could work from this suggestion. If you want something ready of
the shelf, I'm afraid that I don't have any thing in store.

A quick search lead me to:
http://msdn.microsoft.com/library/de...bnetlib_22.asp

There you find some more information about the registrty entries.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp