SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

ワークグループ環境の仮想マシンでデータベースミラーリングを構築時に証明書を使用しないエンドポイントの設定で試行錯誤

2 comments

SQL Server 2012 以降では AlwaysOn 可用性グループが実装され、これによりデータベースミラーリングは将来のバージョンの SQL Server では削除予定の機能となりました。
データベース ミラーリング (SQL Server)

ただし、AlwaysOn については Enterprise Edition/ Active Directory が必要となるため、

  • ワークグループ環境
  • Standard Edition × 2 (プリンシパル / ミラー用)
  • Express Edition (監視サーバー用)

の構成で、ミラーリングを組まざるを得ないこともあるかと思います。

Azure の仮想マシン (Azure VM / VIrtual Machine) でデータベースミラーリングを組む方法としては チュートリアル:Azure で高可用性を実現するデータベース ミラーリング があります。
このチュートリアルでは証明書を使用したエンドポイントへの接続を行っていますが、証明書を使用した場合は、
How to replace expired certificates used in database mirroring
SQL Yoga : Certificate Expired, Mirroring Stopped
データベース ミラーリング エンドポイントで発信接続に証明書を使用できるようにする (Transact-SQL)
に記載されているように証明書の有効期限を意識する必要があるかと思います。
# デフォルトでは 1 年で有効期限が切れるかと。

証明書を使用しないエンドポイントへの接続としては

  • 同一のユーザー名 / パスワードのユーザーで SQL Server のサービスを起動

し、エンドポイント間の接続を行う方法があるかと思います。
こちらはチュートリアルには記載されていない方法となりますが、実際の環境ではこちらで運用されていることが多いかと。
組み方にもよるかもしれませんが、Azure の仮想マシン上に SQL Server 2014 をインストールした環境で同一のユーザー名 / パスワードで起動している SQL Server 間でミラーリングを組む際に試行錯誤する箇所があったのでメモとして。

私が構築した環境では、ミラーリングを開始しようとすると、以下のエラーが発生していました。
image

ミラーリングの開始中にエラーが発生しました。
データベース ‘MIRRORDB’ のAlterに失敗しました。  (Microsoft.SqlServer.Smo)
Transact-SQL ステートメントまたはバッチの実行中に例外が発生しました。 (Microsoft.SqlServer.ConnectionInfo)

サーバー ネットワーク アドレス "TCP://DBM02:5022" にアクセスできないか、このアドレスが存在しません。ネットワーク アドレス名と、ローカル エンドポイントおよびリモート エンドポイントのポートが操作可能であることを確認してください。 (Microsoft SQL Server、エラー: 1418)

SQL Server のサービスに使用しているアカウントは同一ユーザー名 / パスワードなのですがこのエラーが発生していました。
各サーバーの ERRORLOG を確認するとプリンシパル側では、

Error: 1474, Severity: 16, State: 1.
Database mirroring connection error 4 ‘An error occurred while receiving data: ‘10054(既存の接続はリモート ホストに強制的に切断されました。)’.’ for ‘TCP://DBM02:5022’.
Error: 1443, Severity: 16, State: 2.
Database mirroring has been terminated for database ‘MIRRORDB’. This is an informational message only. No user action is required.
Error: 1474, Severity: 16, State: 1.
Database mirroring connection error 4 ‘An error occurred while receiving data: ‘10054(既存の接続はリモート ホストに強制的に切断されました。)’.’ for ‘TCP://DBM02:5022’.
Error: 1443, Severity: 16, State: 2.
Database mirroring has been terminated for database ‘MIRRORDB’. This is an informational message only. No user action is required.

ミラー側では、

Database Mirroring login attempt by user ‘DBM02SQLServiceUser.’ failed with error: ‘Connection handshake failed. The login ‘DBM02SQLServiceUser’ does not have CONNECT permission on the endpoint. State 84.’.  [CLIENT: 10.0.0.4]

というようなエラーが発生していました。
エラーの内容から SQL Server のサービスアカウントがミラーリングで使用しているエンドポイントに対して接続ができないためエラーとなっているようです。

