In an sql authentication environment with an automatic failover in database mirroring how to you manage new logins which have been created on the principle since the start of mirroring? Since the master cannot be mirrored, and the mirror database cannot be read during mirroring (except as a snapshot) in order to find the missing logins, I assume that only after failover a script should run to create the new logins and then run sp_change_users_login . The qestions are:
1) should the script create a new login first and then run sp_change_users_login with option update_one , or should sp_change_users_login using option
Auto_Fix create the missing logins?
2) But what is the password of these users? is it initially NULL , as a consequence of sp_change_users_login? What about the SIDs?
3) Or should we bypass sp_change_users_login altogether and use
CREATE LOGIN <loginname> WITH PASSWORD = <password>, SID = <sid for same login on principal server>,...as described in http://blogs.msdn.com/chadboyd/archive/2007/01/05/login-failures-connecting-to-new-principal-after-failover-using-database-mirroring.aspx
4) What is the event that would trigger this script to run after the aitomatic failover ?
Is there a definitive MIcrosoft agreed apon and recommended method to tackle this?
If you want your application users to connect after failover you better create those logins beforehand @. both the principal and @. mirror server @. the time of configuring mirroring so that they will be available to you but will be mismatched and after failover you can correct them using sp_change_users_login sp.
and for the less important ones you can make use of SSIS to transfer logins frequently so that after failover you can just restore the master syslogins table or master db and fix the mismatch by change users login sp......
|||
Thank you for your anwer. My question was about the new logins created after mirroring had begun. Of course before mirroring started the existing logins were recreated on the mirrorserver beforehand . The question was whether sp_change_users_login should be used with update_one or Auto_Fix or not used at all . The auto fix option creates a login if there isn't one. The update_one option does not .
Assuming rhat SSIS would transfer the new logins from the princiopal master to the mirror master while database mirroring would transfer the users from the principal to the mirror. Will the SIDs be in order? If a script has to be run it would have to be run automatically so what is the trigger that this script will be triggered by?
|||Actually there are a few issues here:
Firstly SSIS will not help you because as of 2005 the Transfer Logins task does not transfer the passwords, it resets the passwords which to be honest wouldn't help especially if you are using the High Availability option.
Next, you need to take care of the SID's otherwise you will have to sync them. See this KB article
Lastly, In 2005 you cannot create a login which has a default database that is offline. So if an app needs to use Automatic Client Redirect in a High Availability setup and the account is created on the Principal after mirroring has been configured, you will not be able to add the login on the Mirror. The only workaround is to bring the mirror online periodically and sync the logins. I heard it may be fixed in 2008 however; I have not tried yet, maybe someone can clarify for me.
