How to Set Up Mirroring in a Workgroup Using Certificate Authentication

Setting Up SQL Server Mirroring in a Workgroup Using Certificate Authentication

Database mirroring in a workgroup environment requires certificate-based authentication because Windows authentication isn't available without a domain. You can configure this across two or three SQL Server instances using T-SQL to create endpoints, generate certificates, and exchange trust between servers. This guide walks through the complete process, including an optional witness server for automatic failover.

A quick note on SQL Server Mirroring: Microsoft deprecated database mirroring in SQL Server 2012 and removed it from future roadmap planning. If you're building a new high availability solution, Always On Availability Groups is the recommended path. That said, mirroring still runs in plenty of production environments, and workgroup configurations using certificate authentication remain a legitimate operational requirement for many organisations. This guide documents the process accurately for those environments.

Why Workgroup Mirroring Needs Certificate Authentication

In a domain environment, SQL Server mirroring endpoints authenticate using Windows (Kerberos or NTLM). In a workgroup, that trust relationship doesn't exist. Certificate-based authentication fills that gap. Each server generates its own certificate, backs it up to a file, and then imports the other servers' certificates. This establishes mutual trust at the endpoint level without requiring Active Directory.

The setup involves three passes across your servers:

1. Create master keys, certificates, and endpoints on each server
2. Exchange certificate files between servers (copy the .cer files manually)
3. Create logins, users, and grant endpoint connect permissions using the imported certificates

If you're including a witness server for automatic failover, note that the witness only needs the WITNESS role on its endpoint, and it can run on SQL Server Express. That's a cost-effective option worth knowing about.

Variables to Replace Before Running Any Script

The scripts below use placeholder variables. Search and replace all of these before executing anything:

Update the certificate expiry_date values to something appropriate for your environment. The dates in the original scripts are long expired.

Step 1: Configure the Principal Server

Run the following on the principal. This creates the database master key, generates a certificate for the principal, creates the mirroring endpoint, and backs up the certificate to disk.

-- ON THE PRINCIPAL

-- Create master key if it doesn't already exist
IF (SELECT COUNT(*) FROM sys.symmetric_keys WHERE name LIKE '%DatabaseMasterKey%') = 0
BEGIN
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '%%SECUREPASSWORD%%';
END
GO

-- Create the principal's certificate
CREATE CERTIFICATE %%PRINCIPAL%%_cert
    WITH SUBJECT = '%%PRINCIPAL%% certificate',
    START_DATE = '2024/01/01',
    EXPIRY_DATE = '2027/01/01';
GO

-- Check for and drop any existing mirroring endpoint
SELECT name, endpoint_id, type, type_desc, state_desc
FROM master.sys.endpoints;

-- DROP ENDPOINT Mirroring; -- Uncomment if endpoint already exists
GO

-- Create the mirroring endpoint
CREATE ENDPOINT endpoint_mirroring
    STATE = STARTED
    AS TCP (LISTENER_PORT = %%PORT%%, LISTENER_IP = ALL)
    FOR DATABASE_MIRRORING (
        AUTHENTICATION = CERTIFICATE %%PRINCIPAL%%_cert,
        ENCRYPTION = DISABLED,
        ROLE = ALL
    );
GO

-- Back up the certificate to disk for transfer to other servers
BACKUP CERTIFICATE %%PRINCIPAL%%_cert
    TO FILE = 'C:\%%PRINCIPAL%%_cert.cer';
GO

Once this completes, copy %%PRINCIPAL%%_cert.cer to the mirror and witness servers.

Step 2: Configure the Mirror Server

Run the following on the mirror server. The process is the same: master key, certificate, endpoint, and backup.

-- ON THE MIRROR

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '%%SECUREPASSWORD%%';
GO

CREATE CERTIFICATE %%MIRROR%%_cert
    WITH SUBJECT = '%%MIRROR%% certificate',
    START_DATE = '2024/01/01',
    EXPIRY_DATE = '2027/01/01';
GO

CREATE ENDPOINT endpoint_mirroring
    STATE = STARTED
    AS TCP (LISTENER_PORT = %%PORT%%, LISTENER_IP = ALL)
    FOR DATABASE_MIRRORING (
        AUTHENTICATION = CERTIFICATE %%MIRROR%%_cert,
        ENCRYPTION = DISABLED,
        ROLE = ALL
    );
GO

BACKUP CERTIFICATE %%MIRROR%%_cert
    TO FILE = 'C:\%%MIRROR%%_cert.cer';
GO

Copy %%MIRROR%%_cert.cer to the principal and witness servers.

Step 3: Configure the Witness Server (Optional)

Skip this section if you don't need automatic failover. If you do include a witness, note the ROLE = WITNESS setting on the endpoint. That's the only meaningful difference from the other servers.

-- ON THE WITNESS

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '%%SECUREPASSWORD%%';
GO

CREATE CERTIFICATE %%WITNESS%%_cert
    WITH SUBJECT = '%%WITNESS%% certificate',
    START_DATE = '2024/01/01',
    EXPIRY_DATE = '2027/01/01';
GO

CREATE ENDPOINT endpoint_mirroring
    STATE = STARTED
    AS TCP (LISTENER_PORT = %%PORT%%, LISTENER_IP = ALL)
    FOR DATABASE_MIRRORING (
        AUTHENTICATION = CERTIFICATE %%WITNESS%%_cert,
        ENCRYPTION = DISABLED,
        ROLE = WITNESS
    );
GO

BACKUP CERTIFICATE %%WITNESS%%_cert
    TO FILE = 'C:\%%WITNESS%%_cert.cer';
GO

Copy %%WITNESS%%_cert.cer to the principal and mirror servers.

Step 4: Exchange Certificates and Grant Endpoint Access