ミラーリングのエンドポイントですが、SSMS から実施した場合、[セキュリティの構成] を行った際に以下のようなクエリが実行され作成されています。

CREATE ENDPOINT [ミラー化]
	AS TCP (LISTENER_PORT = 5022)
	FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION = REQUIRED ALGORITHM RC4)
go
IF (SELECT state FROM sys.endpoints WHERE name = N'ミラー化')  0
BEGIN
	ALTER ENDPOINT [ミラー化] STATE = STARTED
END
go


エンドポイントの作成と、エンドポイントの開始は実行しているようですが、権限系の設定は行われていないようでした。

この環境ですがセキュリティの構成を行う際に、以下のようにサービスアカウントをブランクの状態で設定していました。

image

そのため、上記のクエリのようにサービスアカウントのログインの作成とログインに対してのエンドポイントの接続許可が行われなかったようです。

正常に接続するためには以下のようなクエリが実行される必要があります。

IF (SELECT state FROM sys.endpoints WHERE name = N'ミラー化')  0
BEGIN
	ALTER ENDPOINT [ミラー化] STATE = STARTED
END
go
CREATE LOGIN [DBM01SQLServiceUser] FROM WINDOWS
go
GRANT CONNECT ON ENDPOINT::[ミラー化] TO [DBM01SQLServiceUser]
go


そのため、プリンシパル / ミラー (ウィットネスを拘置する場合にはウィットネスも) に対して、

  • SQL Server のサービスユーザーでログインを作成
  • 作成したログインにミラーリング用エンドポイントの接続を許可

という手順を実施することで以下のようにワークグループ環境の Azure の仮想マシン 証明書を使用しないミラーリング環境を構築することができます。

以下は Azure の仮想マシンで Standard Edition × 2 + Express Edition でミラーリングを設定したものになります。

# Express Edition は既定のインスタンスとしてインストールをしていますので、SQLEXPRESS という名前付きインスタンスではありません。

image

権限の設定関連については、以下のシステムテーブルから確認をするとどのように設定されているかを把握できるかと思います。

select * from sys.server_permissions WHERE class = 105
select * from sys.endpoints WHERE type = 4
select * from sys.server_principals
select * from sys.server_role_members
Share

Written by Masayuki.Ozawa

7月 30th, 2014 at 7:23 pm

Posted in SQL Server

Tagged with

2 Responses to 'ワークグループ環境の仮想マシンでデータベースミラーリングを構築時に証明書を使用しないエンドポイントの設定で試行錯誤'

Subscribe to comments with RSS or TrackBack to 'ワークグループ環境の仮想マシンでデータベースミラーリングを構築時に証明書を使用しないエンドポイントの設定で試行錯誤'.

  1. はじめまして。
    田中と申します。
    いつも勉強させてもらってます。
    自分も同じようなエラーが出てて格闘中です・・・。
    このエラーの対処方法で質問なのですが、
    >SQL Server のサービスユーザーでログインを作成
    この「ログインを作成」とあるのは、サーバのローカルユーザを作成するという事なのでしょうか?
    >作成したログインにミラーリング用エンドポイントの接続を許可
    これはファイアウォールに「5022」ポートが接続できるよう設定してあげるとの事なのでしょうか?
    いきなりの質問で失礼かとは思いますが、回答頂けると幸いです。

    田中大貴

    5 11月 14 at 18:06

  2. このログインは
    -OS のログインとして作成した SQL Server のサービス用ユーザーで SQL Server のログインを作成
    を示しています。
    サンプルのクエリでいう
    CREATE LOGIN [DBM01SQLServiceUser] FROM WINDOWS
    が該当の箇所です。
    エンドポイントの接続の許可は、
    GRANT CONNECT ON ENDPOINT::[ミラー化] TO [DBM01SQLServiceUser]
    を示しています。
    前提としては 5022 のポート接続を許可している必要がありますので、イメージされている作業+上記の接続許可が必要とお考えいただければと。

    Masayuki.Ozawa

    13 11月 14 at 10:21

Leave a Reply