Access Microsoft SQL Server remotely with command prompt — Remote SQLCMD
One of my clients recently had an issue with their Microsoft SQL server which runs on top of Server 2012, we are unable to get the remote connection to the server but while the database installation I have configured the remote access to the database server. That is make so easy to troubleshoot and get the backup to the external drive via remote SQL command prompt.
Here I will explain how we can easily configure the SQL server to allow remote connections from client machine which is available on the same network. In this demo I have SQL server 2019 installed in my laptop.
Allow TCP/IP Connections in the SQL Configuration manager. Open SQL Server configuration manager in the server and open SQL Server Network Configuration. Expand it and find the protocols for your instance, here I am using the default SQLEXPRESS instance.
In the right pane you will find the protocols called TCP/IP, by default status will be set to disabled, open TCP/IP by double click on the name. TCP/IP properties will open in a new window and enable it by set the drop down to Yes to the Enabled field.
To find the established and available port number you can use the netstat -a -n -o command in the command prompt.
Once done, enable the firewall rules for the port used in the previous step and restart the firewall. If you are testing in demo environment, simply turn off the firewall.
Restart the SQL Server Services to apply the changed made in the configuration.
The configuration in the server part has completed now. Below installation must be need in the client machine/s to access the SQL server database.
Client SQL CMD Installation
SQL CMD Utility is a tool with command line interface which helps to access the MS SQL database.
Before install, the SQLCMD utility there are some other prerequires must be installed in order to install the sql cmd successfully otherwise you will encounter some errors as below.
Here it’s requiring Microsoft ODBC Driver 17 for SQL Server, which helps to communicate between database and the applications. It can be downloaded from the following link –
If your windows is not in up to date the following update will be require in order to install the ODBC Driver, update can be downloaded from the below link
Download Visual C++ Redistributable for Visual Studio 2015 from Official Microsoft Download Center
If the client machine fulfilled the above two prerequires you can start installing SQLCMD utility tool in the client machine, SQLCMD can be downloaded from the below link.
Once Command Line utility installed, we can connect to the database using the below command.
sqlcmd -S DESKTOP-0AB9P2O,5068\sqlexpress -U sa -P yourpasswordhere
The format should like this sqlcmd -S [Servername where SQL Installed], [Port Number configured earlier in the SQL Server Configuration Manager to allow remote connections] \ [SQL Server instance name] -U [Username] -P [Password]
Now I am going to take the backup of Datastore database to my external drive (here my external drive is G).
backup database DataStore to disk=’G:\BackupDatastore.bak’
So now without logged into the actual server I have copied the current backup to the external drive, so by configuring this method you can save the data incase if you get any Windows failures. Also by this you can make sure the database is running or not by following any kind of queries remotely.
