Configuring Mirroring with Certificate Authentication
If the SQL Server service is not associated with a domain-based account but a local account or virtual account, we must configure certificate-based authentication. Below are the steps to configure mirroring with certificate authentication.
- To determine whether a database master key exists we query the sys.symmetric_keys catalog view.
SELECT name, algorithm_desc FROM sys.symmetric_keys
Follow link for more information on Creating a Symmetric Key using Transact-SQL
- If no key exists, we can use a Transact-SQL statement (Click here for example) to create a database master key on our servers.
- We then follow this by then creating the ENDPOINT_MIRROR click here to view
- After the certificates have been backed up on each instance, we then need to copy them across to the partner instance so we can create logins and users based on those certificates
USE master; CREATE LOGIN <myServer2_Name>_login WITH PASSWORD = 'Pa$$w0rd'; GO
CREATE USER <myServer2_Name>_user FOR LOGIN <myServer2_Name>_login; GO
- Create a login on instance on our original server by using the 2nd server’s certificate <myServer2_Name>_cert.cer certificate:
CREATE CERTIFICATE <myServer2_Name>_cert AUTHORIZATION <myServer2_Name>_user FROM FILE = 'C:\backup\<myServer2_Name>_cert.cer' Go
- run on instance SQL server 1, grants the login associated with SQL server 2 permission on the endpoint:
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [<myServer2_Name>_login]; GO
Repeat this step on the mirror instance but, in this case, grant permission to the login associated with the primary instance
- run on instance on server2, the partner for the AdventureMirror database is configured as server1.myDomain.com:
ALTER DATABASE AdventureMirror SET PARTNER = 'TCP://<myServer1_Name>.<myDomain>.com:7024'; GO
- You then repeat the process on the primary instance with the address of the mirror instance endpoint:
ALTER DATABASE AdventureMirror SET PARTNER = 'TCP://<myServer2_Name>.<myDomain>.com:7024';
To configure server SQL-CORE as the witness server for the AdventureMirror database, we would need to run on the principle server:
ALTER DATABASE AdventureMirror
SET WITNESS = ‘TCP://SQL-CORE:7024′