locked
Database Mirroring Login Accounts RRS feed

  • Question

  • I need to setup database mirroring between 2 separate SQL 2012 instances. The documentation states that if the 2 server instances are running under different user accounts then these need to be manually created on both instances. Is this login account the account that was used when installing SQL or is it the account assigned to run the SQL server service account?

    Thanks

    Alistair

    Monday, March 10, 2014 10:34 AM

Answers

    • 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.

    I am still unsure what it means when it refers to 'If the server instances run as different user accounts'. Is this the account that shows in services under our instance 'SQL Server (MSSQLSERVER)?

    Thanks


    Did you read below example(http://technet.microsoft.com/en-us/library/ms366346.aspx#CreateLogin)

    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 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.

    Is it still unclear


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    • Marked as answer by AllyRussell Monday, March 10, 2014 5:10 PM
    Monday, March 10, 2014 1:44 PM

All replies

  •  Is this login account the account that was used when installing SQL or is it the account assigned to run the SQL server service account?

    Thanks

    Alistair

    This login would be login which you logged in into SQL Server to configure mirroring.Make sure both principal and mirror server SQL server service runs with this account.Generally domain account is preffered.If it is workgroup server.Create account on both machine give it admin privileges in SQL Server and login into sql server using this account configure mirroring and start SQL server service with this account.Make sure this has grant connect permission on endpoints you create

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Monday, March 10, 2014 11:02 AM
  • I have not yet configured the mirroring and will be using domain accounts. I have the following information from a Microsoft article:

    • 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 the database and is recommended.

    • 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.

    I am still unsure what it means when it refers to 'If the server instances run as different user accounts'. Is this the account that shows in services under our instance 'SQL Server (MSSQLSERVER)?

    Thanks


    Monday, March 10, 2014 1:27 PM
    • 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.

    I am still unsure what it means when it refers to 'If the server instances run as different user accounts'. Is this the account that shows in services under our instance 'SQL Server (MSSQLSERVER)?

    Thanks


    Did you read below example(http://technet.microsoft.com/en-us/library/ms366346.aspx#CreateLogin)

    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 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.

    Is it still unclear


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    • Marked as answer by AllyRussell Monday, March 10, 2014 5:10 PM
    Monday, March 10, 2014 1:44 PM