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.