SQL Server from Ubuntu

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 -
  1. Add the location of the repository.
curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list | sudo tee /etc/apt/sources.list.d/mssql-tools.list
  1. Update the package list.
sudo apt-get update
  1. Install the mssql-tools and unixodbc-dev packages.
sudo ACCEPT_EULA=Y apt-get install -y mssql-tools
sudo apt-get install -y unixodbc-dev
  1. 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)

Categorically Variable