Solved

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

Posted on 2011-03-21
2
334 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
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

777 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