SQL Always on availability with failover and mixed authentication

Question

I have 2 boxes running SQL Server 2016, and set up using Always On Availability. I have a listener set up to choose which server to talk to in case of a failover, which seems to work fine, except for the fact that I'm using mixed authentication, and my connection string is using a SQL Server authentication user rather than a windows one.

For example, the connection string in my application is:

Database=MySQLServer;Server=AGListener;User Id=sqluser;Password=XXXXXXX;

when I'm working against the primary, this works fine. when I failover to the secondary, then I get an error that 'sqluser doesn't have any permissions to the database'. If i run the below script on the secondary server:

EXEC sp_change_users_login 'Update_One', 'sqluser', 'sqluser'

then it works fine again, but when I failover back to the original primary, I get the error again. it looks like on every failover, I have to run the update user script for the connection string to work.

Is using SQL Server authentication for a connection string in an AG environment just not a good idea? Or is there a step I'm missing? I would like my failover to be automatic, rather than needing to remember to run a script every time.

Answer 1

Either create the login on instances hosting the replicas specifying the same SID as on the instance hosting the primary:

To Resolve an Orphaned User

In the master database, use the CREATE LOGIN statement with the SID option to recreate a missing login, providing the SID of the database user obtained in the previous section:

CREATE LOGIN <login_name>   
WITH PASSWORD = '<use_a_strong_password_here>',  
SID = <SID>;

From: Troubleshoot Orphaned Users (SQL Server)

Or use a Contained Database User:

CREATE USER user_name WITH PASSWORD = 'strong_password';