原文地址：https://blog.dbi-services.com/installing-the-odbc-drivers-for-microsoft-sqlserver-for-linux/
原文作者：Cesare Cervini

• 本文是包括SQLite、Postgresql、Firebird、Oracle RDBMS、HSQLDB、MariaDB、MongoDB和Excel在內的系列文章之一。如果目標是建立一個獨立的環境，用來測試此處介紹的gawk上ODBC擴展是否完成的話，請參閱 SQLite 上安裝所需的ODBC驅動程序管理器。
  測試系統版本：debian v11 (bullseye)

以root用戶身份進入后，可按照此處的文檔獲取并安裝適用于Linux的sqlserver express。以下是需要用到的操作步驟：

wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -

add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/20.04/mssql-server-2019.list)"

安裝mssql數據庫軟件

apt-get install -y mssql-server

配置systemd服務

/opt/mssql/bin/mssql-conf setup

1. 查看systemd服務是否正常啟動（參數“–no-pager”表示“不使用分頁器來顯示查詢后的輸出”）：

systemctl status mssql-server --no-pager

輸出如下，則表示systemd服務已啟動

● mssql-server.service - Microsoft SQL Server Database Engine
     Loaded: loaded (/lib/systemd/system/mssql-server.service; enabled; vendor preset: enabled)
     Active: active (running) since Fri 2021-08-27 15:22:14 CEST; 15s ago
       Docs: https://docs.microsoft.com/en-us/sql/linux
   Main PID: 7795 (sqlservr)
      Tasks: 120
     Memory: 880.5M
        CPU: 5.983s
     CGroup: /system.slice/mssql-server.service
             ├─7795 /opt/mssql/bin/sqlservr
             └─7817 /opt/mssql/bin/sqlservr

2. 獲取并安裝SQLServer的ODBC驅動程序：

apt install tdsodbc

apt install curl

curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list

apt-get update 

apt-get install mssql-tools unixodbc-dev

3. 檢查設備是否已在系統范圍內添加：

odbcinst -q -d

odbcinst -q -d -n "ODBC Driver 17 for SQL Server"

4. 在debian上，使用本地管理工具sqlcmd創建sampledb數據庫：

$ sqlcmd -S localhost -U SA -P admin2021!
CREATE DATABASE sampledb
go
1> SELECT Name from sys.Databases;
2> go
Name                                                                                                                            
-----------
master                                                                                                                          
tempdb                                                                                                                          
model                                                                                                                           
msdb                                                                                                                            
sampledb                                                                                                                        
 
(5 rows affected)

5. 填充sampledb數據庫;

此處提供了用于為slqserver創建表的語句，以及在此處填充表的語句。
單擊并將文件分別保存到create_tables_mssql.sql和populate_tables_mssql.sql。
使用默認命令行管理工具sqlcmd執行上述SQL腳本：

sqlcmd -S localhost -U SA -P admin2021! -i create_tables_mssql.sql

sqlcmd -S localhost -U SA -P admin2021! -i populate_tables_mssql.sql

檢查一下數據：

cat - <<eot | sqlcmd -S localhost -U SA -P admin2021!

USE sampleDB
SELECT
        c.country_name,
        c.country_id,
        l.country_id,
        l.street_address,
        l.city
FROM
        countries c
LEFT JOIN locations l ON l.country_id = c.country_id
WHERE
        c.country_id IN ('US', 'UK', 'CN');
eot

Changed database context to 'sampledb'.
country_name                         country_id country_id   street_address                           city                 
---------------------------------------- ---------- ---------- ---------------------------------------- ---------------------
China                                    CN         NULL       NULL                                     NULL                          
United Kingdom                           UK         UK         8204 Arthur St                           London                        
United Kingdom                           UK         UK         Magdalen Centre, The Oxford Science Park Oxford                        
United States of America                 US         US         2014 Jabberwocky Rd                      Southlake                     
United States of America                 US         US         2011 Interiors Blvd                      South San Francisco           
United States of America                 US         US         2004 Charade Rd                          Seattle                       
 
(6 rows affected)
SQL> 

6. 通過編輯用戶的DSN（Data Source Name，數據來源名稱）文件來配置ODBC DSN：

vi ~/.odbc.ini

7. 檢查DSN：

odbcinst -q -s

結果如下：

odbcinst -q -s -n mymssqlserverdb

8. 使用ODBC驅動程序管理器測試工具isql通過ODBC嘗試連接到mssql db：

isql -v mymssqlserverdb SA admin2021!
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+

SELECT c.country_name, c.country_id, l.country_id, l.street_address, l.city FROM countries c LEFT JOIN locations l ON l.country_id = c.country_id WHERE c.country_id IN ('US', 'UK', 'CN');
Output:
+-----------------------------------------+-----------+-----------+-----------------------------------------+---------------+
| country_name                            | country_id| country_id| street_address                          | city          |
+-----------------------------------------+-----------+-----------+-----------------------------------------+---------------+
| China                                   | CN        |           |                                         |               |
| United Kingdom                          | UK        | UK        | 8204 Arthur St                          | London        |
| United Kingdom                          | UK        | UK        | Magdalen Centre, The Oxford Science Park| Oxford        |
| United States of America                | US        | US        | 2014 Jabberwocky Rd                     | Southlake     |
| United States of America                | US        | US        | 2011 Interiors Blvd                     | South San Francisco           |
| United States of America                | US        | US        | 2004 Charade Rd                         | Seattle       |
+-----------------------------------------+-----------+-----------+-----------------------------------------+---------------+
SQLRowCount returns 6
6 rows fetched

9. 使用python模塊pyodbc測試DSN：

python3
import pyodbc 
cnxn = pyodbc.connect(DSN='mymssqlserverdb;UID=SA;PWD=admin2021!')
cursor = cnxn.cursor()  
cursor.execute("""SELECT
        c.country_name,
        c.country_id,
        l.country_id,
        l.street_address,
        l.city
FROM
        countries c
LEFT JOIN locations l ON l.country_id = c.country_id
WHERE
        c.country_id IN ('US', 'UK', 'CN')""")
row = cursor.fetchone() 
while row:
    print (row) 
    row = cursor.fetchone()
Output:
('China', 'CN', None, None, None)
('United Kingdom', 'UK', 'UK', '8204 Arthur St', 'London')
('United Kingdom', 'UK', 'UK', 'Magdalen Centre, The Oxford Science Park', 'Oxford')
('United States of America', 'US', 'US', '2014 Jabberwocky Rd', 'Southlake')
('United States of America', 'US', 'US', '2011 Interiors Blvd', 'South San Francisco')
('United States of America', 'US', 'US', '2004 Charade Rd', 'Seattle')

現在可以從debian帳戶下的任何ODBC應用程序訪問到mssqlserver。
其他數據源的說明可以通過以下鏈接訪問：
SQLite
Firebird
HSQLDB
MariaDB
PostgreSQL
Oracle
MongoDB
Excel