I setup my first AlwaysOn Cluster following the directions here, with some help from the documentation available on bretozar.com. When I started to test the Listener using the UDL process here I wasn't able to connect with any of the user accounts which were built while building the cluster.
I then signed into the current Primary cluster server (server-A) and created a local test user. I was able to get a Successful Connection while accessing though the Availability Group Listener. However When I failed the group over to (server-B) I can no longer connect to the database.
I didn't see the test user in the list of users for that database on server B, now the primary, and when I try to make it again I get the 15023 Error "User already exists." I didn't have any success with the recommended fix
EXEC sp_change_users_login 'Auto_Fix', 'myUser'
The Test-users is also configured with:
Server Roles = public
User Mapping = Always_On_DB
Securables = Server_A (Server-B is not listed as an option, and the Availability Cluster does not save is it is selected.
I can re-add the Test-user to Server-B, I can access all other databases. The 15023 error appears when I try to add the Always_on_DB to the user properties.
Any help would be appreciated, as right now the cluster only allows access when the primary server is on Server-A.
Best Answer
It's likely that your Test-User account has different SIDS on the primary and replica servers. The best way to resolve this is drop the Test User on the Secondary, generate a create user statement from the primary with the proper SID (e.g. sp_revlogin, dbatools.io, or another tool of your choice) and then run that generated CREATE USER statment against the secondary. This will create a user that has the same SID on both replicas so you won't get this error going forward.
Another option, and one that I would recommend instead, is to convert this database to a Partially Contained Database so that all SQL Logins you create with the database are automatically transferred over to any replicas you setup. This is a very handy feature (if the limitations don't adversely affect you) for databases in Availability Groups and will help minimize the administrations headaches that come with them.