Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

If I change the SA password for a Microsoft SQL Server, can I see in a log or a trace if some application somewhere is trying to log in with the old password?

share|improve this question

4 Answers 4

up vote 7 down vote accepted

See this article/tutorial which explains how to enable login auditing in SQL Server 2000 and 2005:

Who is logging in as the sa login in SQL Server?

The native audit level configuration is a simple configuration that can help to determine if the sa login is used in the SQL Server instance. What this configuration does is records all of the successful (or failed or both) logins into a particular SQL Server instance. You will not know the host name, application, etc. However, this configuration will enable you to determine if the sa login is being used in the first place. Once you have this information, then you can dig deeper with SQL Server Profiler outlined below.

For SQL Server 2008 there is a new feature "SQL Server Audit". The following article explains how to autid logins including example scripts: SQL Server Audit in SQL Server 2008.

share|improve this answer

I would tend to agree with the change it and see who complains, BUT only after some due diligence. You have to spend the time to profile and look for someone logging in with SA. If you can't find anything logging in over a week, then you ought to be safe. If it's used less often, it's even more of a reason to make the change.

No one ever wants to change this password, and it ends up creating issues. The SA password gets out of control and when it is disclosed to the wrong person, it creates havoc. Often with data loss, someone losing data with a lack of backups, new database, DELETE with no WHERE, etc.

Disclose you are making the change, run Profiler, and then do it. Your customers will appreciate it.

share|improve this answer

Change the password and see who screams. They should not have an application logging into the system as SA anyway.

share|improve this answer
    
Not a good answer - what if the person who screams is a potential customer on your website? –  Richard Gadsden May 22 '09 at 9:20
1  
Then I would take my business to someone who ran their hosting service well enough that untrusted third parties did not have admin access to machines I was hosting my applications on. –  ConcernedOfTunbridgeWells May 22 '09 at 10:16
    
I totally agree with Concerned that use of the sa login is bad practice, but a common scenario is when someone else set up the server some time ago, and you don't know the sa password. The question specifcally asks for log or trace evidence, rather than something audible ;-) –  nray May 22 '09 at 12:13

It's safe, in that it's more unsafe to not change it.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.