Orphaned SQL Logins on the Mirror

Hi there,

I’ve recently set-up database mirroring between two servers in the same domain: DMZSQL01 and DMZSQL02 with a witness of DMZSQL03.

The mirroring as all gone according to plan.

Set up all the users/databases on the Mirror (DMZSQL02) and then do a back-up/restore to sync the databases and then enable the mirroring, this has all gone fine and we have lots of synchronised/mirrored databases now. However, if I do a failover the logins on the mirror are not valid, they are in SQL Server and also the database but they don’t work. I’ve read through other posts and found links to this SQL Server article:

http://support.microsoft.com/kb/918992/

This has had no effect though. Do I need to remove everything off my mirror and start again setting up the users first and then doing a backup/restore or is there some other way. Both servers are reporting the SID’s are the same for the logins which do not work. However the principle_ID is different, I'm quite sure if this is a problem or not?

Can anyone point me in the right direction of what to do next? Or have any ideas.

Thanks

Ed

I am quite sure the problem is just like your thread title orphaned logins

If you can, failover to the mirror server, and run this to list all orphaned logins in the database you want to fix (it's per-database level, not per-server-instance)

EXEC sp_change_users_login 'report'

I imagine you just have to fix them on the mirrored servers, and then failback to the primary server again after it's fixed and working on the mirror server

|||

Thanks,

I've just run that as you said and it's not showing the SQL Login I need in the database. Can you describe how I can fix/add that user?
The sp_change_users_login 'report' stored proc, does that list the users which should be there but aren't or those which are there which are supposed to be in the DB?

The SQL Login is showing in the User group for the database but not when I run the EXEC sp_change_users_login 'report' command?

Thanks
Ed

|||

Orphaned login is where the login exist at the server level, but they are not linked to the login credentials stored inside each database

I'd say first step is to open the login under Server -> Security -> pick the login -> User Mapping

if it's all blank, it's most likely orphaned. When you try to add permission on the database, it'll give you an error <- this almost guarantees it's orphaned

Then see this thread on how to auto-fix them

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12615

|||

Search this forum for 'orphaned'.

|||

Hi, thanks for the help so far it’s much appreciated and very helpfull.

It appears the SP to fix the orphaned users did work; however, I now have a different problem.

The databases I am mirroring are used by our web site so in our web pages we have connection strings (and a few even use ODBC connections). In the connection strings we use an IP Address of the server (or server name) and database name/SQL Server login details.

Let’s say my principle server is 192.168.3.50 and my Mirror is 192.168.3.51 and my Witness is 192.168.3.52.

My web pages all use 192.168.3.50 as the server in their connection string, however when the database used is failed over, the active database now lives on 192.168.3.51, at this point the web page can’t find the database (as it isn’t active on the principle (192.168.3.50) any more) so reports a login failure. What I guess I need is some method for SQL server to automatically pull the data from 192.168.3.51 when 3.51 is active…. Does that make sense? So if the database on 192.168.3.50 is damaged the same server is still accessed by applications but data is pulled from 192.168.3.51 instead

If there any SQL Server mechanism I can set-up so the database continues to service requests seamlessly regardless to which server the database is currently active on?

Thanks

Ed

|||

this is off topic already, you should mark this one as answered, and create a new thread

in short, you should read more into mirroring and search a bit

other than .Net ADO which supports builtin failover server connection string, you have to handle the failover in the client side, on your own

it is beyond SQL server's scope.

We use DNS Alias so we can change the IP on the DNS without changing configurations for applications

but that means we don't have AUTOMATIC FAILOVER at all, but a manual failover with shorter down time (provided we get notified and react quickly)