QGISからSQL Serverに接続する

　QGISのブラウザパネルにはMS SQL Serverがある．ODBC接続が可能と公式には記述があるが，実際にやってみると手こずることが多かった．今回はQGISからSQL Serverに接続した際の経緯を記述する．

何故か発生するODBC3エラー

　筆者の環境では再現できなくなってしまったが，QGISからSQL Serverに接続する際，ODBC3エラーに悩まされた．調べていくとQGIS側の原因ではなく，SQL Server側の原因だと見当がついた．接続用のログインユーザーを作成する必要があるようだ．

サーバーのSQL Server認証を有効にする

　SQL Serverの認証モードは「Windows認証モード」がデフォルトであるが，これを「SQL Server 認証モードと Windows 認証モード」に変更する．以前は「混合モード」と呼ばれていたものである．

サーバーのプロパティ

　オブジェクトエクスプローラーのサーバーを右クリックして「プロパティ」を選ぶ．

セキュリティ

　「ページの選択」で「セキュリティ」をクリックすると，「サーバー認証」はデフォルトで「Windows 認証モード」となっている．ここを「SQL Server 認証モードと Windows 認証モード」にチェックし直す．

　「構成の変更の一部は，SQL Server を再起動するまで有効になりません．」とのメッセージが出る．ここでOKをクリックして端末を再起動する．

新しいログインユーザーを作成する

　ここで作成するユーザーはQGISがSQL Serverに接続するためのログイン専用ユーザーとして扱うこととする．

全般

　サーバー直下の「セキュリティ」を開き，「ログイン」を右クリックして「新しいログイン(N)…」を選ぶ．

　「ページの選択」は「全般」が最初に表示される．

• 「ログイン名」にユーザー名を入力する．
• WIndows認証(W) にチェックが入っているが，SQL Server認証(S)にチェックし直す．
• 「パスワード(P)」「パスワードの確認入力(C)」にパスワードを入力する．

サーバーロール

　「ページの選択」で「サーバーロール」をクリックする．

• 「サーバーロール(S)」にはpublicにチェックが入っているが，さらにsysadminにもチェックを入れる．

ユーザーマッピング

　「ページの選択」で「ユーザーマッピング」をクリックする．

• 任意のデータベースにチェックを入れる．

既定のスキーマの設定

　デフォルトでは既定のスキーマが空欄になっている．ここでは作成中のログインユーザーと紐づけるデータベースを指定する．

　右端の「…」ボタンをクリックすると「スキーマの選択」ダイアログに遷移する．「参照(B)…」ボタンをクリックする．

　「オブジェクトの参照」ダイアログに遷移する．ここでは「dbo」にチェックを入れてOKをクリックする．

　「スキーマの選択」ダイアログに戻るのでOKをクリックする．

　必要なデータベースに同じ作業を繰り返す．

　「ログイン－新規作成」でOKをクリックしてダイアログを抜ける．

確認

　オブジェクトエクスプローラーで「セキュリティ」「ログイン」をたどると，今作成したQGISUSERというログインユーザーが作成されているのが確認できる．

　SQL Server Management Studioを再起動して最初の「サーバーへの接続」ダイアログで「SQL Server認証」を選び，ログイン(L)にQGISUSERとタイプし，パスワードを入力する．

　パスワードを入力してログインできる．

QGIS

ブラウザパネルでMS SQL Server新規接続を作成

　QGISのブラウザパネルで「MS SQL Server」を右クリックして「新規接続…」を選ぶ．

接続の詳細

　「新規MSSQL接続を作成」ダイアログが開く．ここで設定するのは左側のペイン「接続の詳細」である．

• 「接続の名称」には任意の名称を入力する
• 「プロバイダー/DNS」は空欄のままで良い
• 「ホスト」にはSQL Serverにおけるサーバー名を入力する
• 「信頼された接続」のチェックを外す
• 「ユーザー名」には先刻SQL Serverで作成したログインユーザー名であるQGISUSERと入力する．「保存」をチェックするとユーザー名をQGIS側で保存する
• 「パスワード」にはQGISUSERのログインパスワードを入力する．「保存」をチェックするとパスワードが平文で保存される（環境によってはチェックしないほうが良い場合もあるだろう）

　不思議なもので，一旦接続が確立されてしまうと，「信頼された接続」のチェックをいれても接続できてしまう．これはおそらくWindows認証モードのことと考えられる．環境によっては最初から「信頼された接続」でユーザー名やパスワードを入力せずとも接続可能な場合がある．

データベースの一覧表示

