How do I keep SQL users and passwords in sync in a log shipping configuration?

Hello,

We have a 'replica' of an erp application setup in a remote location. The database server uses SQL 2008. All databases are in sync using log shipping, which is set for every six hours as transactions are low and the business is happy with this level of recovery/loss. The server uses built in sql users for security, and not domain logins unfortunately. I have successfully transferred sql users and passwords across to the secondary server using sp_help_revlogin.

The problem is keeping users and passwords in sync. The application requires users to change passwords every three months, and so we need to get those changes, and any additional users transferred to the secondary server on a regular basis - how can this be done? I cant use sp_help_revlogin as this only creates new users and wont update existing passwords.

Thanks in advance!
CKSWAsked:
Who is Participating?
 
Ryan McCauleyConnect With a Mentor Data and Analytics ManagerCommented:
You'd have to set up some home-rolled replication that uses sp_rev_login to get the password hashes, compare them between servers, and then issue a series of

ALTER LOGIN SomeLogin PASSWORD = 0x234234... HASHED

Open in new window


Here's the syntax for ALTER LOGIN: http://msdn.microsoft.com/en-us/library/ms189828.aspx

On the subscriber server. I've never set something like that up, but if you're not sure how to do it, I can take a crack at writing a script. It seems like you'd be able to do it, since you don't need the passwords themselves, just the hashes.
0
 
CKSWAuthor Commented:
Hi Ryan,

thanks for the offer to do the script - I'd like to take you up on it! Your method sounds like the right way. Just surprising to not find any reference to this at all. My SQL skills are far from great and scripting isnt my friend!!

Thanks again!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.