1

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.

2

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';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.