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

  • 質問

  • 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;" & _  <サーバのIPアドレスをここで設定してみた
      "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 を上記(1)を無効にすると

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

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

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

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

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

     

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

     

    2010年9月15日 7:24

回答

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

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


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/
    • 回答の候補に設定 山本春海 2010年9月24日 4:48
    • 回答としてマーク 山本春海 2010年10月4日 5:30
    2010年9月15日 15:42
  • 確かにtrapemiyaさんの指摘のように、サーバーは見えているみたいですね。

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

    • 回答の候補に設定 山本春海 2010年9月24日 4:48
    • 回答としてマーク 山本春海 2010年10月4日 5:30
    2010年9月15日 23:00
  • そのユーザーがどうして 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. 回答が得られたら、もらった回答の [回答済み] ボタンをクリックしてスレッドを回答済みにしましょう
    • 回答の候補に設定 山本春海 2010年9月24日 4:48
    • 回答としてマーク 山本春海 2010年10月4日 5:30
    2010年9月16日 4:10

すべての返信

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

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

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

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

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

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

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

    2010年9月15日 10:43
  • Express Edition の場合、デフォルトで動的ポートになります。だから、
    SQL Server Browser を有効にしているのではないですか?

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

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

    2010年9月15日 14:16
  • 認証で失敗していますから、リモートのSQL Serverは見えている気がします。どのような理由で認証に失敗しているのかを、以下を参考に調べられてはいかがでしょうか?

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


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/
    • 回答の候補に設定 山本春海 2010年9月24日 4:48
    • 回答としてマーク 山本春海 2010年10月4日 5:30
    2010年9月15日 15:42
  • 確かにtrapemiyaさんの指摘のように、サーバーは見えているみたいですね。

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

    • 回答の候補に設定 山本春海 2010年9月24日 4:48
    • 回答としてマーク 山本春海 2010年10月4日 5:30
    2010年9月15日 23:00
  • 通りすがりの者ですが…
    …返信の羅列から拝察するに、いろんな人が、相手のレベルを考えずにいろいろ言っているので、「誰を信じたらいいのかわからない」状態になっているような感じも致します。
    (気のせいでしょうか?)

    その後の、「迷えるこひつじプログラマー」さんの状況が知りたいです。
    (私はAccess2003のadpで、似たようなメッセージを出した経験があります。)

    2010年9月16日 0:45
  • そのユーザーがどうして 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. 回答が得られたら、もらった回答の [回答済み] ボタンをクリックしてスレッドを回答済みにしましょう
    • 回答の候補に設定 山本春海 2010年9月24日 4:48
    • 回答としてマーク 山本春海 2010年10月4日 5:30
    2010年9月16日 4:10
  • こんにちは、迷えるこひつじプログラマー さん。

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

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

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

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

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

    2010年10月4日 5:30