Ok so you have databases replicating using always on, but the application that you have uses an SQL level Authentication username and password. Then you have a problem, because ideally you would expect that when the username with its permission is created on one of the SQL instances, it would automatically replication that details to the other. But not in reality. The replication takes care of everything you have setup in the DBs and not everything on the DB server. Ok so what’s the problem, you just create the same user on both the SQL Server DBs and you should be good? No it does not work that way either. When you initiate failover from the primary to secondary, the database usernames are intact, but it tries to connect to the SQL username with the same SID, which does not exist in the secondary database, so you application fails, because it connect to the database after the failover.
So here is the steps you need to follow. (Assuming you have Always On setup correctly and failover works perfectly, just that your application does not work after failover)
1. Create the user with its permissions in the Primary SQL Server
2. Run the following query on the Primary Server
use master
go
select sid, name, dbname from syslogins
3. Pick the SID next to the user ID your application is using, let’s call it myuser for now. Once you get the SID, embed it and your use ID, password in the below code and execute it on the secondary server/s. (Make sure the password is same as the one in the primary)
CREATE LOGIN [scuser] WITH PASSWORD=’mypassword’, DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION FF, CHECK_POLICY N, sid=the-big-SID-number-goes-here
GO
ALTER LOGIN [myuser] DISABLE
GO
This will result in a disabled user, you can remove the DISABLE step above or just enable it from the database properties.
That’s it you are all set.