|
|
SQL Server T-SQL User-Defined Function of the Week
Checking for Strong Passwords in T-SQL
Volume 1 Number 41 August, 26, 2003
Check out the UDF Frequently Asked Questions at:
http://www.novicksoftware.com/UdfOfWeek/UDF_FAQ.htm
To ask a UDF related question or to contribute a UDF,
send e-mail to udf@NovickSoftware.com
Sign up for this newsletter at:
http://www.novicksoftware.com/UdfOfWeek/UDFofWeekSignup.htm
Like I said last week, I've been working on security the last
couple of weeks. One of the SQL Server Best Practices is to use
only Windows Authentication for your SQL server. But it isn't
always possible.
Some of the common reasons that require the use of mixed
authentication are:
- Non-Windows clients
- Windows-98/ME users who don't sign in
- 3rd part applications that require mixed mode.
So if you're stuck with mixed mode, make the best of it. A
Best Practice for mixed mode is to require strong passwords.
This week's UDF tests a potential password against a small
set of rules. Here are the rules that I use:
- Minimum Length of 12 characters
- Contains at least one character from each of these groups:
alpha, numeric, and special characters.
The idea behind these rules is that they make dictionary
attacks against the password difficult. You might have other
rules that you'd like to apply, so you're welcome to modify
the UDF to suite your own set of rules.
Let's take a look at the UDF, try it out and then discuss how
it's written. You'll need udf_Txt_HasCharInGroup from Issue #40.
Here's the CREATE FUNCTION script:
/------- Start copying below this line -------------------------\
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.udf_SQL_PasswordIsStrong (
@PWD nvarchar(128) -- The password to test for strength
) RETURNS BIT -- 1 if the password is string, otherwise 0
WITH SCHEMABINDING -- Or Comment about why not
/*
* Determines if a password is a strong password.
* The rules are:
* - Must be 12 or more characters
* - Must include at least one char from each group:
* o Alpha
* o Numeric
* o Special !#%*|_$^&@[]{}()-+=\/.,<>?
*
* Example:
select dbo.udf_SQL_PasswordIsStrong('bm10@t0mb10ts@')
* Test:
PRINT 'Test 1 ' + CASE WHEN 1 =
dbo.udf_SQL_PasswordIsStrong ('bm10@t0mb10ts@')
THEN 'Worked' ELSE 'ERROR' END
PRINT 'Test 2 ' + CASE WHEN 0 =
dbo.udf_SQL_PasswordIsStrong ('bmloto@mblots@')
THEN 'Worked' ELSE 'ERROR' END
* History:
* When Who Description
* ------------- ------- -----------------------------------------
* 2003-08-10 ASN Initial Coding
*
* © Copyright 2003 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain
* other unrelated database objects. You may not publish this
* UDF either in print or electronically.
* Published as T-SQL UDF of Week Newsletter Vol 1 #41 8/26/03
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/
AS BEGIN
IF LEN(@PWD) < 12 RETURN 0 -- LEN must be >= 12
IF @PWD NOT LIKE '%[0-9]%' RETURN 0 -- Must have a number
IF @PWD NOT LIKE '%[A-Z]%' AND @PWD NOT LIKE '%[a-z]%'
RETURN 0 -- Must have an alpha
IF dbo.udf_Txt_HasCharInGroup (@PWD
, '!#%*|_$^&@[]{}()-+=\/.,<>?')
= 0 RETURN 0 -- Must have a special char
Return 1
END
GO
GRANT EXEC, REFERENCES ON [dbo].[udf_SQL_PasswordIsStrong]
TO [PUBLIC]
GO
\-------Stop copying above this line ---------------------------/
Here's a query that tries out the new UDF:
/------- Start copying below this line -------------------------\
SELECT dbo.udf_SQL_PasswordIsStrong ('mypwd123abc#')
as [mypwd123abc#]
, dbo.udf_SQL_PasswordIsStrong ('mypwd123abcd')
as [mypwd123abcd]
, dbo.udf_SQL_PasswordIsStrong ('mypwd^##abcd')
as [mypwd^##abcd]
, dbo.udf_SQL_PasswordIsStrong ('^6t')
as [^6t]
go
\-------Stop copying above this line ---------------------------/
(Results)
mypwd123abc# mypwd123abcd mypwd^##abcd ^6t
------------ ------------ ------------ ----
1 0 0 0
The rules that I used are similar to may others that I've seen
but you'll run across many variations. In particular, the
treatment of special characters. I excluded apostrophe and
quotations because these characters are often stripped from
web input in an effort to prevent SQL Injection attacks. Other
writers allow these and other punctuation marks and even
encourage the use of "Alt" characters. Those are the characters
that can only be typed by holding the all key and then typing
numbers, usually on the numeric key pad. After a struggle
with my laptop trying to use the numeric keypad, I decided
against using the Alt characters.
Another issue comes up if you're outside the English speaking
world. There are other characters, such as accented
characters, that you might want to allow. They're not in a
consecutive range with the other alphabetic characters so you'll
have to switch the test from
@PWD LIKE '%[A-Z]%'
to
1=dbo.udf_Txt_HasCharInGroup (@PWD, 'Chars I allow')
You may even want to switch to a UNICODE based function by
changing the data type of the parameters and internal variables
from varchar to nvarchar. In this case, I would change the
name to udf_TxtN_HasCharInGroup.
Once udf_SQL_PasswordIsStrong is available, how is it used?
I'm planning on using it in a stored procedure that sets the
sa password. I've seen some consultants take this further and
modify the sp_addlogin and sp_password system stored procedures
to check that new passwords satisfy the rules. I'm not ready to
go that far. I try and use Windows Authentication as much as
possible and put the burden on Windows to enforce password rules.
Do you have a great UDF that you'd like to share? Or maybe you
have a T-SQL problem that you think could be solved by a UDF
but you don't know how? Send them to:
UDF@NovickSoftware.com
and they might be published in this newsletter. I try and
respond to every request that I get.
Thanks,
Andrew Novick
+--------------------------------------------------------------+
|
|
