Export (0) Print
Expand All

How to: Allow Database Mirroring Network Access Using Windows Authentication (Transact-SQL)

For database mirroring to use Windows Authentication, if the server instances involved in mirroring a database use the same domain user account, the correct logins exist automatically; you do not need to create them. However, if the server instances use different user accounts (either in the same Microsoft Windows domain or in trusted Windows domains), then on each instance, you must create a login for the startup service account of each of the other instances.

For more information, see Database Mirroring Transport Security.

NoteNote

Ensure that an endpoint exists for each of the server instances. For more information, see How to: Create a Mirroring Endpoint for Windows Authentication (Transact-SQL).

To allow database mirroring network access using Windows Authentication

  1. If the server instances use different domain user accounts for their startup service accounts, create a login for the user account of the remote server instance using a CREATE LOGIN statement with the FROM WINDOWS clause.

  2. Also, to ensure that the login user has access to the endpoint, 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.

This 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
NoteNote

For a complete example showing security setup, preparing the mirror database, setting up the partners, and adding a witness, see Setting Up Database Mirroring.

Community Additions

ADD
Show:
© 2014 Microsoft