Setting Up Login Accounts for Database Mirroring
For two server instances to communicate in a database mirroring session, 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. For information about this endpoint, see Database Mirroring Endpoint.
Creating User Logins
Information in the master or msdb system databases cannot be mirrored. For role switching to work effectively for all the database users, therefore, user logins with connect permissions on the principal server instance must also exist on the mirror server instance.
The impact of this requirement depends on whether the mirroring partners run as the same domain user account:
If the partners run as the same domain user account, the correct user logins exist automatically in both master databases. This simplifies the security configuration the database and is recommended.
If the partner server instances run as different user accounts, user logins on the principal server instance must be manually reproduced on the mirror server instance. To avoid orphaned users after a role switch, you should routinely maintain the same set of user logins on both the principal and mirror database. However, if any users are orphaned after a role switch, you can set up their logins at that time.
Creating a Login for a Different Account
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 in the syslogins table of the master database of each server instance. For more information, see CREATE LOGIN (Transact-SQL).
Important
If you run SQL Server under a non-domain account, you must use certificates. For more information, see Using Certificates for Database Mirroring.
For example, for the server instance sqlA, which runs under loginA, to connect to the server instance sqlB, which runs under loginB, loginA must be in the syslogins table on sqlB, and loginB must be in the syslogins table 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 ... |
|---|---|
sqlA |
sqlB and sqlC |
sqlB |
sqlA and sqlC |
sqlC |
sqlA and sqlB |
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.
Granting Connect Permission
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).
Note
For information about this endpoint, see Database Mirroring Endpoint.
To use Windows Authentication
See Also