　「新規MSSQL接続を作成」ダイアログ右側のペイン「データベースの詳細」のうち，右上のボタン「データベースの一覧表示」をクリックすると，使用できるデータベースの一覧が表示される．ここでは前回の記事で復元したAdventureWorks2019を選択している．

「レイヤ」「レイヤを追加」「MSSQL Spatialレイヤを追加」

　「レイヤ」メニューから「レイヤを追加」「MSSQL Spatialレイヤを追加」とたどる．

データソースマネージャーMS SQL Server

　データソースマネージャーが開く．「接続」をクリックすると，接続とログインが確立されていれば下図のようにテーブルが見えているはずである．「追加」をクリックしてダイアログを抜ける．

「ブラウザ」「XYZ Tiles」「地理院地図」「レイヤをプロジェクトに追加する」

　そのままでは白い背景しか見えないので下地となる地図を追加する．ここでは地理院地図を追加することにする．ブラウザパネルのXYZ TIlesから地理院地図を右クリックし，「レイヤをプロジェクトに追加する」を選ぶ．

　下図のように表示されれば成功である．

接続に失敗する場合

　大抵の場合，下図のようなエラーメッセージが出て接続がうまく行かない．この原因は分かっていない．端末リセット，ODBC接続，SQL Serverの修復セットアップ，データベースの互換性の変更などを試みたがやはり上手くいかず，現状では環境によるとしか言えない．

リモートクライアントからSQL Serverに接続できない場合

　SQL Serverの稼働しているサーバー内からは問題なく接続できるのに，リモートのクライアントからSQL Serverに接続できない場合がある．

SQL Serverのlistenしているプロトコルの確認

　SQL Serverがlisten（待ち受け）していないプロトコルを利用してクライアントが接続を要求しても両者の接続は成功しない．SQL Serverのエディションによってはセットアップ直後のデフォルト設定では外部からの接続が行えない共有メモリプロトコルのみlistenしている場合がある．

SQL Server構成マネージャ

　SQL Server構成マネージャを起動してSQL Serverがlistenしているプロトコルを確認する．必要なプロトコルが無効化されていたら有効化する．

　SQL Server2019の場合，エクスプローラーで下記のファイルを検索するとSQL Server構成マネージャが起動する．

C:\Windows\SysWOW64\SQLServerManager15.msc

　スタートメニューから SQLServerManager15.msc を検索してもよい．

　「SQL Server ネットワークの構成」を展開すると右側のペインにプロトコル名として「共有メモリ」「名前付きパイプ」「TCP/IP」が列記されており，状態がそれぞれ「有効」か「無効」と表示されている．

　「無効」と表示されているプロトコル名をダブルクリックすると，「有効」が「いいえ」となっているので「はい」に変更する．

　「名前付きプロパティ」も「TCP/IP」も同様に変更する．

SQL Serverの再起動

　オブジェクトエクスプローラーでサーバーを右クリックして「再起動」を選ぶとSQL Serverが再起動し，上記の設定変更が反映される．

ファイヤーウォールにSQL Serverのポートを通す

　SQL Server構成マネージャーからTCP/IP接続を有効にしたら，今度はSQL Server本体をWindowsファイヤーウォールの例外に登録する必要がある．SQL Serverを他の端末に公開するにはこの2つを同時に行わないといけない．いずれか一方が欠けていても他の端末からSQL Serverに接続することはできない．

　Windowsファイヤーウォールの例外にSQL Serverを登録するには，Windows11の場合「設定」「ネットワークとインターネット」を開く．続いて「ダイヤルアップ」タブを開き，下部にある「関連設定」のうち「Windowsファイヤーウォール」をクリックする．

　「ファイアウォールとネットワークの保護」の設定画面が開く．「ファイヤーウォールによるアプリケーションの許可」をクリックする．

　「許可されたアプリおよび機能」ウィンドウが開く．右上の「設定の変更」をクリックし，右下の「別のアプリの許可」ボタンをクリックする．

　「アプリの追加」ウィンドウが開く．「参照」ボタンをクリックする．

　SQL Serverの実行本体である「sqlservr.exe」をSQL ServerのインストールパスのBinnフォルダから選択する．

　「アプリの追加」ウィンドウにSQL Servreが追加されたので「追加」ボタンをクリックする．

　「許可されたアプリおよび機能」にSQL Serverが追加されている．

まとめ

　QGISからSQL Serverに接続する手順を記載した．SQL Server側でQGISがログインするためのユーザーを作成する必要があった．

　QGISの「レイヤを追加」「MSSQL Spatialレイヤを追加」での処理がどの環境でも成功するには至っておらず，今後の課題である．