Excel VBA から SQL Server 2008 Express Edition への 接続

Question

Excel VBA　から　SQL Server 2008 Express Edition　へ接続したいとおもっています。

サーバマシンに　SQL Server と　Excel VBA を置いた場合には接続できました。

しかし、クライアントマシンへ　Excel VBA　を置いてインターネットを通して接続する方法がわかりません。

Excel VBA　プログラムでは、見よう見まねで以下のように書いてみました。

Private Const CONNECTION_STRING = _
  "Provider=MSDASQL;Driver={SQL Server};" & _
  "Server=***.***.***.***\SQLEXPRESS;" & _  ＜サーバのＩＰアドレスをここで設定してみた
  "Database=test;" & _
  "User ID=****;" & _
  "Password=****" & _
  ""
  Dim conn As New ADODB.Connection
  conn.Open CONNECTION_STRING
などとしたうえで、

（1）SQL Server 2008 Express Editionのネットワーク接続を有効にする
（2）SQL Server Browserサービスを有効にする
（3）Windowsファイアウォールの設定を行う

などを行いましたが、以下のエラーメッセージが出て接続できません。

実行時エラー'2147217843 (80040e4d)'

[Microsoft][ODBC SQL Server Driver][SQL Server]ユーザー　'yamamoto'はログインできませんでした。

ちなみに　SQL Server　を上記（１）を無効にすると

別のエラーメッセージになるので、

Excel VBA の通信は　SQL　Server　には届いているが、

ユーザーＩＤやパスワードが誤っている、、などによって、

接続ができていないように感じ、SQL Server　側で確認しましたが、

これは合っているようです。

 

よろしくお願いいたします。

 

Answer 1

DBサーバ側のWindows ファイアウォールを一度無効にされてみてはどうでしょう。

また、Management Studio から サーバーのプロパティ - リモート サーバー接続 で、"このサーバーへのリモート接続を許可する" にチェックが入っている確認されてみてはどうでしょう。 

ローカルで接続ができるということは、リモート設定が無効になっているか、Windows ファイアウォールでブロックされているような気がします。

Answer 2

補足すると、SQL Serverで使用するポート番号はTCP 1433です。
(UDP 1434も使っていますが、これはサーバー情報等を送るために使用しているため、無視してもいいと思います)

ただポートを開放すると、それだけリスクが高まるので(例えば昔に流行ったNimdaとかCode Redなど)、できればインターネットVPNを介して接続できませんか？
(繰り返しますが、必要なポート以外を開放するのはお勧めできません。)

その他、原因を調べるためには、現在のWindowsにはパケットキャプチャツールが付属しているので、パケットを解析してみるのも一つの手だと思います。
※大量のパケットを解析する必要があるため、大変ですけど(^_^);

一番のお勧めは、インターネットVPNが使用できるなら、それを使うのが一番だと思います。

Answer 3

Express Edition の場合、デフォルトで動的ポートになります。だから、
SQL Server Browser を有効にしているのではないですか？

そのあたりが分かっていれば、ファイアウォールは UDP:1434 に穴あけ
したと思うのですが、いかがでしょう？

上記がうまく動いているのなら、問題は SQL Server 側の認証・承認が
怪しそうです。混合認証モードにして、ログインアカウントの設定は間
違いなくされているのでしょうか？

Answer 4

認証で失敗していますから、リモートのSQL Serverは見えている気がします。どのような理由で認証に失敗しているのかを、以下を参考に調べられてはいかがでしょうか？

トラブルシューティング : ユーザー 'X' はログインできませんでした
http://msdn.microsoft.com/ja-jp/library/ms366351.aspx

---

★良い回答には回答済みマークを付けよう！ わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/

Answer 5

確かにtrapemiyaさんの指摘のように、サーバーは見えているみたいですね。

もしかしてクライアントで使用しているユーザーがSQL Serverのログインに登録されていないということはありませんか？

Answer 6

通りすがりの者ですが…
…返信の羅列から拝察するに、いろんな人が、相手のレベルを考えずにいろいろ言っているので、「誰を信じたらいいのかわからない」状態になっているような感じも致します。
（気のせいでしょうか？）

その後の、「迷えるこひつじプログラマー」さんの状況が知りたいです。
（私はＡｃｃｅｓｓ２００３のａｄｐで、似たようなメッセージを出した経験があります。）

Answer 7

そのユーザーがどうして SQL Server にログインできなかったのかは、サーバー側の ERRORLOG に記録されています。

場所はデフォルトで c :\Program Files \Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Log \ERRORLOG です。テキスト エディタで開けます。

まずエラーを起こしてみて、その後 ERRORLOG をメモ帳などで開きます。するとそこにエラー（番号は 18456）が記録されてますから、あとは trapemiya さんのあげたリンクから、状態コードを見てどうしてログインできなかったのかを知ることができます。

---

Please mark the thread as Answered when an answer resolves your problem. 回答が得られたら、もらった回答の [回答済み] ボタンをクリックしてスレッドを回答済みにしましょう

Answer 8

こんにちは、迷えるこひつじプログラマー さん。

ＭＳＤＮ　フォーラムのご利用ありがとうございます。フォーラム オペレーターの 山本 です。

その後いかがでしょうか。皆さんからのアドバイスを参考に、問題の切り分けを実施されましたでしょうか。
問題の切り分けなどに参考になると思われる投稿に、勝手ながら私のほうで回答としてマークさせていただきました。

質問された方には、いただいた返信の中で解決に役立った投稿や、参考になる有効な情報には、回答としてマークすることをお願いしています。
回答としてマークすることで、今後同じ問題でこのスレッドを参照される方にも、有効な情報がわかりやすくなるかと思いますので、お手数ですがご協力をお願いいたしますね。

もし、現在まだ問題が解決していない場合や不明点などあれば、確認されたことなどの詳細を記載いただき、ご質問を続けてくださいね。
よろしければお時間のある時にでも、その後の状況をお知らせくださいね。

今後とも、MSDN フォーラムをよろしくお願いいたします。
                                                 
マイクロソフト株式会社 ＭＳＤＮ フォーラム オペレーター 山本 春海