0

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.

leeman24
  • 147
  • 1
  • 9

1 Answers1

1

Your intuition is likely correct. You can verify that the SIDs are different between your nodes by running:

select name, sid from sys.server_principals

on each node and comparing them.

As far as how you're supposed to keep them in sync, do so at creation time (unfortunately, there's no way that I know of to change the SID post facto). That is, the create login statement has an optional clause to provide a SID. So I'd do this:

-- on node 1
create login [foobar] with password = 'str0ngp@ssword!';
select sid from sys.server_principals where name = 'foobar';

-- on subsequent nodes
create login [foobar] with password = 'str0ngp@ssword!', sid = <the sid you obtained from node 1>;

I've done it so that I generate my own SID and pass that even for node 1 (so that the same script is run across all servers), but I leave that as an exercise for the reader. :)

Ben Thul
  • 2,969
  • 16
  • 23
  • Thanks. I basically used the same method to get this issue working but using a stored procedure which generates the create login with the SID that you mentioned. I do like this method better as it should only be a one time thing and I don't need to create a stored proc for no reason. I will be using this method when I implement it outside of testing. Thanks again. – leeman24 Jul 09 '15 at 22:24