SQL Server に SQL Server 認証で接続するためのサーバー側の設定

環境

SQL Server

• SQL Server 2019

Windows (サーバーとして使用)

• Windows 10 Pro 20H2

Windows Server (サーバーとして使用)

• Windows Server 2019

前提条件

インストール対象の OS や SQL Server のバージョンは複数試していますが、いずれの場合においても SQL Server のインストール時、またインストール後の設定を以下のようにしています。

• SQL Server を Administrators 権限でインストールしています。
• SQL Server のインストールパスは既定値のままです。
• OS についてはほぼインストール直後の状態にしています。
• サーバー側に SQL Server Management Studio がインストールされているものとします。
• この Tips で説明している内容は、クライアントから接続確認を行う以外はすべてサーバー側の操作です。

SQL Server の機能は「データベース エンジン サービス」のみ選択しています。

インスタンスについては「既定のインスタンス」「名前付きインスタンス」両方で確認します。

サービスアカウントは既定のままにします。

認証モードは Windows 認証のみとします。設定手順として後の設定で SQL Server 認証を有効にします。

「TestDatabase」というデータベースを作成しておきます。確認のために新規で作成したのみで、中身は何も作成していません。

「TestUser」というアカウントを作成し、このアカウントに対して SQL Server 認証を行うものとします。「TestDatabase」へのアクセス権は付けておきます。

SQL Server 認証を有効にする

SQL Server インストール時に SQL Server 認証を有効にしていない場合は以下の手順で有効にします。

サーバー側で Microsoft SQL Server Management Studio を起動します。

Windows 認証で自身のサーバーにログインします。インスタンス名がある場合は「<サーバー名>\<インスタンス名>」形式でサーバー名に入力してください。

「オブジェクト エクスプローラー」からサーバーを右クリックして「プロパティ」を選択します。

左のメニューから「セキュリティ」を選択して「サーバー認証」から「SQL Server 認証モードと Windows 認証モード」にチェックをいれます。

確定すると SQL Server の再起動を求められます。

「オブジェクト エクスプローラー」からサーバーを右クリックして「再起動」を選択します。再起動には管理者権限が必要です。

「はい」を選択すると SQL Server が再起動します。

リモート接続を許可する

既定では有効になっていると思いますのが念のため確認します。

SQL Server Management Studioで「オブジェクト エクスプローラー」からサーバーを右クリックして「プロパティ」を選択します。

左のメニューから「接続」を選択して「リモートサーバー接続」の「このサーバーへのリモート接続を許可する」にチェックが入っていることを確認します。

sa アカウントによるログイン許可 (必要な場合のみ)

sa アカウントでリモート接続したい場合は設定してください。sa は権限が最上位であるため、不要であれば無効にしておくのがよいでしょう。ちなみに SQL Server インストール時に SQL Server 認証を有効にしている場合はすでに sa アカウントのリモート接続は有効になっています。

SQL Server Management Studio を開き、「オブジェクト エクスプローラー」から「サーバー」「セキュリティ」「ログイン」を展開し、「sa」を右クリックして「プロパティ」を選択します。

左のメニューから「全般」を選択してパスワードを入力します。

左のメニューから「状態」を選択して「ログイン」を「有効」にします。

TCP/IP

TCP/IP の有効化

スタートメニューから「Microsoft SQL Server 2019」のフォルダを開き「SQL Server 2019 構成マネージャー」を選択します。

※ SQL Server のバージョンによって名前が変わります。

左のツリーから「SQL Server 構成マネージャー (ローカル)」「SQL Server ネットワークの構成」を展開し「MSSQLSERVER のプロトコル」を選択します。 右の一覧から「TCP/IP」を右クリックし「有効にする」を選択します。

※「MSSQLSERVER」の名前はインストール時にインスタンス名を指定した場合はその名前に変わります。

OK をクリックします。

左のツリーから「SQL Server のサービス」を選択し、右の一覧から「SQL Server (MSSQLSERVER)」を右クリックして「再起動」を選択します。

TCP ポートの確認・または指定 (インスタンス名を指定している場合)

インスタンス名を指定していない場合はデフォルトの TCP ポート 1433 が指定されますが、インスタンス名を指定している場合はそれ以外のポートを使用する必要があります。

左のツリーから「SQL Server 構成マネージャー (ローカル)」「SQL Server ネットワークの構成」を展開し「MSSQLSERVER のプロトコル」を選択します。 右の一覧から「TCP/IP」をダブルクリックしてプロパティを開きます。

