Sql-server – SQL Server Mirroring can’t connect to mirror database

I got this same error a few weeks ago, despite the fact the connection was fine; it was actually due to an authentication issue.

If you don't have domain connectivity between the two machines, you can use certificates to create credentials that can be used for mirroring endpoints. To do this, you create a certificate on the database on each end, create an endpoint based on the certificate, then export each certificate to the opposite end. The public key in the certificate is thus used to authenticate with the private master key on the remote database.

1. Create a master encryption key on each server. This is only done once per server instance.

   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
   

2. Create a certificate for each instance with an arbitrary subject. I will provide T-SQL for endpoint 1, but you will need to do this on the other endpoint as well.

   CREATE CERTIFICATE Endpoint1_Cert WITH SUBJECT = 'CERT for endpoint 1'
   

3. Create the endpoint using the certificate:

   CREATE ENDPOINT Endpoint_Mirroring
   STATE = STARTED
   AS TCP (LISTENER_PORT=7023, LISTENER_IP = ALL)
   FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE Endpoint1_cert,
   ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL);
   

4. Back up the certificate from each end for export:

   BACKUP CERTIFICATE Endpoint1_cert TO FILE = 'C:\BACKUP\Endpoint1.cer';
   

5. Now move each file to the opposite server using your favored method. Here I assume you use the C:\backup directory as both source and target on each server.

6. Create server logins, based on the moved files. Note the full credential consists of a server level login and a user credential on the master database. (Kind of strange but that's the way it works...). Here's the code you'd run on endpoint 2 to import the certificate from endpoint 1. The password is totally arbitrary and is not used later. The logins must be granted mirroring rights.

    --note, run on endpoint 2
    USE master;
   
    CREATE LOGIN Endpoint1_login WITH PASSWORD = 'userpassword';
   
    CREATE USER Endpoint1_user FOR LOGIN Endpoint1_login;
   
    CREATE CERTIFICATE Endpoint1_cert AUTHORIZATION Endpoint1_user
    FROM FILE = 'C:\backup\Endpoint1.cer'
   
    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Endpoint1_login];
   

7. Run a converse process for creating a certificate on endpoint 2 and using it to create a credential on endpoint 1.

8. Now, it's time to fire up the mirroring. You start with the server whose database is not in recovery mode.

   ALTER DATABASE TESTING
   SET PARTNER = 'TCP://endpoint1host:7023';
   

9. Next run the converse command on the alternate server. (If you get a database is not configured for mirroring at this point, there may be an environment-specific security problem that can be worked around with a sysadmin rights grant to the endpoint -- see comment by AllenKing below. This did not occur in our lab.)

I ran this process successfully on 2012 SP1 on 21 March. After I corrected an error I made on the account creation -- I did not use master but instead used the mirror database -- everything worked fine.

In troubleshooting configuration, I ended up using the following dynamic views:

-- To check on endpoints status -- is the port right, etc?
SELECT * FROM SyS.endpoints 
SELECT * FROM SYS.tcp_endpoints
SELECT * FROM sys.database_mirroring_endpoints

-- This shows the endpoint rights
SELECT EP.name, SP.STATE, 
CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id)) 
AS GRANTOR, 
SP.TYPE AS PERMISSION,
CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id)) 
AS GRANTEE 
FROM sys.server_permissions SP , sys.endpoints EP
WHERE SP.major_id = EP.endpoint_id
ORDER BY Permission,grantor, grantee;