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.syslogins
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 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.
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
Article originally appeared at TransactSQL.com Copyright 2002 by Randy Dyess, All rights Reserved
Make sure you purchase your copy of Transact-SQL Language Reference Guide from my website www.TransactSQL.Com today to learn more about Transact-SQL by reviewing the more than 1200 examples contained within the book.
