Solved

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

Posted on 2011-03-21
2
353 Views
Last Modified: 2012-05-11
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!
0
Comment
Question by:CKSW
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 500 total points
ID: 35181498
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
 

Author Comment

by:CKSW
ID: 35199397
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

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question