Jim P.
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.
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------
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.
The reg entries are what it should look like afterwards.
Any ideas?
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
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"
The reg entries are what it should look like afterwards.
Any ideas?
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ryan,
Are you sure that remoteconn isn't something out of Red Gate?
I can't find any references to it.
Are you sure that remoteconn isn't something out of Red Gate?
I can't find any references to it.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
http://technet.microsoft.com/en-us/library/dd206997.aspx