This is where mutual trust is established. Each server needs a login, a user mapped to that login, and the other servers' certificates imported and associated with those users. Then endpoint CONNECT permission is granted.

Back on the Principal

By this point you should have %%MIRROR%%_cert.cer and %%WITNESS%%_cert.cer sitting in C:\ on the principal.

-- ON THE PRINCIPAL AGAIN

-- Set up mirror login and import mirror's certificate
CREATE LOGIN %%MIRROR%%_login WITH PASSWORD = '%%SECUREPASSWORD%%';
GO
CREATE USER %%MIRROR%%_user FROM LOGIN %%MIRROR%%_login;
GO
CREATE CERTIFICATE %%MIRROR%%_cert
    AUTHORIZATION %%MIRROR%%_user
    FROM FILE = 'C:\%%MIRROR%%_cert.cer';
GO
GRANT CONNECT ON ENDPOINT::endpoint_mirroring TO [%%MIRROR%%_login];
GO

-- Set up witness login and import witness's certificate
CREATE LOGIN %%WITNESS%%_login WITH PASSWORD = '%%SECUREPASSWORD%%';
GO
CREATE USER %%WITNESS%%_user FROM LOGIN %%WITNESS%%_login;
GO
CREATE CERTIFICATE %%WITNESS%%_cert
    AUTHORIZATION %%WITNESS%%_user
    FROM FILE = 'C:\%%WITNESS%%_cert.cer';
GO
GRANT CONNECT ON ENDPOINT::endpoint_mirroring TO [%%WITNESS%%_login];
GO

Back on the Mirror

-- ON THE MIRROR AGAIN

-- Import principal's certificate
CREATE LOGIN %%PRINCIPAL%%_login WITH PASSWORD = '%%SECUREPASSWORD%%';
GO
CREATE USER %%PRINCIPAL%%_user FROM LOGIN %%PRINCIPAL%%_login;
GO
CREATE CERTIFICATE %%PRINCIPAL%%_cert
    AUTHORIZATION %%PRINCIPAL%%_user
    FROM FILE = 'C:\%%PRINCIPAL%%_cert.cer';
GO
GRANT CONNECT ON ENDPOINT::endpoint_mirroring TO [%%PRINCIPAL%%_login];
GO

-- Import witness's certificate
CREATE LOGIN %%WITNESS%%_login WITH PASSWORD = '%%SECUREPASSWORD%%';
GO
CREATE USER %%WITNESS%%_user FROM LOGIN %%WITNESS%%_login;
GO
CREATE CERTIFICATE %%WITNESS%%_cert
    AUTHORIZATION %%WITNESS%%_user
    FROM FILE = 'C:\%%WITNESS%%_cert.cer';
GO
GRANT CONNECT ON ENDPOINT::endpoint_mirroring TO [%%WITNESS%%_login];
GO

Back on the Witness

-- ON THE WITNESS AGAIN

-- Import principal's certificate
CREATE LOGIN %%PRINCIPAL%%_login WITH PASSWORD = '%%SECUREPASSWORD%%';
GO
CREATE USER %%PRINCIPAL%%_user FROM LOGIN %%PRINCIPAL%%_login;
GO
CREATE CERTIFICATE %%PRINCIPAL%%_cert
    AUTHORIZATION %%PRINCIPAL%%_user
    FROM FILE = 'C:\%%PRINCIPAL%%_cert.cer';
GO
GRANT CONNECT ON ENDPOINT::endpoint_mirroring TO [%%PRINCIPAL%%_login];
GO

-- Import mirror's certificate
CREATE LOGIN %%MIRROR%%_login WITH PASSWORD = '%%SECUREPASSWORD%%';
GO
CREATE USER %%MIRROR%%_user FROM LOGIN %%MIRROR%%_login;
GO
CREATE CERTIFICATE %%MIRROR%%_cert
    AUTHORIZATION %%MIRROR%%_user
    FROM FILE = 'C:\%%MIRROR%%_cert.cer';
GO
GRANT CONNECT ON ENDPOINT::endpoint_mirroring TO [%%MIRROR%%_login];
GO

Common Issues to Watch For

A few things that catch people out during workgroup mirroring setup:

• Certificate file paths: The scripts use C:\ as the backup location. Make sure the SQL Server service account has read/write access to that path, and that you're actually copying the .cer files between servers before importing them.
• Firewall rules: The TCP port you choose for the endpoint (commonly 5022) needs to be open between all three servers in both directions.
• Expired certificates: The original script used expiry dates from 2020. Set dates that reflect your actual certificate lifecycle. When a certificate expires, mirroring will break.
• Encryption = DISABLED: This is acceptable in isolated network environments but think carefully before using it across any network you don't fully control. If encryption is required, use ENCRYPTION = REQUIRED ALGORITHM AES.
• Master key backup: Back up your database master keys. If you lose them, you lose access to anything encrypted with them.

Key Takeaways

• SQL Server mirroring in a workgroup uses certificate-based authentication to replace the Windows trust that Active Directory would normally provide.
• The setup follows a three-phase process: create endpoints and certificates on each server, exchange certificate files manually, then import certificates and grant endpoint permissions.
• A witness server enables automatic failover and can run on SQL Server Express, keeping costs low.
• Certificate expiry dates matter. Expired certificates will silently break your mirroring session. Set a calendar reminder to renew them well before they lapse.
• Database mirroring is deprecated. If you're designing a new HA solution, evaluate Always On Availability Groups instead.

If you're running database mirroring in production and aren't sure whether your configuration is healthy, DBA Services offers SQL Server health checks that cover high availability configuration, certificate validity, endpoint status, and failover readiness. Our managed support clients also receive proactive monitoring so issues like expired certificates are caught before they cause an outage. Get in touch to find out how we can help.