SQL Server from Ubuntu

2018-02-05 SQL Linux Andrew B. Collier

Setting up the requisites to access a SQL Server database from Ubuntu.

Install

  1. Add key.
    curl -fsSL https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
  2. Add the location of the repository.
    # For Ubuntu 16.04.
    #
    curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list | sudo tee /etc/apt/sources.list.d/mssql-tools.list
    #
    # For Ubuntu 17.10.
    #
    curl https://packages.microsoft.com/config/ubuntu/17.10/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list
    #
    # For Ubuntu 18.04.
    #
    curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list
  3. Update the package list.
    sudo apt-get update
  4. Install packages.
    sudo apt-get install -y unixodbc-dev
    #
    # For Ubuntu 16.04.
    #
    sudo ACCEPT_EULA=Y apt-get install -y mssql-tools
    #
    # For Ubuntu 17.10 & 18.04.
    #
    sudo ACCEPT_EULA=Y apt-get install -y msodbcsql17
  5. (Optional) Add PATH=$PATH:/opt/mssql-tools/bin as the last line of /etc/bash.bashrc.

Test

Connect to your server.

sqlcmd -S {server_address} -U {user_id} -P {password}
1> SELECT @@VERSION
2> GO

What driver was installed?

cat /etc/odbcinst.ini
[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.0.so.1.1
UsageCount=1

You’ll need the driver string (in this case ODBC Driver 17 for SQL Server) if you are connecting to the server from R.

library(odbc)

db <- dbConnect(odbc(),
                Driver =   "ODBC Driver 17 for SQL Server",
                Server =   "{server_address}",
                Database = "{database_name}",
                UID =      "{user_id}",
                PWD =      "{password}")

odbcClose(db)
Next: Installing DataGrip on Ubuntu.
Previous: Installing rJava on Ubuntu.