SQL account creation process is slightly different for SQL servers which are on always on availability group. The SQL account permissions won't sync properly if the SID of the accounts are not same in primary and secondary SQL server.
Since the SQL account created in the primary SQL server cannot sync the permission properly in secondary server, after the fail over (when the secondary sever will become primary and vice versa) the account will have permission issue and cannot be used anymore to login. To avoid this issue, either 1) we should create the accounts in primary and secondary server with same SID, or 2) if the accounts are already created, delete/drop the account in the secondary SQL server and have the account recreated using same SID (this is not recommended if the account has many database mappings, it will complicate things).
Create a SQL account in primary and secondary SQL server
- Login to the primary SQL server with admin credential and run the below -
Use [master]
GO
CREATE LOGIN [SQL_Login_Account_Name]
WITH PASSWORD = N 'Account_Password',
DEFAULT_DATABASE=[master]
GO
- Provide privilege to this account
- The account will not replicate automatically to the secondary SQL server. To have this account available in the secondary SQL server, we will have to re-create it in secondary. Before we do that, we need the SID of the account. Run the below to get the SID.
Select name, sid FROM sys.server_principals WHERE name = 'SQL_Login_Account_Name'
- Login to the secondary SQL server and run the below -
CREATE LOGIN [SQL_Login_Account_Name]
WITH PASSWORD=N 'Account_Password',
SID = SID_OF_THE_ACCOUNT_FROM_PRIMARY_SQL,
DEFAULT_DATABASE=[master]
GO
- Provide same privilege as given in primary
- Go back to the primary SQL server and provide DB access to this account. The new user account will be automatically replicate in secondary SQL server with same permission.
If the accounts are already created in primary and secondary SQL server then the steps will remain same, except -
1) Drop the account from the secondary sql server
2) Get the SID of the account from the primary sql server
3) Recreate the account in secondary sql server using the same SID
GUI doesn't support creation of accounts using same SID, hence this can be achieved only using TSQL.
