Monday, June 04, 2012 4:25 AM
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.
Monday, June 04, 2012 12:42 PM
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,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