Sign in
 
Home20122008 R2200820052000LibraryForums
Note: Forums will be making significant UX changes to address key usability improvements surrounding search, discoverability and navigation. To learn more about these changes please visit the announcement which can be found HERE.
SQL Server >
> 
> 
SQL account failed to connect to the database after AG failover

Answered SQL account failed to connect to the database after AG failover

  • Monday, June 04, 2012 4:25 AM
     
     
    Sign In to Vote
    0

    Hi all,

    I set up availability group between two SQL server instances, Test1 and Test2, and then i created a SQL user account "AGconnect" on primary and secondary database with DB owner role. After failover to Test 2, then failover back to test1, i found the connection is failed to connect to test with insufficient permission of AGconnect SQL account. 

    Any idea?

    Thanks!

     

All Replies

  • Monday, June 04, 2012 12:42 PM
     
     Answered
    Sign In to Vote
    1

    Yes. You need to make sure that your SQL login has the same SID on each instance and can therefore be mapped to the relevant AG databases (you will need an initial AG failover to configure this on each sql instance - so that the database will be open to check for orphans).

    Use this proc to generate and script the logins, sids and passwords and only create those you require on the destination server/s http://support.microsoft.com/kb/918992/

    Regards,
    Mark Broadbent.

    Contact me through (twitter|blog)
    Please click "Propose As Answer" if a post solves your problem
    or "Vote As Helpful" if a post has been useful to you