【Windows環境設定】会社･自宅のSQL Server Expressのリモート接続を有効化しよう

1 はじめに
2 SQL Server Expressリモート接続有効手順概要
3 Windows 10 PC-Aでの作業
4 Windows 10 PC-Bでの作業
- 4.1 SSMSのインストール
- 4.2 Windows 10 PC-BのSSMSからWindows 10 PC-A SQL Serverへの接続試験
5 想定される問題の原因と対策
6 おわりに

はじめに

Access から SQL Server へのアップサイジングを検討する際

関連WebサイトやYoutube動画で触れる情報は殆ど

AccessからSQL Serverにアクセスする際は、
まずAccessのインストールされているPCにSQL Server をインストールし、同一PC内でAccess-SQL Server接続を試みましょう｡

何故なら､最初からAccessと違うPCにSQL Serverをインストールし、このAccessと別のPCのAccess間でAccess-SQL Server接続を試みて失敗したとき、初学者には何が問題か切り分けができないので。

といった説明では無いでしょうか。

これは本当にその通りなのですが

【Windows環境設定】会社･自宅のSQL Server Expressのリモート接続を有効化しよう — **みなみ**

ところで、どうしたらSQL Serverをリモート接続可能にできるの？

ただ次の段階として、SQL Serverを実際にリモート接続可能にしたいと思ったとき

それらについてはなかなかまとまった情報がないのも事実かと思います。

そこで本記事では

【Windows環境設定】SQL Server ExpressとSSMSを会社・自宅のパソコンに構築しよう

でWindows 10搭載PCｰA(IP Address: 192.168.aaa.10)に構築したSQL Server Expressを

下記プライベートネットワーク内別Windows 10搭載PCｰB(IP Address: 192.168.bbb.119)にインストールしたSQL Server Management Studio(以降SSMSと略記)から

接続可能にするための設定手順について詳しく紹介します。

この手順はWindows Server 2019 Essentials上にインストールしたSQL Server Expressの場合でも全く同一です。

SQL Server Expressリモート接続有効手順概要

SQL Server Expressをサーバ側としてリモート接続を有効する手順は以下の通りです｡

SQL Server Express のリモート接続の有効化
SQL Server Express のTCP/IP有効化と再起動
SQL Servise Brouserの有効化と再起動
Windows Defender ファイアーウォール設定

これらの設定が完了したらクライアント側のWindows 10 PC上で

SSMSインストール
SSMSからSQL Server Essential への接続確認

を順に進めましょう｡

Windows 10 PC-Aでの作業

SQL Server Express のリモート接続の有効化

まずSQL Server ExpressのインストールされているWindows 10 PC-Aで

SSMSを起動し､左側にあるオブジェクトエクスプローラの最上部の(サーバー名をぼかしている)接続しているSQL Server名を右クリックし、現れたコンテキストメニューから
プロパティ(R)
を選択します。

続けて左側の「接続」をクリックして、右側に現れる

□このサーバヘのリモート接続を許可する(A)

の左側の□にチェックを入れ「OK」をクリックして、このSQL Server Expressへのリモート接続を有効化します。

SQL Server Express のTCP/IP有効化と再起動

次に

Windowsメニュー >Microsoft Server 2022 > SQL Server 2022 構成マネージャー

からSQL Server 2022 構成マネージャーを開き

左側で

SQL Serverネットワークの構成 > SQLEXPRESSのプロトコル

を順にクリックします。

そして現れた TCP/IP を右クリック後のコンテキストメニューで プロパティ(R) を選択します｡

ここに現れる

TCP/IPのプロパティ

画面で IPアドレス タブをクリックし

右側のスライドバーを一番下まで下げて現れる

IPALL

のTCPポートの値にSQL Serverのデフォルトポート番号に 1433 を記入して OK ボタンをクリックします。

そして以下のダイアログが現れたら内容を確認して OK をクリックします｡

この IPAll はこのダイアログ画面上で IPAll の前に出現していたIPv4のIPアドレス(例えば169.254.54.4)や､IPv6のIPアドレス(例えばfe80::36a2:a92f:e33c:6ecf%3)を含むすべてのIPアドレスを指す予約語です。

つまり全てのIPアドレスから、TCPポート1433でのアクセスを許容するという意味です｡

逆に言うと、このSQL Server Express がインストールされているPC、サーバが複数の Network Interface Card(NIC) を持っている場合は、それぞれのNICカードに対しIPアドレスが付与されるので

個々のNICカード毎に受信ポートを設定することもできるということですね。

次にSQL Server Express を再起動するため、再度

