Access Microsoft SQL Server remotely with command prompt - Remote SQLCMD

Copper Contributor

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.

Gohulan_0-1610979128302.png

 

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.

Gohulan_1-1610979128308.png

 

Then go to IP Addresses tab next to the protocol tab in the same properties window open. You will find number of IP addresses available in the configuration, it’s not a good secure practice to enable all the IP address but here I am going to use the IPALL for remote connection. In the bottom of the windows, you will find IPALL configuration with a default dynamic port. Clear it and set an available port in the TCP Port. I am using 5068 port.

Gohulan_2-1610979128311.png

 

To find the established and available port number you can use the netstat -a -n -o command in the command prompt.

Gohulan_3-1610979128312.png

 

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.

Gohulan_4-1610979128315.png

 

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.

Gohulan_5-1610979128320.jpeg

 

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 –

Download Microsoft® ODBC Driver 17 for SQL Server® - Windows, Linux, & macOS from Official Microsoft...

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.

Download Microsoft® Command Line Utilities 14.0 for SQL Server® from Official Microsoft Download Cen...

 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

Gohulan_6-1610979128323.png

 

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'

Gohulan_7-1610979128325.png

 

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.  

0 Replies