Changing SA password on MS SQL 2005

Posted on 2009-04-20
Last Modified: 2012-06-27
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?
Question by:sbs_svendborg
    LVL 4

    Expert Comment


    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.

    LVL 4

    Expert Comment

    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.
    LVL 12

    Expert Comment

    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.
    LVL 12

    Expert Comment

    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.
    LVL 57

    Expert Comment

    by:Raja Jegan R
    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.
    LVL 12

    Accepted Solution

    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.
    Chris Musasizi

    Author Comment

    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


    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Suggested Solutions

    When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now