Frequently problematical - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Frequently problematical

Author

Topic

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-02-21 : 13:58:51

I want to do a password analysis for a password security audit. I have a load of passwords in a table and have three problems

1) I would like to perform a frequncy analysis on the characters in the passwords, the frequency being grouped according to whether they are alphabetic, numeric or other characters

e.g.
if my passwords are

elw00s(
steve1

then I get

Alphabetic 9
Numeric 3
Other 1

2) I would also like to indicate how many (and ideally which) of the three groups that any given password covers so with the same passwords as above I would get

PASSWORD ALPHABETICS NUMERICS OTHERS
elw00s( Yes Yes Yes
steve1 Yes Yes No

(I suspect that solving this would allow a solution to the first problem)

3) Finally I would like to do a dictionary check but I have no idea where I can get a dictionary from. For example neither of the passwords above would be in a dictionary but Flower might be.

If anyone has any suggestions for these I would be grateful

Finally if anyone has any suggestions on other checks I can use to indicate easy to crack passwords I would also be grateful

Many thanks

steve

And how is education supposed to make me feel smarter? Besides, every time I learn something new, it pushes some old stuff out of my brain.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-21 : 14:52:49

This is somethig for a tally table and ascci() function :)

This one checks for weak passwords:
Microsoft Baseline Security Analyzer

rockmoose

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-21 : 15:01:18

Its not that bad...


Declare @numbers table (n int)
Insert Into @numbers
Select
	n = n1+n2+n3+n4+n5+n6+n7
From
	(Select n1=0 Union All Select 1) n1,
	(Select n2=0 Union All Select 2) n2,
	(Select n3=0 Union All Select 4) n3,
	(Select n4=0 Union All Select 8) n4,
	(Select n5=0 Union All Select 16) n5,
	(Select n6=0 Union All Select 32) n6,
	(Select n7=0 Union All Select 64) n7
Order By n


Declare @myTable table (pwd varchar(100))
Insert Into @myTable
Select 'elw00s(' Union All Select 'steve1'

Select 
	pwd,
	Alphabetics = case when pwd like '%[a-zA-Z]%' then 1 else 0 end,
	Numerics = case when pwd like '%[0-9]%' then 1 else 0 end,
	Other = case when pwd like '%[^a-zA-Z0-9]%' then 1 else 0 end
From @myTable


Select 
	pwd,  
	Alphabetics = sum(Alphabetics),
	Numerics = sum(Numerics),
	Other = sum(Other)
From 
	(
	Select 
		pwd, 
		Alphabetics = case when substring(pwd,n,1) like '[a-zA-Z]' then 1 else 0 end,
		Numerics = case when substring(pwd,n,1) like '[0-9]' then 1 else 0 end,
		Other = case when substring(pwd,n,1) like '[^a-zA-Z0-9]' then 1 else 0 end
	From @myTable A, @numbers B
	Where B.n <= len(a.pwd)
	) Z
Group By pwd

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-21 : 15:17:29

I hereby retract the use of the ascii() function,
which is completely superfluous for solving the problem at hand.
Nice Corey, as usual.

rockmoose

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-21 : 15:54:16

I love puzzles

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-02-22 : 03:27:06

Corey you're a genius

Many thanks

And how is education supposed to make me feel smarter? Besides, every time I learn something new, it pushes some old stuff out of my brain.

Kristen
Test

22859 Posts

Posted - 2005-02-23 : 00:09:05

I have a 260K 29,000 word dictionary file here that came from some public domain source years ago. Its got some garbage entries in it (you could stick it in Word and spell check it!), and I've no idea how comprehensive it is, but I can email it to you if you like.

Kristen

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-02-23 : 03:57:16

That would be brilliant, thanks Kristen

When I've done the audit I may post the relevant points here

steve

And how is education supposed to make me feel smarter? Besides, every time I learn something new, it pushes some old stuff out of my brain.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources