Log Shipping Passwords

Question

Log Shipping Passwords

joberlin

Old Hand

Points: 337
More actions
June 7, 2004 at 2:21 pm

#180380

I am trying to transfer my SQL logins along with the passwords from the primary to the secondary server. I have the log shipping setup as per TechNet's
article:
http://www.microsoft.com/technet/prodtechnol/sql/2000/main
tain/logship2.mspx
i.e.
1. Running sp_resolve_logins to populate the sysxlogins
in master.
2. Running sp_change_users_login to link the transferred
and resolved logins with their corresponding database
users and permissions
The very first time I transfer the logins the passwords are brought across
fine. If people change their passwords I want to
be able to run this regularily to refresh the logins and
update the secondary server with the new passwords.
Does anyone have some real-world experience on how best to set this up? Thanks in advance. Any help would be appreciated.
John

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

joberlin Old Hand Points: 337 More actions · Answer 1

Further to my initial post... I am looking for a straight forward best practice not a complex scripting exercise. What are people doing out there in the 'real-world'? Is it recommended to just leave the syslogins empty until you fail over and at that point populate it with the sp_resolvelogins?

John

Debbie Crosby SSC Veteran Points: 230 More actions · Answer 2

We have a fail over server and we use DTS. In DTS there is a Transfer Logins task that you can set up. Then schedule the task to run as frequently as you wish. That should keep the logins and passwords in sync. Then when we need to fail over to the secondary server we just run sp_resolve_logins.

Deb

joberlin Old Hand Points: 337 More actions · Answer 3

Hi Debbie, Thanks for your post. Yes, this is what I am doing. I am using the Transfer Logins DTS to export the logins out of the primary server and sp_resolve_logins to import the logins into the secondary server. This works great the first time. Running sp_resolve_logins subsequent times will not overwrite the existing logins in the secondary server. So if for example a user changes their password on the primary, the secondary server still has the old password. How are you managing the existing logins on the secondary server that were initially created with the first sp_resolve_logins?

John

Debbie Crosby SSC Veteran Points: 230 More actions · Answer 4

Hey John, We just have that DTS running all the time (once a minute). When we failed over to the backup server the passwords were in sync already - we only needed to run resolve logins. I am not sure what could be different on your systems... If I can come across anything I'll post again.

Deb