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