When we deploy a SQL Server Always-On, we MUST create the user logins manually to the secondary replica. This happens because SQL server keeps logins in the Master database which is not syncing.
To overcome this problem we need to run some SQL scripts in a certain order :
Select the login SID
From the master replica open SSMS and execute the next query, notice that you must copy both name and sid because we will need it for the second script.
SELECT name, sid FROM sys.server_principals
Create the login to Secondary Replica
On the secondary replica, execute the query to create the User Login, use the name and sid from the previous script and type password and default database.
CREATE LOGIN [MY LOGIN NAME] WITH PASSWORD=N'Myp@S$W0Rd' -- use the SID retrieved above , SID = 0x43D54D9AC2D6514A985D9J57D0A47R25 , DEFAULT_DATABASE=[MY DATABASE NAME] GO
After these steps the user logins exists on both SQL Server Replicas and the Always-On works seamlessly.
Related Links
Failover Clustering and Always On Availability Groups on Azure VMs