SQL Server 2005 Mirroring and Users

Setting up a SQL Server 2005 mirrored environment outside of a Domain where the client connects using Windows Authentication? We had oddles of fun with this on MiVoice Vote, as the Principal and Mirror servers have no idea about each others users so you need to do a bit of jiggery pokery to get your code to connect to each one succesfully.

Setup your mirroring environment as normal, creating a SQL Login / Database user as normal on the Principal tied to the Windows User the client will connect as. Then fail the mirror over so that the roles are swapped (Principal becomes Mirror and vice versa). Now create another SQL Login / Database user (with a different username) on the new Principal (was the Mirror) tied to the Windows User (you have created the Windows User accounts on the two machines with the same username/password, haven't you?) on that machine.

Fail back and everything should be good to go. The diagram below shows how it ended up on MiVoice Vote:

You'll need to make sure that the two database users have the same access rights as each other.

One Comment Filed Under [ SQL Server 2005 ]

Comments

# re: SQL Server 2005 Mirroring and Users
Gravatar
I think you're also going to have to blog on how to get images into blog posts.

Or tell me at the very least :)
Left by Neil R on 7/14/2006 12:18 PM

Leave Your Comment

Title*
Name*
Email (never displayed)
 (will show your gravatar)
Url
Comment*

Please add 7 and 4 and type the answer here:

Preview Your Comment.