scary? - changing sa password on production server

Question

scary? - changing sa password on production server

sqltung

Hall of Fame

Points: 3962
More actions
October 6, 2005 at 2:53 pm

#115296

I've got a production server that's somewhat busy. The sa password needs changing.. we know the current password, but it could be more secure.
How is this done? Some further info:
- Looks like sql server and agent services are using the local windows account..
- I didn't see any applications using the sa login in profiler
Is a reboot required? I'll need to change the Enterprise Manager server registration info as it's using the sa acct, right?
Anything else I should know?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Carl Federl One Orange Chip Points: 25384 More actions · Answer 1

No reboot or SQL Server/Agent restart is required.

I am certain because on each of over 100 SQL Servers, a job runs on Wednesdays at 3PM that changes the SA password to a newly generated GUID.

Welcome to Sarabannes-Oxley.

SQL = Scarcely Qualifies as a Language

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 2

You should verify the SQL Agent Properties on the Connection tab, to insure that this is not set for sa, otherwise you will have to change the password here as well and probably restart SQL Agent service.

Andy

Alex Wilson-148544 SSC-Addicted Points: 450 More actions · Answer 3

Hi Carl,

I'm intrigued with your solution that resets the sa password on wednesday.

Would you mind telling me how you report the newly changed password to the DBA's, is an email fired to say "this is the new password", or does it automatically populate a password management tool?

thanks Carl,

-- alex wilson

sqltung Hall of Fame Points: 3962 More actions · Answer 4

Worked just fine. I did have the orphaned dbo problem, but was able to change db owner for these.

Thanks!

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 5

Greg,

They usually call you in a couple of days complaining that SA password does not work for them anymore... Nice way to find out who actually is using it. Also they will call that their reports did not run, their data sources did not work... It is mostly those who used to work with SA account before you ( a good DBA) took over this server

Also depends on Software: Clarify CRM for example at least till version 11.5 has sp_adduser in their code to add application users. sp_adduser does not use roles: there is a DBO hardcoded in it who is in many cases SA. (Yah, we know we have to use sp_grantdbaccess but not vendor's sw). So unless you modify a system SP (which I personally did) SA account will be used by superusers.

Yelena

Regards,Yelena Varsha

Cricketdogger SSC Veteran Points: 259 More actions · Answer 6

If there are any PeopleSoft databases on your server - beware - the "sa" password is encrypted in each one.

Carl Federl One Orange Chip Points: 25384 More actions · Answer 7

To comply with Sarabannes-Oxley, no one, including the DBAs, are allowed to use generic accounts such as "sa". Instead, each DBA connects using an separate account. To prevent anyone from using the "sa" account, the password unknown and the password is not recorded.

SQL = Scarcely Qualifies as a Language