小さい会社の1人SEの開発記というか奮戦記

SQL Server 2017 Express は標準設定のままでは他のPCからネットワーク経由でリモート接続を許可していません。これを許可をする時に特に重要になる「Windows Defender ファイアウォール」の設定も含め許可手順をまとめます。

条件整理

2019年1月時点でおそらく最新であろう条件で記事をまとめます。SQL Server 2019 はもう少し先でレビューします。

動作確認に私が普段使っている環境設定で記事をまとめさせてもらいます。

以下手順を追って説明します。

SSMSでリモート接続の許可

• SSMS(Microsoft SQL Server Management Studio)を使ってリモート接続を許可します。
  まずログインし、SSMSオフジェクトエクスプローラーで許可を出したいSQL Serverを右クリック、プロパティを選択します。
  
• ページの選択から「接続」を選択、「このサーバーへのリモート接続を許可する」をチェックし、[OK]をクリックすればデータベース側の接続許可は完了です。
  

SQL Server 2017 構成マネージャーからTCP/IPを有効化する

• 「スタート」ボタン → Microsoft SQL Server 2017 → SQL Server 2017 構成マネージャーを起動します。
  
• 左側のウインドウから「SQL Server ネットワークの構成」 → 「SQLEXPRESS のプロトコル」を選びます。次に、右側のウインドウで「TCP/IP」で右クリックし「有効化」後「プロパティ」を選択します。
  
• TCP/IPのプロパティから「IPアドレス」のタブを選びます。一番下までスクロールし、「IPALL」のTCPポートに 1433 を入力、TCP動的ポートは入力内容を削除し空白にします。
  
• SQL Server を再起動後設定が有効になりますので、再起動すればポートの設定は終了です。
  「SQL Server のサービス」から右側のウインドウで「SQL Server」を右クリックして「再起動」すると、1433ポートで接続できるようになります。
  

Windows Defender でファイアーウォールを許可

1433ポートがファイアウォールを通過できる許可を行えば準備は完了です。

• 「コントロールパネル」→「システムとセキュリティ」→「Windows Defender ファイアウォール」と行くか、「ファイアウォール」を検索し画面を立ち上げます。
  左側のウインドウから「詳細設定」をクリックします。
  
• 「セキュリティが強化された Windows Defender ファイアウォール」が立ち上がりますので、左側のウインドウから「受信の規則」をクリックします。
  
• 受信の規則が一覧で表示されますので、右側のウインドウから「新しい規則」をクリックします。★1
  
• 今回は1433ポートを通過させるので「ポート」を選択します。
  
• 「TCP」と「特定のローカルポート」を選択し「1433」と入力します。後でUDPも同じ様に登録します。
  
• 「接続を許可する」を選択します。
  
• 規則の適用はネットワーク運用ルールに従って下さい。まずは全てチェックしておきます。
  
• 分かり易い名前を付けて下さい。私は「SQL SERVER EXPRESS」と登録しました。
  
• ★1の「新しい規則」に戻りUDPの1433ポートも許可して下さい。手順はUDPを選ぶところ以外は共通です。
  
• 「SQL SERVER EXPRESS」という名称でTCPとUDPの許可が登録されました。
  
• いらないかもしれませんが、過去の慣習でパソコンを再起動をここで行っています。

Excel VBA からの接続文

エクセルからVBAでリモート接続する場合は以下となります。冒頭の条件でADO接続
ポイントは「Data Source=IPアドレス￥インスタンス名,使用ポート番号」です。

‘ 接続文
TXT_CN = “Provider=SQLOLEDB”
TXT_CN = TXT_CN & “; Data Source=192.168.0.100￥SQLEXPRESS,1433”
TXT_CN = TXT_CN & “; Initial Catalog=test_db”
TXT_CN = TXT_CN & “; User ID=test_user”
TXT_CN = TXT_CN & “; Password=test_password”

VBAでの接続テスト具体例

Option Explicit
 
Sub Setsuzoku()
 
 ' 変数宣言
 Dim ADO_CN As Object ' コネクションオブジェクト
 Dim TXT_CN As String ' 接続文
 
 ' 接続文
 TXT_CN = "Provider=SQLOLEDB"
 TXT_CN = TXT_CN & "; Data Source=192.168.0.100\SQLEXPRESS,1433"
 TXT_CN = TXT_CN & "; Initial Catalog=test_db"
 TXT_CN = TXT_CN & "; User ID=test_user"
 TXT_CN = TXT_CN & "; Password=test_password"
 
 ' ADOコネクションオブジェクトを作成
 Set ADO_CN = CreateObject("ADODB.Connection")
 
 ' データベース接続
 ADO_CN.Open TXT_CN
 
 MsgBox "正常に接続できました"
 
 ' 接続解除
 ADO_CN.Close
 Set ADO_CN = Nothing
 
End Sub

まとめ

細かい処理がたくさん並びますが「理解してから始めればなんてことはありません。」

裏を返せば「理解しないで始めると十中八九はまります。」熟読してから始めることを強くお薦め致します。