Password Help
-
Password HelpYesterday I wrote about compliance tips and one of the things I mentioned was using encryption and passwords for your backups. It's a good idea that you should use even with disk backups. You never know when someone might get socially-engineered and hand over one of your .bak files to the wrong folks.
But using encryption isn't enough; you need strong key management that changes the passwords on a regular basis to prevent data losses from the passwords getting released over time. I've worked in a dozen companies and there are many cases where a password has been used for some shared function, some application, some particular feature, whatever. If this password has remained the same for over a year, it's been almost common knowledge throughout the company what the password is. I've seen business people, executives, even secretaries that knew the password to the point where it was useless.
Shared passwords will be a fact of life in many cases. Administrative teams grow, help desk people may need access, consultants help out and learn them, and others overhear them. It's going to happen, so you need to mitigate this risk.
I used to manage a nightclub and we had similar risks with the alcohol. Since turnover was a fact of life, we had a simple policy. When a manager left, for any reason, all alarm codes were changed. On the third occurrence of a manager leaving, we had all locks re-keyed. It was a simple policy to reduce risk.
The same thing needs to happen with your backups. Use one or even a few passwords to protect the various database backups. But then change those passwords every month or two and escrow or archive the old ones. You may not be able to prevent losses of the backup files, but by ensuring they are protected and the passwords changed regularly, you may just protect the data.
And your job.
Steve Jones
-
Here is a nice bit of T-SQL code that will generate a 128 byte strong (some will say 'impossible') password that works for an SA login. It will work in Query Analyzer and SSMS but not in Enterprise Manager. With SQL 7 and later, there is generally no reason (unless a third-party application requires it ;-() that people should be using the default SA login. By giving it such a hard password and stowing it away in an appropriate place you take away the urge to use it.
set nocount on
declare @counter int,
@password varchar(128),
@char char(1),
@charindex int,
@loop int
/* Unallowed characters:
! = 33
( = 40
) = 41
, = 40
* = 42
; = 59
? = 63
@ = 64
[ = 91
] = 93
{ = 123
} = 125
*/
select @counter = 1, @password = ''
while @counter < 2
begin
--Restrict the password to 0-9, A-Z, and a-z
select @loop = 1
while @loop = 1
begin
select @charindex = convert(int, rand() * 254)
if (@charindex between 65 and 90 or @charindex between 97 and 122)
and @charindex not in (33,40,41,42,59,63,64,91,93,123,125,130,132,133,134,135,136,137,139,161,162,166,167,168,169,171,172,173,174,175,176,177,180,182,184,187,188,189,190,191,215,247)
--or @charindex between 161 and 255 or @charindex between 130 AND 140
select @loop = 0
end
--Accumulate characters for password string
select @char = char(@charindex)
select @password = @password + @char
select @counter = @counter + 1
end
while @counter < 4
begin
--Restrict the password to 0-9, A-Z, and a-z
select @loop = 1
while @loop = 1
begin
select @charindex = convert(int, rand() * 254)
if (@charindex between 48 and 57 or @charindex between 65 and 90 or @charindex between 97 and 122)
and @charindex not in (33,40,41,42,59,63,64,91,93,123,125,130,132,133,134,135,136,137,139,161,162,166,167,168,169,171,172,173,174,175,176,177,180,182,184,187,188,189,190,191,215,247)
--or @charindex between 161 and 255 or @charindex between 130 AND 140
select @loop = 0
end
--Accumulate characters for password string
select @char = char(@charindex)
select @password = @password + @char
select @counter = @counter + 1
end
while @counter < 5
begin
--Restrict the password to 0-9
select @loop = 1
while @loop = 1
begin
select @charindex = convert(int, rand() * 254)
if @charindex between 48 and 57 --or @charindex between 65 and 90 or @charindex between 97 and 122
and @charindex not in (33,40,41,42,59,63,64,91,93,123,125,130,132,133,134,135,136,137,139,161,162,166,167,168,169,171,172,173,174,175,176,177,180,182,184,187,188,189,190,191,215,247)
--or @charindex between 161 and 255 or @charindex between 130 AND 140
select @loop = 0
end
--Accumulate characters for password string
select @char = char(@charindex)
select @password = @password + @char
select @counter = @counter + 1
end
while @counter < 10
begin
-- Restrict the password to NOT 0-9, A-Z, and a-z
select @loop = 1
while @loop = 1
begin
select @charindex = convert(int, rand() * 254)
if --@charindex between 48 and 57 or @charindex between 65 and 90 or @charindex between 97 and 122
--or
(@charindex between 161 and 255 or @charindex between 130 AND 140)
and @charindex not in (33,40,41,42,59,63,64,91,93,123,125,130,132,133,134,135,136,137,139,161,162,166,167,168,169,171,172,173,174,175,176,177,180,182,184,187,188,189,190,191,215,247)
select @loop = 0
end
--Accumulate characters for password string
select @char = char(@charindex)
select @password = @password + @char
select @counter = @counter + 1
end
while @counter < 11
begin
--Restrict the password to 0-9
select @loop = 1
while @loop = 1
begin
select @charindex = convert(int, rand() * 254)
if @charindex between 48 and 57 --or @charindex between 65 and 90 or @charindex between 97 and 122
and @charindex not in (33,40,41,42,59,63,64,91,93,123,125,130,132,133,134,135,136,137,139,161,162,166,167,168,169,171,172,173,174,175,176,177,180,182,184,187,188,189,190,191,215,247)
--or @charindex between 161 and 255 or @charindex between 130 AND 140
select @loop = 0
end
--Accumulate characters for password string
select @char = char(@charindex)
select @password = @password + @char
select @counter = @counter + 1
end
-- while @counter < 64 -- use this for app role passwords
while @counter < 129 -- use this for regular passwords
begin
--Restrict the password to 0-9, A-Z, and a-z
select @loop = 1
while @loop = 1
begin
select @charindex = convert(int, rand() * 254)
if (@charindex between 48 and 57 or @charindex between 65 and 90 or @charindex between 97 and 122 or @charindex between 161 and 255 or @charindex between 130 AND 140)
and @charindex not in (33,40,41,42,59,63,64,91,93,123,125,130,132,133,134,135,136,137,139,161,162,166,167,168,169,171,172,173,174,175,176,177,180,182,184,187,188,189,190,191,215,247)
select @loop = 0
end
--Accumulate characters for password string
select @char = char(@charindex)
select @password = @password + @char
select @counter = @counter + 1
end
select RTRIM(@password) AS Password
-
Here is a similar bit of T-SQL code that generates 15 character passwords for service accounts. There is a numeric keypad character in each of the first seven and second seven characters. You will not find a canned password cracker that will ever attempt to crack this (it would take years and years). This is good to use for Service Accounts. The 15 characters is one more than the 14 character old LANMAN limit that you found in NT4 domains before Active Directory took hold.
set nocount on
declare @counter int,
@password varchar(128),
@char char(1),
@charindex int,
@loop int
select @password = ''
select @counter = 1
while @counter < 3
begin
--Restrict the password to 0-9, A-Z, and a-z
select @loop = 1
while @loop = 1
begin
select @charindex = convert(int, rand() * 254)
if @charindex between 65 and 90 or @charindex between 97 and 122
--or @charindex between 161 and 255 or @charindex between 130 AND 140
select @loop = 0
end
--Accumulate characters for password string
select @char = char(@charindex)
select @password = @password + @char
select @counter = @counter + 1
end
while @counter < 4
begin
--Restrict the password to 0-9, A-Z, and a-z
select @loop = 1
while @loop = 1
begin
select @charindex = convert(int, rand() * 254)
if @charindex between 58 and 64 or @charindex between 33 and 46 --or @charindex between 97 and 122
--or @charindex between 161 and 255 or @charindex between 130 AND 140
select @loop = 0
end
--Accumulate characters for password string
select @char = char(@charindex)
select @password = @password + @char
select @counter = @counter + 1
end
while @counter < 5
begin
--Restrict the password to 0-9
select @loop = 1
while @loop = 1
begin
select @charindex = convert(int, rand() * 254)
if @charindex between 48 and 57 --or @charindex between 65 and 90 or @charindex between 97 and 122
--or @charindex between 161 and 255 or @charindex between 130 AND 140
select @loop = 0
end
--Accumulate characters for password string
select @char = char(@charindex)
select @password = @password + @char
select @counter = @counter + 1
end
while @counter < 6
begin
--Restrict the password to 0-9
select @loop = 1
while @loop = 1
begin
select @charindex = convert(int, rand() * 254)
if @charindex = 135
or @charindex = 149
or @charindex between 196 AND 199
or @charindex = 201
or @charindex = 209
or @charindex = 214
or @charindex between 223 AND 226
or @charindex between 228 AND 239
or @charindex between 241 AND 244
or @charindex between 246 AND 247
or @charindex between 249 AND 252
or @charindex = 255
select @loop = 0
end
--Accumulate characters for password string
select @char = char(@charindex)
select @password = @password + @char
select @counter = @counter + 1
end
while @counter < 10
begin
-- Restrict the password to NOT 0-9, A-Z, and a-z
select @loop = 1
while @loop = 1
begin
select @charindex = convert(int, rand() * 254)
if --@charindex between 48 and 57 or @charindex between 65 and 90 or @charindex between 97 and 122
--or
@charindex between 48 and 57 or @charindex between 65 and 90 or @charindex between 97 and 122
--@charindex between 161 and 255 or @charindex between 130 AND 140
select @loop = 0
end
--Accumulate characters for password string
select @char = char(@charindex)
select @password = @password + @char
select @counter = @counter + 1
end
while @counter < 11
begin
--Restrict the password to 0-9
select @loop = 1
while @loop = 1
begin
select @charindex = convert(int, rand() * 254)
if @charindex between 58 and 64 or @charindex between 33 and 46 --or @charindex between 65 and 90 or @charindex between 97 and 122
--or @charindex between 161 and 255 or @charindex between 130 AND 140
select @loop = 0
end
--Accumulate characters for password string
select @char = char(@charindex)
select @password = @password + @char
select @counter = @counter + 1
end
while @counter < 12
begin
--Restrict the password to 0-9
select @loop = 1
while @loop = 1
begin
select @charindex = convert(int, rand() * 254)
if @charindex = 135
or @charindex = 149
or @charindex between 196 AND 199
or @charindex = 201
or @charindex = 209
or @charindex = 214
or @charindex between 223 AND 226
or @charindex between 228 AND 239
or @charindex between 241 AND 244
or @charindex between 246 AND 247
or @charindex between 249 AND 252
or @charindex = 255
select @loop = 0
end
--Accumulate characters for password string
select @char = char(@charindex)
select @password = @password + @char
select @counter = @counter + 1
end
while @counter < 16
begin
--Restrict the password to 0-9, A-Z, and a-z
select @loop = 1
while @loop = 1
begin
select @charindex = convert(int, rand() * 254)
if @charindex between 48 and 57 or @charindex between 65 and 90 or @charindex between 97 and 122
--or @charindex between 161 and 255 or @charindex between 130 AND 140
select @loop = 0
end
--Accumulate characters for password string
select @char = char(@charindex)
select @password = @password + @char
select @counter = @counter + 1
end
select SUBSTRING(RTRIM(@password),1,15) AS Password
-
Steve,
This editorial on compliance that you mentioned was not yesterday, it was on 10/3/2006.... Time really is going fast.....
On a more professional note, don't forget your business process for restore. Not everyone who requests a database restore from the Help Desk has rights to it. Every restore request should be verified with the database owner and his boss.
Regards,Yelena Varsha
-
I know, I had this one ready to go last week and it didn't get run for some reason.
Password HelpViewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply