Enable \ Disable Remote connections Via DOS or T-SQL
I've seen in forums seeking to enable \ disable Remote connections via DOS prompt \ T-SQL. I've searched google for this topic and didn't 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 "HKLM\Software\Microsoft\Microsoft SQL Server\MSSQLSERVER\Supersocketnetlib\Protocol", 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 -i"path\remoteconn_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 --> TCP\IP only
tcpnp --> Both Named pipes and TCP\IP
1 --> Enable the protocol
0 --> Disable the protocol
Eg.) sqlcmd -E -S -Q"Exec master..remoteconn np,1"
The above will enable Named pipes protocol3.) 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)
4.) You can see from the below screenshot that Named pipes protocols have been enabled, since its showing both protocol because TCP\IP has been already in enabled state
Hope this article will help you to solve remote connection enabling \ disabling from DOS prompt