Windowsメニュー >Microsoft Server 2022 > SQL Server 2022 構成マネージャー

からSQL Server 2022 構成マネージャーを開き

左側の

SQL Serverのサービス

を選択してから、右側で SQL Server(SQLEXPRESS) を右クリックし

現れるコンテキストメニューから 再起動(T) を選択します｡

すると画面に以下のようなダイアログが現れ

しばらくすると画面上で SQL Server(SQLEXPRESS) の状態が 実行中 に戻るので

これでSQL Server Express の再起動ができたことになります。

SQL Servise Brouserの有効化と再起動

SQL Server Express ではSQL Servise Brouserサービス自体が停止状態になっているので

続いてこの SQL Servise Brouser サービスを有効化します。

具体的には先ほど同様にSQL Server 2022 構成マネージャーを開き､左側の

SQL Serverのサービス

を選択してから、右側の SQL Server Browser を右クリックし

現れるコンテキストメニューから プロパティ(R) を選択します。

そして SQL Serverのプロパティ画面で サービス タブをクリックし

開始モード を右クリックして現れるコンテキストモードで自動を選択し

OK をクリックします。

続いて先ほどの

SQL Serverのサービス

を選択してから、右側で SQL Server Browser を右クリックし

現れるコンテキストメニューから開始(S) を選択します。

すると先ほどの画面で SQL Server Browser の状態が 実行中 となります。

これで SQL Server Browser がサービス実行中となりました。

SQL Server Browserサービスとは

この SQL Server Browserサービスは

クライアントアプリケーション､つまりリモートPC上のSSMSやAccess等がネットワーク上のSQL Serverインスタンス(つまり稼働中のSQL Server)を見つけ、その接続を支援するための
サービスで

デフォルトでUDPポート1434を使用し

一般に次のようなプロトコルシーケンスで
クライアントアプリケーションとSQL Server間の接続を支援します。

SQL Server Browserサービスのプロトコルシーケンス

接続情報要求: クライアントは SQL Server Browser サービスに接続情報を要求します。
接続情報応答: SQL Server Browser サービスはクライアントに接続情報を提供します。この情報には、利用可能な SQL Server インスタンスの名前、IP アドレス、およびポート番号が含まれています。
接続要求: クライアントは、受け取った接続情報を使用して、SQL Server インスタンスに接続を要求します。
接続応答: SQL Server インスタンスはクライアントに接続応答を送信し、接続を確立します。

Windows Defender ファイアーウォール設定

ところで
ここで検討している下記プライベートネットワーク構成では

SQL Server Browserサービスのプロトコルシーケンス に示されるように

Windows 10 PC-A 内のWindows Defender ファイアーウォール設定で

PC-A 外のプライベートネットワークからの

SQL Server Express (TCPポート1433を使用)
SQL Server Browser (UCPポート1434を使用)

パケットを受信許容する必要があります。

従って

Windowsの設定 > 更新とセキュリティ ＞ Windowsセキュリティ

から呼び出される

ファイアーウォールとネットワーク保護 > 詳細設定

から開く

セキュリティが強化された Windows Defender ファイアウォール

画面から

前述の２つのポートに対し

以下のような受信規則を新規作成する必要があります。

そしてこのファイアーウォール設定を有効化するため

一度Windows 10 PC-A を再起動します。

Windows 10 PC-A を再起動しないとこのWindows Defender ファイアーウォールの設定変更は有効にならないので注意が必要です。
またこの再起動後、SQL Server Browserサービスが無効になる場合があるので、念のためSQL Server 2022 構成マネージャーできちんと実行中状態になっているか確認しましょう。

Windows Defender ファイアーウォール の詳細な設定方法については

必要に応じ、以下の記事も参照頂くといいかもしれません。

【自宅VPN構築】#13 Windows 2019 Server Essentials 搭載 PC で OpenVPN Serverを立ち上げよう

Windows 10 PC-Bでの作業

SSMSのインストール

ここではまず最初に
Windows 10 PC-A にインストールされた SQL Management Express で
リモート接続設定が正しく行われているかを確認するため

Windows 10 PC-BにSSMSをインストールします｡

このWindows 10 PC-BにSQL Server Management Studio(SSMS) をインストールする手順については以下の関連記事に記載のSSMSインストール手順手順と全く同一です。

必要に応じてこちらも参照してください。

Windows 10 PC-BのSSMSからWindows 10 PC-A SQL Serverへの接続試験

続いて、いよいよWindows 10 PC-BのSSMSからWindows 10 PC-A SQL Serverへの接続試験を行います。

具体的には

