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
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)
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
Hope this article will help you to solve remote connection enabling disabling from DOS prompt
Leave a Reply