Auditing Your SQL Server Environment Part I

Over the last few years I have been a roving SQL Server DBA contractor and tended to work contacts in small
and mid-size companies that involved organizing, documenting and then tuning/optimizing existing SQL Servers
in those companies. I have noticed one very disturbing truth over the years; no one seems to document anything.
In fact, I was usually thankful if I could find something or someone who knew the sa passwords on each SQL Server
installation, let alone knew anything else about their setups. I have often been asked how I could go into a
company that had over 50 SQL Server databases up and running on a dozen or more servers, that had no existing
documentation, and that had no full-time DBA on staff or had lost their DBA several months ago and a developer or
manager was filling in, and ramp up to a level that I could function in a very short time frame.  My answer was
practice and my file of stored procedures written over the years that I carried with me which allowed me to do
quick audits of the SQL Server installations and databases so I could quickly produce documentation to work from.
This article is the first in a series that I intend to post on my website to share those stored procedures and methods
I have learned with you to help you produce and learn a new environment if you move on or obtain another project at your
existing company.
Determine If You Have a Problem
Many companies who use SQL Server with custom applications do so with the application connecting to SQL Server
with SQL Server Authentication. I know that Microsoft would love for everyone to use Windows Authentication or
at least use an application role, but that just isn't the norm at this time. There is a big problem with SQL Server
Authentication and that is with password handling. There is nothing that prevents a login from having no password,
I was so thankful that with SQL Server 2000 you have to force the installation process to install SQL Server with a
blank password for the sa login instead of the default being a blank password, nor is there anything that
mandates a minimum length on passwords, or keeps users from using their login name as their password. It is with
this in mind I would like to present a short stored procedure that will go into the system table sysxlogins and
check user's passwords for those three shortcomings: no password present, the password being the same as the login name
and the password being only one character long.
Create this stored procedure in the master database on each of the server's you administer and execute it
to perform a simple audit of the existing logins to determine if you have any logins with weak passwords.
IF OBJECT_ID('dbo.spAuditPasswords') IS NOT NULL
DROP PROCEDURE dbo.spAuditPasswords
GO
CREATE PROCEDURE dbo.spAuditPasswords
AS
/**********************************************************************
   Creation Date: 03/22/02    			Created By: Randy Dyess
                              				Web Site: www.TransactSQL.Com
                             		 		Email: RandyDyess@TransactSQL.Com
   Purpose: Perform a simple audit of user's passwords
   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
--Variables
DECLARE @lngCounter INTEGER
DECLARE @lngCounter1 INTEGER
DECLARE @lngLogCount INTEGER
DECLARE @strName VARCHAR(256)
--Create table to hold SQL logins
CREATE TABLE #tLogins
(
numID INTEGER IDENTITY(1,1)
,strLogin SYSNAME NULL
,lngPass INTEGER NULL
)
--Insert non ntuser into temp table
INSERT INTO #tLogins (strLogin)
SELECT name FROM master.dbo.syslogins WHERE isntname = 0
SET @lngLogCount = @@ROWCOUNT
--Determine if password is null and user iis SQL Login
PRINT 'The following logins have blank passwords'
SELECT name AS 'Login Name' FROM master.dbo.syslogins
WHERE password IS NULL
AND isntname = 0

--Determine if password and name are the ssame
SET @lngCounter = @lngLogCount
WHILE @lngCounter <> 0
BEGIN
	SET @strName = (SELECT strLogin FROM #tLogins WHERE numID = @lngCounter)
	UPDATE #tLogins
	SET lngPass = (SELECT PWDCOMPARE (@strName,(SELECT password FROM master.dbo.sysxlogins
	WHERE name = @strName)))
	WHERE numID = @lngCounter
	SET @lngCounter = @lngCounter - 1
END
PRINT 'The following logins have passwords the same as their login name'
SELECT strLogin AS 'Login Name' FROM #tLogins WHERE lngPass = 1
--Reset column for next password test
UPDATE #tLogins
SET lngPass = 0
--Determine if password is only one characcter long
SET @lngCounter = @lngLogCount
WHILE @lngCounter <> 0
BEGIN
	SET @lngCounter1 = 1
	SET @strName = (SELECT strLogin FROM #tLogins WHERE numID = @lngCounter)
	WHILE @lngCounter1 < 256
	BEGIN
		UPDATE #tLogins
		SET lngPass = (SELECT PWDCOMPARE (CHAR(@lngCounter1),(SELECT password FROM master.dbo.sysxlogins
		WHERE name = @strName)))
		WHERE numID = @lngCounter
		AND lngPass <> 1
		SET @lngCounter1 = @lngCounter1 + 1
	END
	SET @lngCounter = @lngCounter - 1
END
PRINT 'The following logins have one character passwords'
SELECT strLogin AS 'Login Name' FROM #tLogins WHERE lngPass = 1
GO
--Test
EXEC dbo.spAuditPasswords
GO
Steps To Remedy Weak Passwords
Now that you have created and executed the stored procedure and have determine 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 had 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 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.
Keep in mind that DTS packages will sometimes be saved with a login and password and determining which ones will
be affected by a changed password is a manual process. You may have to open each DTS package and save them under
a new login created for the packages. If you miss one you will have to reset the login password the package is
saved under back to its original password just long enough for you to resave the DTS package under a new login.
As you open each package, be sure to check the connection objects to find out what login it uses to connect to
SQL Server. Also keep in mind that SQL Servers that are currently running replication will usually be affected
by a change in the sa password. You need to be ready to rebuild that replication if you change the sa password.
You can script out the replication, which is a good idea anyway, and rerun the script after you have changed the
password.
Summary
There are quite a few companies out there that have very weak login security on their SQL Servers and determining
these weaknesses should be one of your primary concerns when you enter a new environment. This short article has
explained several quick ways to audit for those weak logins and start you on the path to understanding your new
SQL Server environment.
Next month I will submit an article that will help you to determine which logins belong to which server or database
role to prepare you for documenting exactly what each login's permission set is.

Knowledge Based Articles
Q189126 Microsoft's Policy Regarding Missing or Invalid Passwords
Q298758 PRB: Using the Auto_Fix Option with sp_change_users_login Can Leave Security Vulnerabilities
Q168001 PRB: User Logon and/or Permission Errors After Restoring Dump
Q259710 PRB: SQL Server Agent Fails to Start on Windows 9x When You Change the sa Password
Q274773 FIX: If You Change Windows Security to Windows/SQL Security the SA Password is Blank

Copyright 2002 by Randy Dyess, All rights Reserved
You can also see Randy in his personal page at www.TransactSQL.Com