【VBA×SQL】ServerへのログインやWindows認証の設定方法

VBA×SQLの3本目の記事です。

今回はAppをダウンロード＆インストールしたRDBMSで最初に行う各種設定を紹介していきます。

VBAは環境構築する必要がないので実感がないかもしれませんがVBA以外の言語は環境構築が大変です。

単純にAppを用意するだけでExcelと連携できるか・・・と考えてしまうのですが難しいです。

色々準備があります。コードを書く前にもやる事が沢山あるのでひとつずつ見ていきましょう。

（深堀すると難しくなるところはサクッとスルーして読みやすくしています）

この記事はVBAの中でSQLの使い方を紹介するシリーズの3番目の記事です。

シリーズ初回の記事はこちらです。通して見ていただくとより理解が進みます。

まずはインストール状況の確認です。

デフォルトでインストールされた方はSQLServerExpressは以下アドレスに格納されています。

画面左下のWindowsマークをクリックしアプリケーションを探します。

アルファベットのMまで進んでいただきMicrosoftSQLServerTools19の上で左クリック。

SQLServerManegementStudio19を左クリックしましょう。

以下ダイアログが表示されてAppが起動します。

ここでは「データベースエンジン」を選択してください。

データベースエンジンでアクセスする事でデータベースやテーブルを作成する事ができます。

サーバー名はデフォルトで進めます。ここに入力されているサーバー名がインスタンスです。

インスタンスが難しいと感じた方は単純にサーバー名だと思ってください。

自分のPC内にサーバーが用意されたことになります。

ただし実際にサーバーという物体が用意されるわけではないです。

Appをインストールした際に自PCにサーバーとなるデータ領域を用意しています。

その名前がサーバー名に表示されているDESKTOP・・・という名前になります。

C:\Program Files\Microsoft SQL Server　の中にログファイルが居ますが見ても分かりません。

まずは開発環境を用意する事でPC内にサーバーとして使えるデータ領域を確保できたと考えてください。

最初はWindows認証を選択してください。

Windows認証というのはPCにログインする時の認証をそのまま使ってAppを起動するという事です。

Windows認証と対になる認証方法としてSQLServer認証があります。

SQLServer認証はAppにアクセスする際にログインIDとPWを使用します。

SQLServer認証を使うのはAppへのアクセス権をコントロールしたい為です。

このあと解説します。

全ての要素を選択できたら「接続」ボタンをクリックしましょう。

エラーメッセージが表示されず以下のような画面が表示されたらアクセス成功です。

画面左の短冊に表示されているのがオブジェクトエクスプローラーです。

ここにサーバーの要素が固まって配置されています。

Excelからサーバーへアクセスする際に人によってできる事を分けたいのでSQLServer認証を使います。

あくまで例ですが以下の様にアクセス権を分ける事ができます。

これとは別にExcelVBA側でフォームを作ってアクセス権をコントロールする事も可能です。

（情報追加、削除のボタンを作りログインする人によりボタンを押せる／押せないを切り替える等）

よってSQLServer側で権限を沢山持たなくても権限をコントロールしたうえでの運用はできます。

ただしSQLServerではSQLServer認証をよく使うので紹介の意味も込めて使用していきます。

オブジェクトエクスプローラーの上部にあるサーバー名の上で右クリック。

その後プロパティの上で左クリック。

左側の短冊から「セキュリティ」の上で左クリック。

サーバー認証を「SQLServer認証モードとWindows認証モード」に切り替えます。

「構成の変更の一部は・・・有効になりません。」と書いてあります。

ここも「OK」ボタンの上で左クリック。

オブジェクトエクスプローラーのサーバー名の上で右クリック。

その後「再起動」の上で左クリック。

また長方形のダイアログボックスが出てきました。

再起動するかどうかを確認しています。「はい」の上で左クリック。

これでサーバーの再起動ができました。

次からはログインIDとPWを使って「も」Appにアクセスできる様になりました。

「私は今何を作っていてこの後どこに向かっていくのか・・・」と思っている方。居ますよね。

今の状況を図にしてみましたので一度確認ください。

以下のような環境を構築する為に作業をしています。

今までは自PC内に用意されたSQLServerにアクセスしたり認証の方法について勉強していました。

この後サーバー内にデータベースを用意するのと先程説明したSQLServer認証で使うIDを用意していきます。

次回以降の記事でデータベース内のテーブルを用意していきます。

最後にExcelとテーブルとの間で情報のやり取りをしていきます。

オブジェクトエクスプローラー内のデータベースの上で右クリック。

「新しいデータベース」の上で左クリック。

表示されたダイアログボックスの上部「データベース名」に任意の名前を入力します。

ここでは「testDB」とします。

データベースオブジェクト内にtestDBというデータベースが用意されたことを確認してください。

アイコンが出来ていれば作業は成功しています。

先程サーバーへのアクセス時にSQLServer認証が出来る様にしました。

ここで1つIDを用意していきます。

ExcelからSQLServerにアクセスする際にログインIDとPWを使ってログインさせる為です。

今回用意するIDはサーバー内で一番強力なIDです。

Excelとやり取りする際は一番強力なIDにしておいてExcelVBA側で権限をコントロールします。

（SQLServer認証のところで説明していますので忘れた方はこちらから戻って見直してください）

サーバーを用意した時にsaというログイン情報が用意されます。

これを使ってSQLServer認証をできる様にしていきます。

サーバー→セキュリティ→ログイン→saという文字を探してください。

「sa」の上で右クリック。その後プロパティの上で左クリック。

ログインのプロパティというダイアログボックスが立ち上がります。

左の短冊では何も選択されていませんが「全般」が選択され表示されています。

パスワードというところに入っている●を消してご自身で決めたパスワードに書き換えて下さい。

2か所共同じパスワードを書くようにしましょう。（2つ目は確認用です）

ログインが「無効」になっているので「有効」に切り替えてください。

その後ダイアログボックス右下の「OK」ボタンの上で左クリック。

これでsaというログイン名でサーバーにアクセスが可能になりました。

再度オブジェクトエクスプローラーでsaを見つけてログインのプロパティを見てみます。

サーバーロールではsysadminにチェックが入っています。

これがサーバー内で全ての作業を行う事ができる権限です。

左の短冊のユーザーマッピングを見てみます。

用意されている全てのデータベースでdb_ownerという権限を持っていることがわかります。

権限はあえて説明しない予定でした。出来るだけ難しい情報を省きたかった為です。

しかし解説も兼ねているので触れないのも良くないと考えました。

読まなくても何とかなるのでここは流してもらっても構いません。

SQLServerは大きく分けて権限が2つに分かれています。

サーバー上の権限を管理する為のロールです。

他にも種類があるのですがここでは2種類だけを紹介します。

ExcelVBAで作業するにあたっては全部覚える必要は無いのでこの辺りで説明を区切ります。

サーバーとは別にデータベースだけの権限が設定できます。

パブリックを含めると10個のロールが用意されています。今回使用しているものだけ紹介します。

サーバーレベルではsysadminでデータベースレベルではdb_ownerです。

saというログインオブジェクトはApp内における全ての作業が可能というログインIDという事になります。

次はテーブルの作成方法を紹介していきます。

テーブルを扱う際にはエクセルの様に雑多に何でも入れていいのでは？と考えてる方も多い様です。

実はテーブルにはルールがありますのでそう簡単にはいきません。

ひとまず最低限のルールだけ勉強しながら簡単なテーブルを作成します。

ここまで出来れば残すはExcelVBAでテーブルにアクセスする方法のみになります。

これでExcel×SQLが達成されます。