Allow Network Access to a Database Mirroring Endpoint Using Windows Authentication (SQL Server)
Applies To: SQL Server 2016
Using Windows Authentication for connecting the database mirroring endpoints of two instances of SQL Server requires manual configuration of login accounts under the following conditions:
If the instances of SQL Server run as services under different domain accounts (in the same or trusted domains), the login of each account must be created in master on each of the remote server instances and that login must be granted CONNECT permissions on the endpoint.
If the instances of SQL Server run as the Network Service account, the login of the each host computer account (DomainName\ComputerName$) must be created in master on each of the remote server instances and that login must be granted CONNECT permissions on the endpoint. This is because a server instance running under the Network Service account authenticates using the domain account of the host computer.
Ensure that an endpoint exists for each of the server instances. For more information, see Create a Database Mirroring Endpoint for Windows Authentication (Transact-SQL).
To configure logins for Windows Authentication
Also, to ensure that the login user has access to the endpoint, use the GRANT statement to grant connect permissions on the endpoint to the login. Note that granting connect permissions to the endpoint is unnecessary if the user is an Administrator.
For more information, see Grant a Permission to a Principal.
The following Transact-SQL example creates a SQL Server login for a user account named Otheruser that belongs to a domain called Adomain. The example then grants this user connect permissions to a pre-existing database mirroring endpoint named Mirroring_Endpoint.
USE master; GO CREATE LOGIN [Adomain\Otheruser] FROM WINDOWS; GO GRANT CONNECT on ENDPOINT::Mirroring_Endpoint TO [Adomain\Otheruser]; GO