Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Installing SQL Server Tools to make a Database Connection from Redhat Linux Server


By:   |   Read Comments (7)   |   Related Tips: More > SQL Server on Linux

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


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.

PuTTY Configuartion to connect to a Linux server

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.

entered the login name and password and then pressed Enter to connect to the Linux Server

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

Check mssql-server service on the Linux 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.

Connect to the Linux server using superuser mode

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.

run the curl command to download the installation packages from the Microsoft website to this Linux server

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/

Run the ls -lrt command to display all the files present in that folder

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.

install SQL Server tools

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.

downloading packages to install the SQL Server tools

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*.

Press the Y key to accept the license terms and install SQL Server tools

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.

check status sqlcmd installation

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))

run select @@version in sqlcmd to capture the SQL Server version

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.

create a new database and check the sysdatabases table

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.

check the SQL Server Linux installation from SSMS on a Windows machine

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


Last Update:


signup button

next tip button



About the author
MSSQLTips author Manvendra Singh Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, February 14, 2017 - 9:03:42 AM - Raj Back To Top

 can you please share how the .bash_profile and .bashrc should looks like after installation of sql server tools.

I have followed the steps above but still it says

 

--bash sqlcmd :command not found

 


Thursday, February 09, 2017 - 12:05:14 AM - Manvendra Back To Top

 

I would recommend you to reinstall or update this package again as if it shows as installed then sqlcmd should be recognized by LInux.


Wednesday, February 08, 2017 - 9:57:08 AM - Raj Back To Top

below is the output 

--bash sqlcmd not found..


Wednesday, February 08, 2017 - 9:09:52 AM - Manvendra Back To Top

 

 

Only type sqlcmd and press enter to check what is the output. If it will give details of sqlcmd tht means sqlcmd is installed and ready to use.


Wednesday, February 08, 2017 - 8:04:04 AM - Raj Back To Top

Output of yum info mssql-tools

 

Name: mssql-tools

Arch: x86_64

Version:14.0.3.0

Release:1

Size:846k

repo:installed

From repo: packages-microsoft-com-prod

 

summary:tools for microsoft sql server

 

 

Still it shows sqlcmd --bash sqlcmd not found.


Tuesday, February 07, 2017 - 10:23:13 AM - Manvendra Back To Top

 Run below command to check the package is installed or not. 

yum info mssql-tools


Tuesday, February 07, 2017 - 10:00:01 AM - Raj Back To Top

 Hi, i have installed the Sqltools but after installation as well it shows 

 

--bash sqlcmd not found.

 

Please let me know what could be the issue.

 


Learn more about SQL Server tools