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.
Best Answer
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:
Remus Rusanu (in his answer) correctly identified how to determine the security in place on a mirroring endpoint; as in:
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.