SQL Always on availability with failover and mixed authentication 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.
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 &lt;login_name&gt;   
WITH PASSWORD = '&lt;use_a_strong_password_here&gt;',  
SID = &lt;SID&gt;;



From: Troubleshoot Orphaned Users (SQL Server)

Or use a Contained Database User:

CREATE USER user_name WITH PASSWORD = 'strong_password';