先日、リモートPC から Excel VBA を使って SQL Server 2014 Express に接続しようとしたところ、接続できずにエラーが発生しました。
原因は、SQL Server の設定の問題だったのですが、確認した項目の一覧を備忘録として残しておきます。
スポンサーリンク
VBA の接続エラー
まずは、VBA から リモート接続で SQL Server に接続しようとして発生したエラーと VBA スクリプトになります。
SQL Server 接続エラー
[DBNET LIB][ConnectionOpen(Connect()).]SQL Server が存在しないか、アクセスが拒否されました。
Dim con As ADODB.Connection
Dim cn As String
' 接続文字列
cn = "Provider=SQLOLEDB;Data Source=192.168.24.1\SQLEXPRESS,1433;Initial Catalog=dbname;User ID=sa;Password=passwd;"
Set con = New ADODB.Connection
con.CursorLocation = adUseClient
' Open でエラー発生
con.Open cn
今回は、ポート番号1433で SQL Server が接続を待ち受けているものとします。後で知ることになるわけですが、デフォルトで1433ポートで待ち受けているわけではないようです。
SQL Server Management Studio で確認すること
ログインユーザーのプロパティを確認
ログインユーザのプロパティで ”状態” を選択し、ログインが有効になっていることを確認します。
ログインが許可されているか
サーバーのプロパティの接続を確認
このサーバへのリモート接続を許可するにチェックがついていることを確認します。
このサーバーへのリモート接続を許可する
SQL Server Configuration Manager で確認すること
次に、Configuration Manager でリモート接続の設定確認を行います。
サービスの起動確認
少なくとも、SQL Server 本体と SQL Server Browser という2つのサービスが起動されているか確認します。
SQL Server サービス確認
SQL Server ネットワークの構成でプロトコルを確認
共有メモリとTCP/IPが有効になっていることを確認します。
ネットワーク構成プロトコル確認
TCP/IPのプロパティを確認
TCP/IP を右クリックしてプロパティからIPアドレスタブで待ち受けポート番号を確認します。IPALL となっているTCPポート番号が1433になっているか確認します。TCP動的ポートに番号が入っている場合は、削除します。
ポート番号を確認
TCP動的ポートとは、使用されていないポート番号を見つけて待ち受けポートとして割り当てるということのようです。「なんて、迷惑なこと！」と思ったのは私だけではないのではないでしょうか？
ちなみに、今回 VBA から接続できなかったのは、このポート番号の設定でした。
Windows ファイアウォールの設定
今回、SQL Server は TCP の 1433 ポートで待ち受けるように設定しています。また、前述の SQL Server Browser サービス は UDP の 1434 ポートで待ち受けを行っています。
従って、Windows ファイアウォールが有効となっている場合は、このポートへの接続を許可するように設定を追加します。面倒くさければ、Windows ファイアウォールを無効にしてもいいかと思います。
番外編（灯台下暗し的な確認項目）
設定等の確認も大切ですが、もっと基本的なことが原因かもしれません。そのような確認項目を列挙します。
- ケーブルは繋がっているか？抜けていないか？
- PING は通るか？敢えてPINGの応答を返していない場合は返してみるようにしてみるとか。
- インターネットに繋がるか？リモートPCとDBサーバーともに確認