By: Manvendra Singh | Updated: 2017-01-25 | Comments (7) | Related: > SQL Server on Linux
Problem
Microsoft has released its first public preview for the next version of SQL Server on November 16, 2016. I have explained step by step method to install SQL Server Next version on Redhat Linux in my last tip. Here I will show you how to install SQL Server tools like sqlcmd to make a database connection from a Linux box on Redhat Linux 7.2 version.
Solution
Microsoft has released its first public preview for the next version of SQL Server on Linux. This version of SQL Server does not install sqlcmd and BCP utility along with the SQL Server installation. We need the sqlcmd utility to make a database connection from Linux, so in this tip I will show you the installation of these tools to make database connections from Linux machines or PUTTY.
The next version of SQL Server on Linux will include all the standard SQL Server management features that come with the Windows version. These include advanced features like Always-Encrypted, Row-Level Security and Columnstore Indexes, but a lot of features are still not supported in this preview like SQL Server Agent, Replication, AlwaysON, etc. You can get the full list of features which are not supported and known issues at this Microsoft link: SQL Server on Linux Release Notes.
SQL Server Tools Installation on Redhat Linux 7.2
Before going ahead, you need to have a Linux box with the next version of SQL Server installed on that machine. I have followed the step by step directions from this tip. Now let's start the SQL Server tools installation on a Linux box.
Step 1: First connect to the target Linux server on which you have installed SQL Server and where you will install the SQL Server tools for this tip. You can directly connect to the server through or you can use PuTTY which is a third party tool to make a Linux server connection. I used PuTTY to login to this server. I launched PuTTY and entered the IP address of the Linux server and then clicked on the Open button as shown in the picture below.
Once you click on the Open button, a black screen with appear asking you to enter the login name followed by the password. I entered the login name and password and then pressed Enter to connect as per the below screenshot.
Step 2: You can see that I have made successful connection to the Linux server linux4mssql where I installed SQL Server in my last tip. Now let's check and verify the SQL Server installation on this machine. Run the below command to check the mssql-server service.
#check the status of mssql-server service. systemctl status mssql-server
We can see service status is showing enabled and active (running) which is highlighted in green which means SQL Server is installed and running fine on this Linux server.
As we cannot make a SQL Server database connection on a GUIless Linux version without the sqlcmd utility, so we need to install it on this machine to access the SQL Server instance.
Step 3: Again, we will use superuser mode su for the next step. Superuser mode in Linux is similar to the system administrator account permission in Windows. So connect using superuser mode by running the below command.
#Connect with superuser permission sudo su
You can see that i have been connected to the superuser account in the below screenshot.
Step 4: Now we will run the curl command to download the installation packages from the Microsoft website to this Linux server. The downloaded file will be saved in /etc/yum.repos.d/ as shown in the command line.
#Download installation configuration file. curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/msprod.repo
We can see the progress, time and details of the downloaded configuration file in the image below.
Now we can check the location /etc/yum.repos.d/ to verify whether the above file is saved in this location or not. Run the ls -lrt command to display all the files present in that folder.
#list all files/folders inside the yum.repos.d folder. ls -lrt /etc/yum.repos.d/
You can see our target file msprod.repo file is there in this location in the above picture. You can exit the superuser mode here if you wish by typing exit followed by pressing Enter.
Step 5: Now our next step is to install the SQL Server tools on this box with the help of this package. We will use yum package to install it as we do for other applications on Linux. Run the below command to install the SQL Server tools on this machine.
#Install SQL Server vNext tools sudo yum install mssql-tools
You can run this command from your home location as well although I prefer to run it from the yum.repos.d folder. Once you press Enter to execute above command, the execution process will start with checking some dependencies followed by downloading the required packages. Once the dependencies are resolved, all required packages will be displayed on this screen.
Now it will ask you to choose yes or no to proceed with the installation. Type y to choose yes. Once you press enter all three packages will be downloaded and installation will start automatically with asking you to accept the license terms.
Every package will ask you to enter YES to accept the license terms during installation as shown in the above screenshot. Enter YES for each particular package to install them one by one on your machine. Once installation is completed you will get the below screen with the name of installed product and its status as compete. We can see the installed product is shown as mssql-tools.x86_64*.
Step 6: Finally the SQL Server tools have been installed on your Linux machine. Now we will check and verify whether the sqlcmd utility is installed to access SQL Server on this machine or not. Type sqlcmd and press enter to check the details of this utility.
#check sqlcmd utility. sqlcmd
We can see that now sqlcmd command is recognized by Linux and the output of this command is showing that sqlcmd is installed to use.
Step 7: Next we will validate sqlcmd by making a database connection from the Linux box. Run the below command to connect to the database engine from the Linux server.
#Run sqlcmd to make database connection. #You need to pass server name/IP address with -S, user name with -U and password with -P. sqlcmd -S 192.168.209.128 -U SA -P 'XXXXXXX'
Once you execute the above command, SQL Server prompt will appear with cursor 1>. I executed the "SELECT @@VERSION" T-SQL command to check the version of the installed SQL Server and received the below output:
#I executed SELECT @@VERSION to get version details of SQL Server. I got below output: Microsoft SQL Server vNext (CTP1) - 14.0.1.246 (X64) Nov 1 2016 23:24:39 Copyright (c) Microsoft Corporation on Linux (Red hat Enterprise Linux Server 7.2 (Maipo))
Step 8: After checking the SQL Server version I will create a database named "Manvendra" and checked database name in the sysdatabases system table by running a general query.
#Create a database named "Manvendra" and check database name in sysdatabases system table. #Run this command on sqlcmd prompt only. CREATE DATABASE Manvendra GO SELECT name from sysdatabases GO
We can see we have 5 databases in the output including our newly created "Manvendra" database.
Step 9: Now we will validate whether this newly created database is accessible through SQL Server Management Studio (SSMS) on a Windows box or not. Launch SSMS on your Windows server and connect to the Linux server using IP address 192.168.209.128. You can use sa login and password to make the database connection. Once you make a database connection, run the sp_helpdb command to check the database details. I executed the same and have the below output.
Now we have validated that we can connect to the SQL Server vNext database engine either using sqlcmd from Linux or through SSMS, so go ahead and play with this instance to learn more about SQL Server on Linux.
Next Steps
- Go ahead and learn about these new options for the SQL Server world.
- Stay tuned for my next tips focused on SQL Server on Linux.
- Explore more knowledge on SQL Server Database Administration Tips.
About the author
Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2017-01-25
