SQL Server Express にリモート接続

SQL Server Express は、標準設定のままだと別のPCから接続することができません。SQL Server Management Studio (SSMS) を使用してリモート接続の許可等をしてやるとともに、SQL Server 構成マネージャを使ってプロトコルの設定をしてやる必要があります。SQL Server Express では、TCP ポートの設定では、SQL Serverが標準で使っている固定ポートを使用する方法と、もう一つは、動的ポートを使用し SQL Server Browser を有効にして外部からの接続にポートを教えてやって接続できるようにする方法の二通りがあります。個人がローカルで使う場合はどちらを使ってもいいのですが、公開用サーバーのようにファイアーウォールの設定を必要とする場合には、固定ポートを使用した方がファイアーウォールの設定が簡単になります。

1.認証モードの変更、sa アカウントの有効化、リモート接続の許可

インストール時の設定で、SQL Server 認証モード及び sa アカウントを有効にしている場合は、この設定は必要ありません。「基本」でインストールした場合等有効になっていない場合は、SSMS で以下の操作をおこなって、SQL Server 認証モード、sa アカウントを有効にしてリモート接続を許可します。
(1) SQL Server 認証モードを有効にする
SSMS を起動して、変更したい SQL Server に接続します。SSMS オブジェクト エクスプローラーで変更したいサーバーを選択して、右クリックしてプロパティをクリックします(下図)。

サーバーのプロパティのダイアログが表示されるので、「セキュリティ」のページを選択すると下の図のような画面になります。「サーバー認証」で「SQL Server 認証モードと Windows 認証モード」の項目を選択します。OK ボタンをクリックすると「SQL Server を再起動するまで有効になりません。」というメッセージが表示されるので再起動します。再起動は、プロパティのダイアログを表示したときと同じくサーバーを選択して右クリックすると「再起動」という項目があるのでそれを選択することでできます。
(2) sa アカウントを有効にする
オブジェクトブラウザーで「セキュリティ」の「ログイン」を展開すると sa アカウントが既にできています。sa アカウントを選択して右クリックして「プロパティ」を選択すると、下の図のようにログインのプロパティのダイアログが表示されます。「全般」のページには、パスワードを入力する項目があるので、そこにパスワードを入力します。
次に、「状態」のページを選択すると下の図のようなダイアログが表示されるので、ログインを有効にしてから、OK ボタンをクリックします。
(3) リモート接続を許可する
オブジェクト エクスプローラーで変更したいサーバーを選択して、右クリックしてプロパティをクリックします。サーバーのプロパティのダイアログが表示されるので「接続」のページを選択します。下の図の画面が表示されるので、リモートサーバー接続で、「このサーバー へのリモート接続を許可する」にチェックをして、OK ボタンをクリックします。

以上の操作をコマンドで行う場合は、以下のようになります。コマンドプロンプトか PowerShell で実行でき、SSMS をインストールする必要がないので手間が省けます。

sqlcmd
:connect (local)\SQLExpress

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2 ;
GO
ALTER LOGIN sa ENABLE ;
GO
ALTER LOGIN sa WITH PASSWORD = 'パスワード' ;
GO
EXEC sp_configure 'remote access', 1 ;
GO
RECONFIGURE ;
GO  

認証モードの変更を変更した場合、変更を適用するためには SQL Server の再起動が必要になりますが、SSMS か Sql Server 構成マネージャを使って再起動をすることができます。

2.TCP/IPの有効化
「スタート」ボタン、「すべてのアプリ」、「Microsoft SQL Server 2016」、「Sql Server 構成マネージャ」の順にクリックして、Sql Server 構成マネージャを立ち上げます。「SQL Server ネットワークの構成」の「MSSQLSERVERのプロトコル」(「基本」でインストールした場合と名前付きインスタンスでインストールした場合は「SQLEXPRESSのプロトコル」)を選択します。TCP/IPを選択して右クリックをして「有効化」をクリックします。

