• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1560
  • Last Modified:

Changing SA password on MS SQL 2005

I have lost my SA password on my SQL 2005 server.

I have 10 production databases, and it is very importent that these are not affected, if I choose to change the SA password on the SQL server.

I know I can just change the password in Management Studio, but what is the impact?
Does anybody know?

Alternatively, is there a way where I try out possible SA passwords, to see if a password should work, so I don't need to change the password?
0
sbs_svendborg
Asked:
sbs_svendborg
1 Solution
 
amazingwolf1Commented:
Hi,

A few words about the SA account:

As a minimum, you should always set a password for the SA account even when you select pure windows authentication.  The SA account is the master account for the entire SQL Server instance, in SQL Server 2k it can not be removed,  you can not revoke sysadmin access, and by default it has DBA account for all databases in the SQL Server instance.  The DBA should:

a)      never use it scheduled or regularly executed non-scheduled DTS jobs
b)      alter the password regularly
c)      guard it religiously
d)      ensure backup and recovery documents clearly document its use, how to get the password and associated responsibilities of using the login

What makes the SA account powerful is simply the sysadmin (System Administrator) fixed server role.  Never grant this privilege to any other user, there is simply no reason to in a production environment.

As you can see, it is recomended to change the password from time to time. Assuming none of your applications connect to the DB as SA (they should not do that), you may change the password without a problem.

If you have a test environment, I suggest always checking on that environment first, especially if you are not sure your apps do not use the SA account.

HTH
0
 
Igor-KCommented:
Hi,
MS SQL 2005 has new setting "Enforce password policy".  This makes use of windows password policies.  It is likely the SA account will get locked after number of failed attempts, although I am not sure of it.  You can uncheck this option for the time been and then put it on.
Apart of that, there is no other facility to block login after number of failed attempts.

If you change SA login password, anyone who uses it must use new password, disregarding of the database they use.  If it is feasible, you can track everybody who uses this login and advice them to switch to a different login.  You can track using Profiler for example. Or just analysing current activity, sysprocesses tables.  There are lots of way to do it.
0
 
Chris MConsulting - Technology ServicesCommented:
Hi as a DBA, you may cause a serious loss to the organisation if you changed the SA account in case your applications use it.
As a rule, do not change it as yet but you will only change the SA account password after going through this check list:
1. Use any other account that may have admin rights to check the processes that connect to your instance with the SA account and verify if they are initiated by any of your critical applications. If yes, don't think about changing the password right now. (I will tell you later how to).
2. Work together with your Systems analysts or application support team (and the vendor if need be) to verify if your application (or your applications) does/do not use the SA account while connecting to SQL server.
3. Do a proper system audit, establish how many applications connect to your database server, and establish which accounts they use.
Here's the way to go:
As a DBA, you must manage all your changes. Do not just change the password yet.
For a start, use SQL profiler (Start > Programs> SQL 2005 > Perfromance tools > Profiler), and trace for activities by SA.
To ensure that you minimise "garbage", let's filter only for SA's activity.
Click File > New Trace. Then loging using any other account that has admin rights on your database server (usually OS admins have this by default unless you kick them out).
After logging in, click "Event selection" tab and let's only look at SA by clicking "Column Filters" button, then highlight LoginName and specify like "SA" on to your right.
After filtering, click "Run" to start the trace. You will see SA's activities if the SA account is being used. After getting the data, you can see application names, those are the ones that connect using SA. SQL agen is used for SQL jobs.
If you see nothing for the SA, leave your trace running for some time even days if nothing comes up.
It's likely that no one used the SA account if no activity comes up while your all your applications are in use.
In event that the SA account is being used as an application account then arrange to have a planned downtime according to your service level agreement (and preferrably outside business hours) and have the system down to perform the following list of tasks:
1. create an application account with appropriate privileges (usually insert, update, delete and create/drop table privileges) - do not grant it administrative rights or database owner rights.
2. Let your Systems support/Systems analysts change your application account and switch on your applications back.
3. Document your changes. Change management is very important.
4. Let your system run for a few days and ensure that everything is alright.
5. After a week or so, when no one uses the SA account, backup your master database first after which you can  change the SA account (outside business hours), just in case something goes wrong. Have your systems/application support team around. Do not forget to change your agent from using the SA account, check it just in case it does.
Please note that things go wrong when we least expect them to so DO NOT give chance to unforeseen risks take you down.
Best of luck, let us know how it all goes.
God bless,
Chris Musasizi.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Chris MConsulting - Technology ServicesCommented:
I forgot to say that if you want to try SA password out, simply connect from SQl server management studio (or any other SQL client) and do a SQL login specifying the user as SA and try out a number of passwords.
I hope that you remember the SA password.
Good luck, be a good DBA, keep your changes.
Regards,
Chris.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
As a DBA, you will be maintaining several Databases and its better if you have an Excel sheet with various Usernames documented so that you wont run into similar kind of issues.

If you forgot your password, unless you remember it you have to reset the password and if any application using it needs to be changed accordingly.

Other than that you dont have any options.
0
 
Chris MConsulting - Technology ServicesCommented:
Give the excel sheet (the one with user names & passwords) a funny name that does not lead a hacker to suspect it contains the SA credentials.
And dont forget to keep your excel sheet in a secure place otherwise someone will crack your password to the excel sheet & he will have access to all your databases.
 
All the best.
Regards,
Chris Musasizi
0
 
sbs_svendborgAuthor Commented:
Hi guys

Thanks for your all answars.

I'll ghost my server before changing the password. That way I have a working copy if something goes terribly wrong.
I plan to change the password on the 22nd og May, so I hope everything works out.

I do believe that I have been smart enough, not to use the SA account for anything serious :-)

I will award the points to pastorchris. I fell he delivered to best answar :-)

Regards, Ole

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now