Categories
DBA

Enable Disable Remote connections Via DOS or T-SQL

Ive seen in forums seeking to enable disable Remote connections via DOS prompt T-SQL. Ive searched google for this topic and didnt get any article on this topic, so i decided to write a procedure to change Remote connections via T-SQL so that this can be called from DOS.

Enabling Disabling Remote connections are nothing but changing registry values in HKLMSoftwareMicrosoftMicrosoft SQL ServerMSSQLSERVERSupersocketnetlibProtocol, with respect to the values in the registry it enables are disables the protocol for Remote connection.

You can get my remote connection procedure from the path Click Here

1.) Create the procedure in master db

Eg.) sqlcmd -E -S -ipathremoteconn_dos.sql dmaster

2.) You can enable or disable particular protocol as below using the script
The procedure requires two parameter one is protocol name and another is enable or disable bit

Syntax:

Exec master..remoteconn protocol,1 or 0

where are
np > Named pipes only
tcp > TCPIP only
tcpnp > Both Named pipes and TCPIP
1 > Enable the protocol
0 > Disable the protocol

Eg.) sqlcmd -E -S -QExec master..remoteconn np,1

remote_conn_img1

remote_conn_img2

The above will enable Named pipes protocol

3.) Restart SQL services to make the changes effective

Eg.) net start MSSQLSERVER (for default instance) net start mssql$instancename (for named instance)
net stop MSSQLSERVER (for default instance) net stop mssql$instancename (for named instance)

remote_conn_img3

4.) You can see from the below screenshot that Named pipes protocols have been enabled, since its showing both protocol because TCPIP has been already in enabled state

remote_conn_img4

Hope this article will help you to solve remote connection enabling disabling from DOS prompt

Leave a Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

*