SQL Server: How do you check if it's safe to change the SA password?
772
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?
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.
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.
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?
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.
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.
Change the password and see who screams. They should not have an application logging into the system as SA anyway.
It's safe, in that it's more unsafe to not change it.