If you ever find yourself having to setup database mirroring between different, untrusted Windows Domains, one of the most common ways to do it involves creating certificates on each instance, creating logins and users for those certificates, and copying the certificates back and forth between the instances. I will have a future post that walks you through all of the gory details of doing this. In the meantime, I thought this little collection of certificate and endpoint queries would be a useful tool for troubleshooting, and checking your progress as you setup database mirroring using certificates.

-- Some useful certificate and mirroring endpoint queries
-- Glenn Berry 
-- March 2012
-- http://sqlserverperformance.wordpress.com/
-- Twitter: GlennAlanBerry
  
-- Get symmetric key information (do you have a ##MS_DatabaseMasterKey##)?
SELECT @@SERVERNAME AS [ServerName], name AS [KeyName], principal_id, symmetric_key_id, 
key_length, key_algorithm, algorithm_desc, create_date, modify_date, [key_guid]
FROM sys.symmetric_keys; 
 
-- Get certificate information
SELECT @@SERVERNAME AS [ServerName], name AS [CertificateName], certificate_id, principal_id, 
pvt_key_encryption_type_desc, issuer_name, [subject], [expiry_date], [start_date]
FROM sys.certificates;
 
-- Get mirroring endpoint information
SELECT @@SERVERNAME AS [ServerName], name AS [EndpointName], endpoint_id, principal_id, protocol, 
protocol_desc, [type], type_desc, [state], state_desc, is_admin_endpoint, 
[role], role_desc, is_encryption_enabled, connection_auth, 
connection_auth_desc, certificate_id, encryption_algorithm, encryption_algorithm_desc
FROM sys.database_mirroring_endpoints;
 
-- Get more mirroring endpoint information
SELECT @@SERVERNAME AS [ServerName], dme.protocol_desc, dme.type_desc, dme.state_desc, dme.role_desc, 
te.port, te.ip_address, is_encryption_enabled, connection_auth_desc, certificate_id, encryption_algorithm, 
encryption_algorithm_desc
FROM sys.database_mirroring_endpoints AS dme
INNER JOIN sys.tcp_endpoints AS te
ON dme.endpoint_id = te.endpoint_id;
 
-- Get endpoints that are using certificates
SELECT @@SERVERNAME AS [ServerName], dme.name AS [EndpointName], c.name AS [cert_name], 
c.[start_date], c.[expiry_date] 
FROM sys.database_mirroring_endpoints AS dme 
INNER JOIN sys.certificates AS c 
ON dme.certificate_id = c.certificate_id; 
 
-- Get logins that have been granted permissions to endpoints that are using certificates
SELECT @@SERVERNAME AS [ServerName], sp.name AS [login_name], e.name AS [EndpointName], 
c.name AS [cert_name], c.[start_date], c.[expiry_date] 
FROM sys.server_permissions AS ssp 
INNER JOIN sys.endpoints AS e 
ON e.endpoint_id = ssp.major_id 
AND ssp.class = 105                                               -- endpoint class
INNER JOIN sys.server_principals AS sp 
ON sp.principal_id = ssp.grantee_principal_id 
INNER JOIN sys.database_principals AS dp 
ON sp.[sid] = dp.[sid] 
INNER JOIN sys.certificates AS c 
ON dp.principal_id = c.principal_id 
WHERE e.[type] = 4;                                               -- database mirroring

Filed under: Microsoft, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 Tagged: Database Mirroring