Windows メニュー > Microsoft SQL Server Tools 19

から

SQL Server Management Studio

を起動すると

次のログイン画面が出てくるので

サーバー名の入力画面の右にある▽アイコンをクリックすると

下に <参照…> という文字が出てくるので､これを選択します｡

次に サーバの参照 画面で ネットワークサーバ タブを選択すると

しばらくしてこの画面が以下のように変化するので

左側の＋をクリックします。

すると少し待って SQL Server をインストールしたサーバ名が現れるので、これをクリック

続けて OK ボタンをクリックします。

すると

もとのログイン画面にSQL Server のインスタンス名が表示されるので

認証(A): SQL Server 認証
ログイン(L): SQL Server構築時に決めたSQL Server 認証時のログイン名
パスワード(P):SQL Server構築時に決めたSQL Server 認証時のパスワード

を設定して 接続(C) ボタンをクリックします｡

全てがうまくいくと

いい感じで SSMS が PC-A の Windows Server に接続できます。

この サーバの参照画面 からネットワーク上のSQL Serverのインスタンスを表示する仕組みは､ SQL Server Browser サービスにより行われているようですね。

従って、SQL Serverのインスタンスを正しく表示できていれば、逆にこの SQL Server Browser サービスの設定は正しく行われていることがわかります｡

想定される問題の原因と対策

ここではSQL Server のリモート接続設定を行うにあたり想定される問題の原因と対策についてまとめておきます。

問題が発生したら必要に応じてレベル番号の小さなものから順に確認しましょう。

レベル番号	条件	確認事項	想定される問題	原因と対策
1	同一のWindows 10 PCに Windows Server Express と SSMS をインストール	SSMSから Windows Server Expressへ SQL Server認証を使ってログイン可能	SQL Server認証を使ってログインできない	Windows Server Express内で認証設定方式設定、SQL Server認証時のユーザーアカウント設定が正しく設定されていないと考えられるためまずはWindows認証でログインし、それらを設定
2	同一LAN上(つまり同一サブネット内)のWindows 10 PC-AにWindows Server Express、 Windows 10 PC-Bに SSMS をインストール	Windows 10 PC-BのSSMSから Windows 10 PC-AのWindows Server ExpressへSQL Server認証を使ってログイン可能	Windows 10 PC-BのSSMSでログイン時のサーバの参照で SQL Server Expressのインスタンスがきちんと見えない	Windows 10 PC-A内のファイアーウォールでSQL Server BrowserのUDPポート1434､SQL ServerのTCPポート1433の受信設定が正しく設定されていないあるいは設定後Windows 10 PC-Aを再起動していない可能性があるので再度ファイアーウォールでこれらの受信設定を再確認し、必要に応じてWindows 10 PC-A を再起動する
				Windows 10 PC-A内のSQL Server のTCP/IP サービスが有効になっていない可能性があるので再度TCP/IP サービスの有効有効化手順を再確認､再設定して必要に応じてSQL Server Express を再起動する
				Windows 10 PC-A内のSQL Server Browser サービスが実行中でない可能性があるので再度SQL Servise Brouserの有効化手順を再確認し、必要に応じてSQL Server Express を再起動する
3	LAN1上のWindows 10 PC-AにWindows Server Express、 LAN2(つまりルータをはさんだ別サブネット)上のWindows 10 PC-Bに SSMS をインストール	Windows 10 PC-BのSSMSから Windows 10 PC-AのWindows Server ExpressへSQL Server認証を使ってログイン可能	Windows 10 PC-BからWindows 10 PC-A､あるいはその逆方向ヘのping応答が無い｡	これは Windows 10 PC-A と Windows 10 PC-B 間に挟まるルーター、ファイアーフォール等のルーティング、ポートフォワード、受信ポリシー設定等の問題なのでこれらをまず解決する
			Windows 10 PC-BのSSMSでログイン時のサーバの参照で SQL Server ExpressのインスタンスでWindows 10 PC-Aのドメイン名がきちんと見えない	これはActive Directory 等におけるDNSの設定の有無の問題なのでもともとDNSの設定がされていなければ気にする必要なくIP Addressで参照、指定が可能
			プライベートネットワークではログインできるが、パブリックネットワークではログインできない	これはWindows 10 PC-A内のファイアーフォールの受信ポリシーに対する公開設定の問題なのでセキュリティ条件を前提に必要に応じて見直す。但しパブリックネットワークへの公開はセキュリティ上のリスクを増大させるためLAN1とLAN2間にOpenVPN等による暗号化されたセキュアな通信路を確立しておくことが大前提