How can I inspect the security settings of a SQL Server Endpoint?

Question

I am in the process of creating a TEST environment for our SQL Server development staff.

In production we have 3 SQL Servers, SQL01 contains several databases that are mirrored to SQL02. SQL03 acts as the witness in a "high safety with automatic failover" or synchronous configuration.

I've used VMWare P2V to virtualize all three machines onto separate hardware, reconfigured the SID's of the machines, and blackholed the IP addresses of our production servers from these new machines.

I had initially forgotten to blackhole the production witness machine, so the databases on the TEST machines were still using the SQL03 machine as the witness. Noticing the issue, I decided to reconfigure the databases on TEST to point to the newly virtualized TEST witness, call it TEST03.

To reconfigure the database to use the new witness, I entered the following command on the Primary server TEST01:

ALTER DATABASE [TestDB] SET WITNESS = 'TCP://TEST03.domain.inet:5022';

The response was unexpected:

The ALTER DATABASE command could not be sent to the remote server instance
'TCP://TEST03.domain.inet:5022'.  The database mirroring configuration was
not changed.  Verify that the server is connected, and try again.

I was very perplexed at this error message since the configuration does work on the production machines, and has not been modified in any way on the test machines.

In order to get this to work I needed to create a LOGIN on the test witness:

CREATE LOGIN [DOMAIN\SQLServiceAccount] FOR WINDOWS WITH DEFAULT_DATABASE [Master];

and GRANT it CONNECT rights on the endpoint in question:

GRANT CONNECT ON ENDPOINT::Mirroring TO [DOMAIN\SQLServiceAccount];

I was then able to successfully point the mirrored databases on the TEST environment to the new test witness.

How can I inspect the production witness endpoint to see what security is associated with it?

I assume there must be some system catalog that I can inspect, however Books-on-Line does not seem to have anything specific for Endpoints, and Bing is well, Bingless...

---

Additional Info:

SELECT ep.endpoint_id, p.class_desc, p.permission_name, ep.name, sp.name 
FROM sys.server_permissions p
    INNER JOIN sys.endpoints ep ON p.major_id = ep.endpoint_id
    INNER JOIN sys.server_principals sp ON p.grantee_principal_id = sp.principal_id
WHERE class = '105';

returns:

endpoint_id class_desc  permission_name endpoint_name   principal_name
2           ENDPOINT    CONNECT TSQL    Local Machine   public
3           ENDPOINT    CONNECT TSQL    Named Pipes     public
4           ENDPOINT    CONNECT TSQL    Default TCP     public
5           ENDPOINT    CONNECT TSQL    Default VIA     public

And:

SELECT name, endpoint_id, protocol_desc, type_desc, role_desc 
FROM sys.database_mirroring_endpoints;

Returns:

name        endpoint_id  protocol_desc  type_desc           role_desc 
Mirroring   65536        TCP            DATABASE_MIRRORING  WITNESS

There appears to be no entry in sys.server_permissions for the mirroring endpoint object. No major_id and no minor_id matches 65536. Also, none of the system databases contain any reference to the endpoint.

I'm at a loss.

Answer 1

After spending a portion of the day dealing with moving mirrored databases from server to server in our DEV environment in preparation for our move to SQL Server 2012, I came across this MSDN document that explains that ENDPOINT security is determined solely based on the type of account SQL Server is running under. If SQL Server is using a domain account, then that account automatically has access to the endpoint. If SQL Server is using a built-in account such as [Network Service] or [Local System] etc, then the endpoint must be configured with a security certificate, and only holders of the certificate can access the endpoint.

The relevant section from the document:

Determining the Authentication Type for a Database Mirroring Endpoint

It is important to understand that the SQL Server service accounts of your server instances determine what type of authentication you can use for your database mirroring endpoints, as follows:

If every server instance is running under a domain service account, you can use Windows Authentication for your database mirroring endpoints. If all the server instances run as the same domain user account, the correct user logins exist automatically in both master databases. This simplifies the security configuration for the availability databases and is recommended.

If any server instances that are hosting the availability replicas for an availability group run as different accounts, the login each account must be created in master on the other server instance. Then, that login must be granted CONNECT permissions to connect to the database mirroring endpoint of that server instance.

If your server instances use Windows Authentication, you can create database mirroring endpoints by using Transact-SQL, PowerShell, or the New Availability Group Wizard.

Note:

If a server instance that is to host an availability replica lacks a database mirroring endpoint, the New Availability Group Wizard can automatically create a database mirroring endpoint that uses Windows Authentication.

If any server instance is running under a built-in account, such as Local System, Local Service, or Network Service, or a nondomain account, you must use certificates for endpoint authentication. If you are using certificates for your database mirroring endpoints, your system administrator must configure each server instance to use certificates on both outbound and inbound connections.

There is no automated method for configuring database mirroring security using certificates. You will need to use either CREATE ENDPOINT Transact-SQL statement or the New-SqlHadrEndpoint PowerShell cmdlet. For more information, see CREATE ENDPOINT (Transact-SQL).

Remus Rusanu (in his answer) correctly identified how to determine the security in place on a mirroring endpoint; as in:

SELECT ep.name, sp2.name AS Grantee, sp.name AS Grantor, p.permission_name, ep.state_desc
FROM sys.server_permissions p 
    INNER JOIN sys.endpoints ep ON p.major_id = ep.endpoint_id
    INNER JOIN sys.server_principals sp ON p.grantor_principal_id = sp.principal_id
    INNER JOIN sys.server_principals sp2 ON p.grantee_principal_id = sp2.principal_id
WHERE p.class_desc = 'ENDPOINT' AND ep.type_desc = 'DATABASE_MIRRORING'

However in my case on SQL Server 2005 the original mirroring endpoints do not show up in the results of this query - I assume this is because they default security allows CONNECT access to the account SQL Server is running under (as explained above).

So the canonical answer to the question, how do you know who has CONNECT access to an ENDPOINT, is the sum of the query above, and the account SQL Server is using, along with any possible certificates you've configured with access to the endpoint.

Answer 2

I just happen to have set this up for a database in production and was wondering the same thing cause I needed to compare the configuration to another mirroring session to make sure I matched it correctly.

sys.dm_db_mirroring_connections should provide you with you want. It contains the principal_name which is:

Name of the login that was validated for connection permissions. For Windows Authentication, this value is the remote user name. For certificate authentication, this value is the certificate owner.

If you are working with a session that is paused though I am not sure this would still return the information, believe it only has active connections.

Answer 3

See the Books Online documentation for sys.database_mirroring_endpoints

For server object permissions you look at the usual place, sys.server_permissions. As with every case when Windows security is involved things are more complicated due to windows groups membership, and you also have to consider the permissions hierarchy.