April 12, 2007 at 7:58 am
Hi
we have more than 200 servers in the environment. And as a part of security audit we have to change all the 'sa' password on all servers. So is it possible to do it in one go at a time on all servers.
Thanks
April 12, 2007 at 8:28 am
Yes. It is possible.
You can use sp_addlinkedserver to link each server, then modify password on each server.
April 12, 2007 at 9:00 am
http://qa.sqlservercentral.com/columnists/sjones/datamigrationquicklyinsertingnewdata.asp
Get a list of servers into Excel, setup a "isql -S" in one cell, "sp_password null, new, sa" in the other and combine those with the server name into a 3rd.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 12, 2007 at 11:01 am
Does it mean you will have the same SA password on all boxes? This will be a bigger security concern than older passwords. Moreover, more people will need to know this password.
Also, on different boxes developers, DBAs and others may need to know the SA password and may use it for something. In some cases applications require SA login. I would not change ANYTHING on the system without first notifying the application support and superusers.
Regards,Yelena Varsha
April 12, 2007 at 1:16 pm
Thanks you all for quick help. Yelena we are going to send notification for the individual NTlogins having different type of access on server. Thanks for pointing that applying same password for all boxes is risky. definitely will take care of that.
April 12, 2007 at 3:09 pm
I used to keep the same sa password on every production server. Different passwords for dev and QA. I'm not sure it's a bad security risk, especially given the hassles that separate passwords require for a DBA group. Very, very few people should have sa, so I'm not sure it's a problem.
To mitigate the risk, I changed pwds every month.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 13, 2007 at 2:48 am
In my company each SQL instance has it's unique sa password which only I know (but is logged in a locked safe). However each designated DBA has their windows login access as a sys admin to all required servers and no-one uses sa. I have created temporary sys admin logins for engineers to avoid using sa to install products and will always try to avoid any use of sa.
April 13, 2007 at 9:00 am
The company I work for, SQL Farms, has a tool with which you can push scripts to all servers in parallel, get results, etc. You can push the password (i.e., run sp_password) change to all your servers in one-click without setting up linked servers or such.
April 13, 2007 at 9:24 am
We're luckly...because, quite simply, we put very strong pwds on our sa accounts and lock them down, i.e. don't use them (also, only the DBAs have access to the pwds). Anyone wants access, they have accounts with the permission only that they need and that we can track, be it a Windows or SQL account. This includes 3rd Party Apps, which one of our requirements is 'no using sa account'
HTH
April 13, 2007 at 9:52 am
Chuck What tool your company is using to push script to set password once at a time. The whole idea behind this is saving the time in transferring a list of servers into Excel and carrying the process further as Steve said.
April 13, 2007 at 12:20 pm
How about those embeded sa passwords within SQL server? For example, DTS package when saved using "sa". If the sa password is not updated, the scheduled job for the DTS will fail because of login failure.
I am looking for a "one click" tool to change sa password for all the servers and be able to update sa password wherever used within each SQL server as well.
Chuck, is the SQL farm tool can handle this?
I have been looking for this for a while.
April 16, 2007 at 11:32 am
Kids,
Vivien brings up a good point!!!
In SQL Server 2000 we had cases when the package was created when somebody was connected as SA, the owner was a domain login, all connections inside the package were using some other logins and when SA password was changed the package stopped running. I was able to reproduce and post this bug 3 years ago.
As a coincedence, when I was looking up this problem in my records today I realized it happened exactly 3 years ago on April 15.
Regards,Yelena Varsha
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply