Enabling Remote connections via DOS or T-SQL

How to use the script

1.) Create the procedure in master db
2.) Use the command as below

Exec master..remoteconn protocol, 1 or 0 

where protocol refers

np – NamedPipes only

tcp – TCPIP only

tcpnp – Both TCPIP and NamedPipes

Eg.) Exec master..remoteconn np,1

This will enable Named piped in Remote connection, if you provide 0 it will disable named pipes in remote connections

For more information about refer the article Enable Disable Remote connections Via DOS or T-SQL

Script

CREATE PROCEDURE remoteconn
@val VARCHAR(5),--tcp to enable tcp connection, np to enable np connection, tcpnp to enable tcp & np connection
@enable BIT --enable  disable bit
AS
DECLARE @inspath VARCHAR(10),@regw VARCHAR(100),@ke VARCHAR(100),@ke1 VARCHAR(100)
DECLARE @insname VARCHAR(25)
IF CONVERT(VARCHAR(25),SERVERPROPERTY('instancename')) IS NULL
SET @insname='MSSQLSERVER'
ELSE
SET @insname=CONVERT(VARCHAR(25),SERVERPROPERTY('instancename'))
EXEC master.dbo.xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key='SoftwareMicrosoftMicrosoft SQL ServerInstance NamesSQL',
@value_name=@insname,
@value=@inspath OUTPUT
SET @regw='SoftwareMicrosoftMicrosoft SQL Server'+@inspath+'MSSQLSERVERSuperSocketNetLib'
IF @val='np'
BEGIN
SET @ke=@regw+@val+''
END
ELSE
IF @val='tcp'
BEGIN
SET @ke=@regw+@val
END
ELSE
IF @val='tcpnp'
BEGIN
SET @ke=@regw+'tcp'
SET @ke1=@regw+'np'
IF @enable=1
BEGIN
EXEC master.dbo.xp_regwrite @rootkey='HKEY_LOCAL_MACHINE',
@key=@ke1, @value_name='Enabled', @type='REG_DWORD',@value=1
END
ELSE
BEGIN
EXEC master.dbo.xp_regwrite @rootkey='HKEY_LOCAL_MACHINE',
@key=@ke1, @value_name='Enabled', @type='REG_DWORD',@value=0
END
END
IF @enable=1
BEGIN
EXEC master.dbo.xp_regwrite @rootkey='HKEY_LOCAL_MACHINE',
@key=@ke, @value_name='Enabled', @type='REG_DWORD',@value=1
END
ELSE
BEGIN
EXEC master.dbo.xp_regwrite @rootkey='HKEY_LOCAL_MACHINE',
@key=@ke, @value_name='Enabled', @type='REG_DWORD',@value=0
END