Python3 SQL Server 2016 接続設定
動機
Python3 からリモートの SQL Server 2016 に接続したい。
PythonからSQL Serverに接続する際に情報が少なくて調べるのが大変だったのでまとめた。 ローカルホスト上にSQL Serverを立てて接続する方法などはたくさん出てくるがリモートサーバへの接続やWindows以外のOSからの接続方法などは断片的な情報しか出てこない。
環境
サーバー
- Windows 10
- SQL Server 2016
クライアント
SQL Server・クライアントPCそれぞれの現状
SQL Serverの状況
- リモートのSQL Serverは正常に動作
- SQL Serverへのローカルからの接続は成功
クライアントPCの状況
サーバとクライアントのネットワーク状況
問題
接続用のスクリプト
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)')
ここからでは何が原因かわからないため、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 接続設定
リモートからの接続の許可
リモートからの接続を許可にチェックを入れる
TCP/IPによる接続を許可
SQL Server Configuration Managerを起動する。
SQL Server Configuration Manager は日本語では SQL Server 構成マネージャー。
[SQL Server ネットワークの構成] >> [SQL EXPRESSのプロトコル] を選択。
右ペインのTCP/IP を右クリックして、プロパティを選択。以下のようなポップアップが開く。
Enabledを"はい"に変更。[IPアドレス]タブを選択。
一番下の [IP ALL] の欄の[TCP port] の値を1433 にする。別に他の値でも良いが、Windows SQL Serverのデフォルトポートは1433。
適用ボタンをクリック。設定を適用するためにはサービスの再起動が必要。
左ペインで [SQL Serverのサービス] を選択。右ペインの [SQL Server] を右クリックして[再起動] を選択。サービスの再起動が行われる。
これで、SQL Server的にはTCP/1433 でコネクションが張れるようになった。
Windowsはデフォルトでファイアウォールが設定されているので、次にポートの開放を行う必要がある。
Windows Firewall の設定
Windows Firewallを起動する。名前はちょと違うかもしれない（手元にWindows環境がないので確認できない）。
[新規の受信規則] を選択して新たなルールを追加する。
ポートベースで許可を行う。プログラムベースの方がスマートかもしれない。
TCPにチェックを入れる。[特定のローカルポート]の値を1433にする。先程違うポート番号を設定した場合はそれを入れる。
接続を許可するにチェックを入れる。
ここは自分のポリシーに従って。今回は全部許可にした。
今作成しているファイアウォールのルールに名前をつける。適当に。
SQL Serverの認証設定
ここまでで接続自体はできるようになっていて欲しいが、できない。SQL Serverには"Windows 認証" と "SQL Server 認証"の２つのユーザ認証方式をサポートしている。
Windows認証の仕組みはどうなっているのかよくわからないが、ユーザ名だけで認証できるようだ。今回は、ユーザ名とパスワードによって認証を行う一般的な認証を行いたい。従って、SQL ServerでSQL Server認証を許可しなければならない。
環境構築の段階で設定していればここは飛ばして良い。
[SQL Server認証モードとWindows認証モード] にチェックを入れ、[適用] をクリック。
ここでもサービスを再起動する必要があるので、先程同様にSQL Serverのサービスを再起動する。
この時点で、接続自体はできるようになった。
> sqlcmd -S 10.0.0.124 -U user1 Password: 1>
プロンプトに 1> と表示され、接続できていることが確認できた。
