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
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
To reconfigure the database to use the new witness, I entered the following command on the Primary server
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];
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...
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';
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
SELECT name, endpoint_id, protocol_desc, type_desc, role_desc
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.