※「MSSQLSERVER」の名前はインストール時にインスタンス名を指定した場合はその名前に変わります。

「IPアドレス」のタブをクリックします。

「IPAll」グループにある「TCP 動的ポート」に記載されているポートを調べます。ちなみにこの値は任意の値に変更可能です。 この値は後のファイアウォール設定で指定します。

ファイアウォール

ファイアウォール (TCP)

スタートメニュから「Windows 管理ツール」フォルダを開き、「セキュリティが強化された Windows Defender ファイアウォール」を選択します。

左のツリーから「受信の規則」を右クリックして「新しい規則」を選択します。

「ポート」を選択します。

「TCP」を選択します。

SQL Server のインスタンス名を指定していない場合は「特定のローカル ポート」に「1433」を入力します。

SQL Server のインスタンス名を指定している場合は「特定のローカル ポート」に「SQL Server 構成マネージャー」で確認、または入力した TCP 動的ポートを指定します。

「接続を許可する」にチェックが入っていることを確認します。

「ドメイン」「プライベート」のみにチェックを入れて下さい。インターネット上での公開は想定していません。

後で見たときに分かりやすい名前を入力してください。ここでは「[追加] SQL Server TCP 1433」としています。

追加された状態です。

インスタンス名を指定していない場合はこれで設定は完了です。

ファイアウォール (UDP) (インスタンス名を指定している場合)

「セキュリティが強化された Windows Defender ファイアウォール」を開き、左のツリーから「受信の規則」を右クリックして「新しい規則」を選択します。

「ポート」を選択します。

「UDP」を選択し、「特定のローカル ポート」に「1434」を入力します。

「接続を許可する」にチェックが入っていることを確認します。

「ドメイン」「プライベート」のみにチェックを入れて下さい。インターネット上での公開は想定していません。

後で見たときに分かりやすい名前を入力してください。ここでは「[追加] SQL Server UDP 1434」としています。

追加された状態です。

SQL Server Browser を起動する (インスタンス名を指定している場合)

スタートメニューから「Microsoft SQL Server 2019」のフォルダを開き「SQL Server 2019 構成マネージャー」を選択します。

※ SQL Server のバージョンによって名前が変わります。

左のツリーから「SQL Server 構成マネージャー (ローカル)」を展開し「SQL Server のサービス」を選択します。 右の一覧から「SQL Server Browzer」を右クリックし「プロパティ」を選択します。

「サービス」タブを選択し「全般」グループの「開始モード」を「自動」にします。選択したら「OK」ボタンをクリックします。

前の画面に戻ったらもう一度「SQL Server Browzer」を右クリックして「開始」を選択します。

これで設定は完了です。

どうしても繋がらないとき

ここまでの設定を行っても SQL Server 認証でつながらないときは以下の設定を行うと繋がる可能性があります。 ただし、セキュリティ的にこれまでの設定より弱くなる場合がありますので注意してください。

まずは SQL Server Management Studio で接続を試す

プログラムなどで接続確認を行うと接続ユーザーにデータベースへのアクセス権限がなかったなど確認項目が多くなってしまうので、まずは SQL Server Management Studio から SQL Server 認証が行えるか確認しましょう。

ネットワーク プロファイルが「プライベート」になっているか

たまにあるのが Windows インストール直後や新しいネットワークに接続したときに間違えて「パブリック」にしてしまっていることがあります。 この状態だと SQL Server 以外にもいろいろとネットワーク経由の接続ができなくなります。 不特定多数の共有ネットワークではない安全なネットワーク内なら「プライベート」に設定しましょう。

sqlservr.exe をファイアウォールで許可する

この設定を行うと SQL Server に対してポートをすべて指定しているのと同じになりますので注意してください。

「セキュリティが強化された Windows Defender ファイアウォール」を開き、左のツリーから「受信の規則」を右クリックして「新しい規則」を選択します。

「プログラム」を選択します。

「このプログラムのパス」に以下の EXE のパスを指定します。MSSQL15.SQLEXPRESS の箇所は SQL Server のバージョンによって変わります。

%ProgramFiles%\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\Binn\sqlservr.exe

「接続を許可する」にチェックが入っていることを確認します。

「ドメイン」「プライベート」のみにチェックを入れて下さい。インターネット上での公開は想定していません。

後で見たときに分かりやすい名前を入力してください。ここでは「[追加] SQL Server EXE」としています。

追加された状態です。