○ 固定ポートを使用の場合
デフォルトでは、「既定のインスタンス」に設定した場合は、固定ポートに設定されていますが、Web PI でインストールした場合と「名前付きインスタンス」に設定した場合は、動的ポートに設定されています。動的ポートに設定されている場合は、固定ポートに設定を変更します。変更の手順は、上の図で、プロパティをクリックすると「TCP/IPのプロパティ」ダイアログが表示されるので、「IPアドレス」タブを選択します。一番下のIPAllで、「TCP ポート」をSQL Server標準の1433に、「TCP 動的ポート」を空白にしてOKボタンをクリックします。SQL-Server を再起動すると設定が有効になり、固定ポートで接続できるようになります。

○ 動的ポートを使用する場合

「SQL Server 構成マネージャ」を動作させる必要があります。手順は「SQL Serverサービス」をクリックし「SQL Server Browser」を選択し、右クリックして、プロパティを選択します。

「サービス」タブを選択し、「開始モード」を「自動」に設定してOKボタンをクリックします。これで次回のWindows起動時から自動でSQL Server Browserが起動します。上の画面で「SQL Server Browser」を選択し、右クリックすると今回は「開始」が有効になっているので、「開始」をクリックするとSQL Server Browserが起動します。

なお、「既定のインスタンス」に設定した場合にも、動的ポートを使用することは可能ですが、「TCP/IPのプロパティ」で「TCP ポート」を空白に、「TCP 動的ポート」を 0 に設定して、動的ポートを利用できるようにする必要があります。

4.ファイアウォールの設定

Windows Vista、Windows 7 では、デフォルトでファイアウォールがオンになっているので使用するポートを開く必要があります。固定ポートの場合は、TCP 1433 の受信を許可するようにします。ファイアウォールの設定についての具体的な手順は、SQL Server をリモート接続-ファイアウォールの設定にメモをしました。動的ポートの場合は、UTP 1433 と TCP 1433 の受信を許可するようにしますが、複数のインスタンスがインストールされている場合は、動的にポートが使用されるようになるため、ファイアウォールの構成が難しくなります。
参考リンク:MSDN ライブラリ SQL Server のアクセスを許可するための Windows ファイアウォールの構成

5. ホスト名・接続文字列

SQL Server Management Studio で接続する時に使用するサーバー名は、固定ポートか動的ポートかで異なってきます。
固定ポートの場合は、サーバー名,ポート番号 で、インスタンス名は不要です。また、既定のポート番号 1433 の場合は、ポート番号を省略できます。インスタンス名 SQLExpress でインストールした場合でも、ポート番号1433の場合は、192.168.0.3 のように IPアドレスだけで接続できます。ただし、192.168.0.3\SQLExpress とインスタンス名を追加するとと接続できなくなるので、その場合は、192.168.0.3\SQLExpress,1433 とポート番号まで記述する必要があります。接続文字列は以下のようになります。
.NET の場合
<connectionStrings>
  <add name="{接続文字列名}" connectionString="Data Source=192.168.0.3,1433;Initial Catalog={データベース名};Integrated Security=True;"providerName="System.Data.SqlClient" />
</connectionStrings>
.NET Core の場合
"ConnectionStrings": {
  "DefaultConnection": "Server=192.168.0.3,1433;Database={データベース名};Trusted_Connection=True;MultipleActiveResultSets=true"
}
動的ポートの場合は、インスタンス名でサーバーがポート番号を識別するので、サーバー名\インスタンス名となります。例えば、192.168.0.3\SQLExpress のようにします。接続文字列は以下のようになります。
.NET の場合
<connectionStrings>
  <add name="{接続文字列名}" connectionString="Data Source=192.168.0.3\SQLEXPRESS;Initial Catalog={データベース名};Integrated Security=True;"providerName="System.Data.SqlClient" />
</connectionStrings>
.NET Core の場合
"ConnectionStrings": {
  "DefaultConnection": "Server=192.168.0.3\\SQLEXPRESS;Database={データベース名};Trusted_Connection=True;MultipleActiveResultSets=true"
}