ninigi.log

作業ログ。ネットワーク, python, RoR, Reactなど。

Python3 SQL Server 2016 接続設定

やりたいこと

  • Python3 からリモートの SQL Server 2016 に接続する

動機

PythonからSQL Serverに接続する際に情報が少なくて調べるのが大変だったのでまとめた。

ちょっと調べるとローカルホスト上にSQL Serverを立てて接続する方法などはたくさん出てくるがリモートサーバへの接続やWindows以外のOSからの接続方法などは断片的な情報しか出てこない。

本記事では、 ubuntu 18.04 python 3.6.8 を用いて SQL Server 2016 に接続する方法について書いていく。

設定前の状況

  • SQL Serverは動作しており、ローカルからの接続 (SQL Studio 使用) は成功している
    • ただし、認証にはWindows認証を用いている(認証については後述)
  • クライアントPCには、pythonのモジュールである pyodbc がインストール済み
  • クライアントPCには、コマンドラインツール sqlcmd もインストール済み (上記の過程でインストール済み)
  • ping によってIP的には通信ができていることは確認済み
  • Pythonpyodbcにおけるドライバーのエラーは解決済み(他の記事に書く)

以上のような状況で、接続に失敗する。

Pythonからの試行

接続用のスクリプト

conn = pyodbc.connect('DRIVER={/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.4.so.1.1};'
                                      'Server=10.0.0.124;'
                                      'UID=user1;'
                                      'PWD=pass;'
                                      'Database=master;'
                                     )

このスクリプトを実行時のエラー

Traceback (most recent call last):
  File "dbtest.py", line 7, in <module>
    conn = pyodbc.connect('DRIVER={/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.4.so.1.1};'
pyodbc.OperationalError: ('HYT00', '[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')

IPアドレス、UID、PWDは合っていることは確認済み。

ここからでは何が原因かわからないため、CLIからコマンドを叩く。

CLIからの試行

接続試行用のコマンド

> sqlcmd -S 10.0.0.124 -U user1
Password:

コマンド実行時のエラー

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : TCP Provider: Error code 0x102.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

接続自体ができていないということがわかった。Windows Server側の設定が必要らしいのでこれについてまとめていく。

SQL Server 接続設定

リモートからの接続の許可

f:id:ninigishi:20190911155413p:plain リモートからの接続を許可にチェックを入れる

TCP/IPによる接続を許可

SQL Server Configuration Managerを起動する。 f:id:ninigishi:20190911155537p:plain

SQL Server Configuration Manager は日本語では SQL Server 構成マネージャー。

f:id:ninigishi:20190911155738p:plain

[SQL Server ネットワークの構成] >> [SQL EXPRESSのプロトコル] を選択。

右ペインのTCP/IP を右クリックして、プロパティを選択。以下のようなポップアップが開く。

f:id:ninigishi:20190911160024p:plain

Enabledを"はい"に変更。[IPアドレス]タブを選択。

f:id:ninigishi:20190911160211p:plain

一番下の [IP ALL] の欄の[TCP port] の値を1433 にする。別に他の値でも良いが、Windows SQL Serverのデフォルトポートは1433。

適用ボタンをクリック。設定を適用するためにはサービスの再起動が必要。

f:id:ninigishi:20190911160535p:plain

左ペインで [SQL Serverのサービス] を選択。右ペインの [SQL Server] を右クリックして[再起動] を選択。サービスの再起動が行われる。

これで、SQL Server的にはTCP/1433 でコネクションが張れるようになった。

Windowsはデフォルトでファイアウォールが設定されているので、次にポートの開放を行う必要がある。

Windows Firewall の設定

Windows Firewallを起動する。名前はちょと違うかもしれない(手元にWindows環境がないので確認できない)。

[新規の受信規則] を選択して新たなルールを追加する。

f:id:ninigishi:20190911161139p:plain

ポートベースで許可を行う。プログラムベースの方がスマートかもしれない。

f:id:ninigishi:20190911161213p:plain

TCPにチェックを入れる。[特定のローカルポート]の値を1433にする。先程違うポート番号を設定した場合はそれを入れる。

f:id:ninigishi:20190911161332p:plain

接続を許可するにチェックを入れる。

f:id:ninigishi:20190911161402p:plain

ここは自分のポリシーに従って。今回は全部許可にした。

f:id:ninigishi:20190911161434p:plain

今作成しているファイアウォールのルールに名前をつける。適当に。

SQL Serverの認証設定

ここまでで接続自体はできるようになっていて欲しいが、できない。SQL Serverには"Windows 認証" と "SQL Server 認証"の2つのユーザ認証方式をサポートしている。

Windows認証の仕組みはどうなっているのかよくわからないが、ユーザ名だけで認証できるようだ。今回は、ユーザ名とパスワードによって認証を行う一般的な認証を行いたい。従って、SQL ServerSQL Server認証を許可しなければならない。

環境構築の段階で設定していればここは飛ばして良い。

f:id:ninigishi:20190911163005p:plain

[SQL Server認証モードとWindows認証モード] にチェックを入れ、[適用] をクリック。

ここでもサービスを再起動する必要があるので、先程同様にSQL Serverのサービスを再起動する。

この時点で、接続自体はできるようになった。

> sqlcmd -S 10.0.0.124 -U user1
Password:
1>

プロンプトに 1> と表示され、接続できていることが確認できた。

pythonで冒頭のスクリプトを叩いてもエラーが発生しないことが確認できた。