I am setting up a 3 node cluster as part of an availability group.
Initially I tested failover between nodes using SQL Management Studio and everything failed over successfully when I stopped a node, and I was still able to write queries.
I started to test with an application which connects using an SQL user and whenever I would switch nodes, I would get login failed.
I believe the cause of this issue is because the server logins SID's which are tied to the database are different than the server logins on the other nodes which resulted in login failed. Please correct me if I am not understanding this correctly.
If I am understanding this correctly, how can I ensure that the server logins SID's are the same between nodes? Is there a way of copying this over or how is this supposed to be done?
I read a little about contained databases where I could just set the login on the database itself vs. creating a server login but I would rather not go down that route.