SE の雑記

SQL Server をインストールした後に外部の端末から接続をできるようにするための一連の設定になります。
今までまとめたことが無かったので。

■構成マネージャーの設定

エディションにもよるのですがデフォルトでは [TCP/IP] が無効になっていることがあります。

SQL Server 構成マネージャーから対象のインスタンスの [TCP/IP] を有効にします。
インスタンス単位で設定する必要があるので複数のインスタンスを実行している場合は各インスタンスに対して設定を行います。

設定が終了したら SQL Server のサービスを再起動すると TCP/IP による接続が許可されます。

■ファイアウォールの設定

外部からの接続を許可するためにファイアウォールで SQL Server が使用するポートを許可する必要があります。

SQL Server では、[既定のインスタンス] と [名前付きインスタンス] の 2 種類のインスタンスがありインスタンスの種類によって許可をするポートが変わってきます。

既定のインスタンスの場合は

の 二種類を許可することで外部から接続ができます。

以下のコマンドでファイアウォールを設定することができます。

netsh advfirewall firewall add rule name="SQL Server" protocol=TCP dir=in localport=1433 action=allow
netsh advfirewall firewall add rule name="SQL Server (DAC)" protocol=TCP dir=in localport=1434 action=allow

SQL Server では専用管理者接続 (DAC) という外部から接続ができないときにでも使用することができる専用の管理者接続が用意されています。

デフォルトではこの専用管理者接続は SQL Server のサービスを起動している端末からのみ接続ができます。

# 127.0.0.1 で TCP 1434 のポートをリスニングしています。

リモートからも DAC で接続ができるようにするためには、[remote admin connections] という設定を有効にする必要があります。

この設定はクエリで有効にします。有効にしたら SQL Server のサービスを再起動するとリモートから DAC で接続をすることができます。

sp_configure 'remote admin connections', 1
RECONFIGURE

既定のインスタンスに接続をする場合には最低限、TCP 1433 をファイアウォールで許可する必要があります。

# TCP 1434 はリモートで DAC を接続する場合のみ設定をする必要があります。

名前付きインスタンスの場合には解放するポートが変わってきます。

名前付きインスタンスは 2 種類のポートの設定方法があります。

名前付きインスタンスではポートの設定が [動的ポート] と [固定ポート] の 2 種類があります。

名前付きインスタンスはデフォルトでは [動的ポート] の設定となっており、サービスを起動するたびにポートが変わります。

そのため、既定のインスタンスのようにポート番号を指定してファイアウォールを設定することができません。

動的ポートが設定されているインスタンスのファイアウォールを設定するためには以下のように設定をします。

netsh advfirewall firewall add rule name="SQL Server Browser" protocol=UDP dir=in localport=1434 action=allow
netsh advfirewall firewall add rule name="SQL Server (SQL2012)" program="C:Program FilesMicrosoft SQL ServerMSSQL11.SQL2012MSSQLBinnsqlservr.exe" dir=in action=allow

動的ポートに設定されている SQL Server に接続する場合、以下のようにインスタンス名を指定した場合は、

1. SQL Server Browser サービス (UDP 1434) に接続をし、対象のインスタンスのポート番号を接続元に返す
2. 返されたインスタンスのポート番号を使用して SQL Server に接続

という流れで接続が行われます。

最初の処理を完了させるために、UDP 1434 をファイアウォールで許可する必要があります。

その後の SQL Server に接続をするのですが、ポート番号がサービスに起動のたびに変わってしまうため、特定のポート番号でファイアウォールを設定することはできません。

この場合、対象のインスタンスの SQL Server のプログラムをファイアウォールで許可します。

許可する設定は以下のようになります。

この設定をすることで動的ポートで実行されている SQL Server の接続をファイアウォールで許可することができます。

この場合、DAC のポートも動的に変更されることになりますが、SQL Server のプログラムを許可しているのでリモートから DAC の接続も可能になっています。

名前付きインスタンスは SQL Server 構成マネージャーから固定ポートの設定にすることもできます。

名前付きインスタンスを使用する場合、固定ポートで利用するパターンが多いと思います。

固定ポートを利用する場合は、SQL Server 構成マネージャーで対象のインスタンスの TCP ポートを固定で設定し、動的ポートは空白に設定します。

そうすると SQL Server は毎回特定のポートで起動されます。

固定ポートを使用した場合は以下のポートをファイアウォールで許可します。

名前付きインスタンスを固定ポートで設定した場合、固定ポートのポート番号とポート番号 + 1 を許可します。

# サービス用のポート番号 + 1 が DAC のポートとなります。

コマンドで設定する場合は以下のようになります。

netsh advfirewall firewall add rule name="SQL Server Browser" protocol=UDP dir=in localport=1434 action=allow
netsh advfirewall firewall add rule name="SQL Server" protocol=TCP dir=in localport=xxxx action=allow
netsh advfirewall firewall add rule name="SQL Server (DAC)" protocol=TCP dir=in localport=xxxx action=allow

SQL Server は接続をするときにポート番号を指定して接続することができます。

この場合は、ポート番号を直接指定していますので、SQL Server Browser のポートが解放されていなくても接続をすることができます。

# SQL Server Browser はポート番号ではなく、インスタンス名を指定した場合のポート番号の解決に使用しているのでポートを指定した場合は使用しなくても接続ができます。

■SQL Server 認証を使用した接続

SQL Server 認証を使用する場合は、[SQL Server 認証モードと Windows 認証モード] を有効にする必要があります。

サーバー認証のモードについては、レジストリに設定がされており設定を変更した場合は、以下のクエリが実行され、レジストリが変更されます。

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N'LoginMode', REG_DWORD, 1
GO

SQL Server 認証モードと Windows 認証モードの切り替えは 設定だけでなく、SQL Server のサービスを再起動する必要がありますので注意が必要になります。

SQL Server をインストールした後に外部から接続をするためにはこのあたりの設定をすれば大丈夫かと思います。