Steps To Remedy Weak Passwords
Now that you have created and executed the stored procedure, and have determined that you have a problem, the hard work begins. You must perform an audit on each of the logins with weak passwords to either move everything off that login, or to document what processes must be changed when you create a new password for that login.
Just going ahead and changing the password, even the sa password, without performing a login audit can be a real nightmare with you taking the chance of breaking numerous applications, DTS packages, SQL Server replication, and DSN connections. The second thing you must do is to put into place a policy that from today on, you control the logins and passwords, this doesn’t mean you can change passwords at your whim, it means that you should create any new logins, create strong passwords on them, and control what permissions the logins have.
Just to make a point here, you should never let anyone develop anything using the sa login. The sa login needs to be free so that when anyone who knows the login (this should be very few people) changes jobs or leaves the company, the login’s password is changed immediately without having to have applications and other objects changed as well. I usually made a habit of changing the sa login’s password on a monthly basis on my production servers to make sure nothing has been created with them, and if someone ‘found’ out the password, they would only know if for a month. And the third thing you must do is to document everything you learned so the next DBA taking over your position, or a part of your responsibilities, won’t have to spend that first month creating their own documentation.
How To Perform an Audit on a Existing Login
There are three simple ways to perform an audit on an existing logins. The first is to talk to your developers, find out which logins they are using and which applications use which logins. Also, while you are talking to your developers, find out how much work will be involved in changing the logins or passwords to something else if the need arises.
After you have talked to your developers and have documented their answers, you will still need to use one of the two remaining methods to determine if anything has been left out. One of these methods is setting up a Profiler trace using the Security Audit event class, with the Audit Login event. You should also make sure the hostname data column is added to the list of columns on the Data Columns Tab to help you track the source of the application.
The other method is to periodically run and trap the output of either sp_who or sp_who2 system stored procedure. You can trap sp_who2 by creating the below stored procedure and creating a job that runs the stored procedure on a regular basis. This basis should be done at least every 15 minutes to trap short-running processes.
IF OBJECT_ID(‘dbo.spTrapWho’) IS NOT NULL
DROP PROCEDURE dbo.spTrapWho
GO
CREATE PROCEDURE dbo.spTrapWho
AS
/***************************************************************
Creation Date: 03/22/02 Created By: Randy Dyess
Web Site: www.TransactSQL.Com
Email: RandyDyess@TransactSQL.Com
Purpose: Trap the output of sp_who2
Location: master database
Output Parameters: None
Return Status: None
Called By: None
Calls: None
Data Modifications: None
Updates: None
Date Author Purpose
———- ————————– —————————-
***************************************************************/
SET NOCOUNT ON
IF OBJECT_ID(‘dbo.tSPWho’) IS NULL
BEGIN
CREATE TABLE tSPWho
(
spid INTEGER NULL
,status VARCHAR(100) NULL
,login SYSNAME NULL
,hostname SYSNAME NULL
,blkby VARCHAR(10) NULL
,dbname SYSNAME NULL
,command VARCHAR(100) NULL
,cputime INTEGER NULL
,diskio INTEGER NULL
,lastbatch VARCHAR(50) NULL
,programname SYSNAME NULL
,spid2 INTEGER NULL
)
INSERT INTO tSPWho
EXEC dbo.sp_Who2
END
ELSE
BEGIN
INSERT INTO tSPWho
EXEC dbo.sp_Who2
END
GO
Once you have run and analyzed your trace or the output from one of the system stored procedures, you can narrow which logins are running from which hosts, and maybe even tell what applications are using what logins. Using the output from this analysis will help you in going back to re-interview your developers to determine the amount of work required in changing the newly discovered logins in their code.