Avatar of Jim P.
Jim P.
Flag for United States of America asked on

Disable named pipes with T-SQL

We have multiple hosted clients with each in there own little domain. I'm trying to setup a single DB server to catch reporting from the remote servers. I have to do it via IP.  I want to disable named pipes on the servers when I'm adding the logging script to the individual SQL Servers. Using the configuration GUI is just plain slow.

This is what I have currently to change the "Protocols for <InstanceName>" and the associated registry key.
EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0', 
                               N'ProtocolsSupported', N'REG_MULTI_SZ', N'sm tcp';
GO
--------------------------------------------------------

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0]
"ProtocolsSupported"=hex(7):73,00,6d,00,00,00,74,00,63,00,70,00,00,00,6e,00,70,\
  00,00,00,76,00,69,00,61,00,00,00,00,00
"ProtocolOrder"=hex(7):73,00,6d,00,00,00,74,00,63,00,70,00,00,00,00,00

Open in new window

It is failing with  
-------------------------------------------------------------------------------------------------------
Error executing xp_regwrite extended stored procedure: Registry Type Invalid
-------------------------------------------------------------------------------------------------------

The other for SQL Native Client --> Client Protocols doesn't error, but the registry doesn't change either.
 
EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', 
                     N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Np', 
                     N'Enabled', N'REG_DWORD', 0 ;
GO
--------------------------------------
[HKEY_LOCAL_MACHINE\ SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Np]
"Enabled"=dword:00000000
"PipeName"="\\\\.\\pipe\\MSSQL$UNISON\\sql\\query"
"DisplayName"="Named Pipes"

Open in new window


The reg entries are what it should look like afterwards.

Any ideas?
Microsoft SQL Server 2005Microsoft SQL ServerMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
Jim P.

8/22/2022 - Mon
Aaron Shilo

Jim P.

ASKER
We're going to be touching 50+ hosts. While powershell may be a great option, I barely have a light grasp of it. My other techs none really at all.

So I'm looking for a paste and execute T-SQL script that can be run from a query window. This is one portion of a several hundred lines of code.
ASKER CERTIFIED SOLUTION
Ryan McCauley

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Jim P.

ASKER
Ryan,

Are you sure that remoteconn isn't something out of Red Gate?

I can't find any references to it.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jim P.

ASKER
That is exactly what I was looking for. Googling has everyone just saying ignore the NP. I have had nothing but headaches from them since the day I started doing SQL. Turning them off on five servers using the GUI is not a big deal. Doing it on 40-50+ is another animal.

I can usually build my own routines, but this one was just baffling me.

Thanks for the assistance. May all your days get brighter and brighter.