May 13, 2008 at 2:32 am
Hi
Does anyone have a solution which would allow me to quickly re-set the sa password across a group of approx 30 servers, dispersed globally.
They are running SQL2005. Ideally I would like to be able to run it from one query window and change all 30 servers remotely. THis will ahve to be done every quarter.
thanks
Rob
May 13, 2008 at 3:43 am
I think thats not possible as this will cause security concerns.:D
May 13, 2008 at 3:33 pm
You could probably do it with a batch file full of sqlcmd calls. However, that means your sa passwords (don't use the same one for all servers, not good security practice) will be stored in plain-text.
Create a template file with the sqlcmd calls and use dummy data for the passwords. Store somewhere secure. For each time you need to run the file, create a copy, put in the correct passwords, run it then securely delete it.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
May 13, 2008 at 4:00 pm
http://www.sqlservercentral.com/articles/DTS/datamigrationquicklyinsertingnewdata/1419/
Instead of inserts, use sqlcmd with a string to change the passwords. Keep all the server names in a single column.
May 13, 2008 at 4:17 pm
Several years ago I wrote a process for this...
1: I used a "master" server that had all of the target servers as Linked Servers.
2: A procedure queried tested the current password and a table was used to hold the results of success or failure (to login with the current sa password).
3: Another procedure would take the new password, read the temp table and only update the password on servers that the initial test had passed (via osql & dynamic sql). Failures were addressed manually.
I can dig up the code, but it's nothing to write home about....
Your friendly High-Tech Janitor... 🙂
May 14, 2008 at 6:57 am
One possible option is to create a table on a central database that contains the name of all of the other databases, as well as an encrypted old sa and new sa password field. Write a CLR stored procedure that takes an encryption key as an input and performs the update to the new sa password, decrypting it using a compiled alorithm. Distribute this stored procedure and the CLR object to all of your servers. Give only the SA account execute permission to this stored procedure. You could then add a control CLR stored procedure that loops through and connects to each server in turn, running this distributed stored procedure (using an ADO.Net connection). Then you could create three additional CLR stored procedures, one to accept an encryption key and randomly assign new SA passwords before calling the other stored procedure, one to accept two encryption keys, an old and a new, and re-encrypt the sa passwords without changing them using the new key, and one accepting an encryption key and a database id, that would return the decrypted password. Then all you would have to do is come up with a way to backup and maintain your encryption keys, which you could change weekly. Not the best solution, since getting into the central database (and also getting access to the key) would give a user access to all databases, but centralizing this type of security always has it's risks. It does offer a single sa password that you would have to know (or you could use integrated security on the central database).
Just brain-storming!
May 14, 2008 at 8:31 am
microsoft site has a procedure called sp_help_revlogin which can copy usernames and encrypted passwords from one server to another. In the procedure it has :
"IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE ....."
If you change this to only copy the password where name ='sa' then you execute the procedure will get an UPDATE statement containing an encrypted string for the password. You could run this UPDATE statement on each server
May 14, 2008 at 12:26 pm
Have you thought about adding yourself as a sysadmin and then you can have a job running on each of the servers that randomizes the SA password every 30/60/90 days. That way you don't know the password, but as sysadmin you can change the password in the event that it is needed for something. After you finish the job will change the password again.
You can use the newid() function to generate a password.
May 14, 2008 at 12:34 pm
If you're going to (or have to) use the 'sa' account, you should know what the password is/was....I don't know if a random sa password process would pass a SOX audit.
Edit:
My experience w/SOX auditors has been that they usually don't know what they want in the first place, so it just might be valid. However, (IMO) I don't think it's a good security practice to have unknown passwords for high-level accounts....
Your friendly High-Tech Janitor... 🙂
May 14, 2008 at 1:48 pm
I have been part of a team that implemented that at a public company under SOX compliance.
It has been well received because they knew changes were being done by our own logins with SysAdmin rights, and the fact that nobody knew what the password was. I think auditors get more concerned when you ahve a group of 4 or 5 people all using the SA account to make changes to the system. That way you also minimize people using the sa account for things they shouldn't like dts packages reports etc.
I have worked in other places where nobody can change the sa account because they aren't sure what all might be using it. In my experience when you are able to say that no one knows the password and it can be changed immediately upon request the auditors have been fine with that.
May 23, 2008 at 3:28 am
Hi Everyone
thank you all for taking the time and effort for your responses. I will try out a few of the options, and let ye know if any of them suit 😀
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply