Export (0) Print
Expand All

Set Up Login Accounts for Database Mirroring or Always On Availability Groups (SQL Server)


Applies To: SQL Server 2016

For two server instances to connect to each other's database mirroring endpoint point, the login account of each instance requires access to the other instance. Also, each login account requires connect permission to the Database Mirroring endpoint of the other instance.

The impact of this requirement depends on whether the server instances run as the same domain user account:

  • If 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 Database Mirroring and Always On Availability Groups.

  • If the server instances run as different user accounts, user logins on the server instance that hosts the principal server or primary replica must be manually reproduced on the server instance that hosts the mirror server or on every server instance that hosts a secondary replica. For more information, see Create a Login for a Different Account and Grant Connect Permission, later in this topic.

    System_CAPS_ICON_important.jpg Important

    To create a more secure environment, consider using separate domain accounts for each server instance.

If two server instances run as different accounts, the system administrator must use the CREATE LOGIN Transact-SQL statement to create a login for the startup service account of the remote instance for each server instance. For more information, see CREATE LOGIN (Transact-SQL).

System_CAPS_ICON_important.jpg Important

If you run SQL Server under a non-domain account, you must use certificates. For more information, see Use Certificates for a Database Mirroring Endpoint (Transact-SQL).

For example, for the server instance sqlA, which runs under loginA, to connect to the server instance sqlB, which runs under loginB, loginA must exist on sqlB, and loginB must exist on sqlA. In addition, for a database mirroring session that includes a witness server instance (sqlC) and in which the three server instances run under different domain accounts, the following logins must be created:

On instance...Create logins for and grant connection permission to ...
sqlAsqlB and sqlC
sqlBsqlA and sqlC
sqlCsqlA and sqlB
System_CAPS_ICON_note.jpg Note

It is possible to connect with the network service account by using the machine account instead of a domain user. If the machine account is used, it must be added as a user on the other server instance.

Once a login has been created on a server instance, the login must be granted permission to connect to the database mirroring endpoint of the server instance. The system administrator grants the connect permission using a GRANT Transact-SQL statement. For more information, see GRANT (Transact-SQL).

The Database Mirroring Endpoint (SQL Server)
Troubleshoot Database Mirroring Configuration (SQL Server)
Entity with relative path '../Topic/Troubleshoot%20AlwaysOn%20Availability%20Groups%20Configuration%20(SQL%20Server).md' can not be found, for source topic '{"project_id":"7d6ffa79-2ddc-4606-b774-a6a5d46784cd","entity_id":"e9f5287b-1325-4cda-88a6-19eaaa52a652","entity_type":"Article","locale":"en-US"}'.

Community Additions

© 2016 Microsoft