?
Solved

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

Posted on 2011-03-21
2
Medium Priority
?
355 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 2000 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

800 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