February 16, 2007 at 11:55 am
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/kjohnson/2873.asp
March 13, 2007 at 1:02 am
I think somebody forgot to update the links [3] to [5]
March 14, 2007 at 3:40 am
I appreciated this article. I will use this to create checklist when evaluating SQL security. Thanks
March 14, 2007 at 4:53 am
Here is some code for generating a nice 128 character SA password; have fun:
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
March 14, 2007 at 7:16 am
Nice article. We only use SA for database ownership and sometimes for SQL Job
ownership. It is never used for connecting to the server because no one knows what
the password is. We use a simpler routine to generate a random 72 character value
for it.
DECLARE @pwd char(72)
SELECT @pwd=convert(char(36),newid())+convert(char(36),newid())
EXECUTE master..sp_password null,@pwd,'sa'
Sample value:
0A8A24E8-A728-4DCF-B561-179511138895AAB9C183-BC26-49B3-BDC5-009AFFA5B83B
You are right about SQL ids not going away. Many developed and purchased applications
use them for simplicity and to reuse execution plan
"Only query plans with the same user ID are candidates for reuse."
See http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
There are times when we DBA's need to use a SQL id.
To accomplish this we still don't use SA, instead we have a SQL Id with system
administration authority and assign it a complex password.
Thanks for giving me the opportunity to spout off about not using SA and having to
use mix mode
David Bird
March 14, 2007 at 7:26 am
The problem is that most basic bundled software packages REQUIRE 'sa' to be used to do upgrades that I know of. It is very sad that software companies utilize 'sa' because it is just easier to use the 'god' right ID. Once I attempted to create an ID, assign it 'sa' rights for a temporary ID for one of these software apps we have to do an install/upgrade. It never worked correctly. I could not figure out why, so I changed the 'sa' password, we did the upgrade and I changed it back. Odd, very odd.
Some of the software vendors that come on-site here to install a new SQL Server based app want 'sa' password to be a word or sa... I over ride that and they want the user account password to be the same as the login or a simple word.... I override that idea too. A couple have come on-site to install and with that thought process and are not happy that I require a more difficult password as the application is already setup for a specific ID and password.... jeesh....
March 14, 2007 at 7:58 am
I recommend to my clients that they generate a password and use the white envelope approach to secure it. The password is placed in an envelope and secured with the CFO or other management person outside IT and accessed only when needed. Once it is used, the password is changed and secured in the white envelope until needed again.
March 14, 2007 at 8:06 am
Great idea, however for persistant connections via a web service or application the password cannot change or it will break connectivity.
March 14, 2007 at 8:37 am
Thanks. Always good to read articles about security.
In our case we have a strong password for the sa account. To keep track of all the DBA activities we use for several DBA's a personal administrator account.
Only Windows authentication is not an option for us. The database administrators rights are of less concern than all the users being capable of login in with or without Windows authentication with a lot of tools other than the application where the database in the first place is used for.
We have build our own software with a built-in password scrambler. So the password you use to connect to the database is different from the one you type in at connect time. That's our solution to keep all those end users out of the database. So in no way Windows authentication for us!
March 14, 2007 at 10:14 am
This is true... a few apps require sa login rights, which drives me up the wall. DB_Creator rights, too, bug me, having dealt with that and a particular security vulnerability scanner lately.
With all that said, a long and complex password is the way to go. Someone else mentioned the white envelope approach. Make sure one copy is stored off-site, too, in case of a DR situation.
K. Brian Kelley
@kbriankelley
March 14, 2007 at 10:17 am
This is why maintenance windows are so important. There's a real debate as to how long passwords should be kept. We want our users to change their passwords every 30 / 42 / 60 / 90 days because if a password stays out there long enough, the window to brute force grows larger, making it more likely that if someone were to get the password hash, a brute force attempt could succeed. What's true for user passwords is true for "service accounts," too, though. While it's a burden to change these passwords, it's probably in everyone's best interest to do so. There needs to be a methodology to change passwords like this in the event someone with knowledge of the password (or the ability to gain the password) leaves the organization.
K. Brian Kelley
@kbriankelley
March 14, 2007 at 10:23 am
One problem with that password generator - you only allow 17 total characters (0-9, A-F, hypen) which makes hacking it much simpler than if you allowed 63 or more characters (A-Z, a-z, 0-9, hyphen). Just as a simple example, a brute force attack on a 4-character password with only 17 allowable characters would take (at maximum), 83,521 attempts. A brute force attack on a 4-character password with 63 allowable characters requires a maximum of 15,752,961 attempts. Bute forcing a 4-character password that uses all standard keyboard-accessible printable characters (95 total) requires 81,450,625 max. attempts.
Here's a simple password generator that takes your idea of using NEWID(), but generates significantly stronger passwords with any printable/keyboard accessible characters from SPACE (0x20) to tilde (0x7e). This may have to be modified to eliminate certain characters if you want or need to exclude them:
/*
-- Requires a numbers table like this
SELECT TOP 500 Num = IDENTITY(INT, 1, 1)
INTO dbo.Numbers
FROM syscolumns s1
CROSS JOIN syscolumns s2
ALTER TABLE dbo.Numbers
ADD CONSTRAINT PK_Numbers
PRIMARY KEY (Num)
*/
-- Set this to the max length for the password to generate
DECLARE @pwd_length INT
SELECT @pwd_length = 255
-- Initialize variables, varbinary work password and varchar final pwd
DECLARE @work_pwd VARBINARY(256)
SET @work_pwd = CAST('' AS VARBINARY(256))
DECLARE @pwd VARCHAR(256)
SET @pwd = ''
-- Use NEWID() to generate somewhat "random" string of bytes
WHILE (DATALENGTH(@work_pwd) < @pwd_length)
SET @work_pwd = @work_pwd + CAST(NEWID() AS VARBINARY(16))
-- Limit it to the length defined by @pwd_length
SET @work_pwd = SUBSTRING(@work_pwd, 1, @pwd_length)
-- Put it in a table
CREATE TABLE #PwdChars (Num INT PRIMARY KEY NOT NULL, i INT,
PwdChar INT)
-- We need to account for non-printable and special characters here. We only want
-- keyboard accessible characters; basically SPACE (0x20) to tilde (0x7e).
INSERT INTO #PwdChars (Num, i, PwdChar)
SELECT n.Num, CAST(SUBSTRING(@work_pwd, n.Num, 1) AS INT),
CASE
WHEN CAST(SUBSTRING(@work_pwd, n.Num, 1) AS INT) < 32
THEN CAST(SUBSTRING(@work_pwd, n.Num, 1) AS INT) + 32
WHEN CAST(SUBSTRING(@work_pwd, n.Num, 1) AS INT) > 254
THEN 126
WHEN CAST(SUBSTRING(@work_pwd, n.Num, 1) AS INT) > 126
THEN CAST(SUBSTRING(@work_pwd, n.Num, 1) AS INT) / 2
ELSE CAST(SUBSTRING(@work_pwd, n.Num, 1) AS INT) END
FROM dbo.Numbers n
WHERE n.Num > 0 AND n.Num <= DATALENGTH(@work_pwd)
-- Now loop through and build the character password
WHILE @pwd_length > 0
BEGIN
SET @pwd = @pwd + CHAR(
(SELECT PwdChar
FROM #PwdChars
WHERE Num = @pwd_length))
SET @pwd_length = @pwd_length - 1
END
-- Clean up
DROP TABLE #PwdChars
-- Display it
SELECT @pwd
March 14, 2007 at 2:59 pm
Great discussion.
I like the password generators. The easier you can make it to get a long/strong password, the more likely people are to use them.
Vendor applications that require elevated privileges (or even use a hard-codes sa account and password) can be a thorny issue. We don't want them on our servers, but the business wants them so they can remain competitive. To accomodate both sides, I try to segregate vendor application databases on their own instance to keep them away from corporate data, when possible.
Don't forget to have the SQL Service for the "vendor" instance run under a different domain account than your "corporate" SQL Servers so the vendor instance won't have access to any valuable permissions on your corporate instance.
March 15, 2007 at 5:37 am
I really do not think SQL Authenticated logins are going away any time soon. Otherwise MSFT would not have upgraded them in 2005 to allow for password expiring and the lockout feature after X attempts. I am glad they have added this feature as it can be yet another weapon in an attempt by DBA's to secure passwords. At minimum we all should use the failed attempts feature... even if it is set very high it will avoid some password cracking software.
March 15, 2007 at 6:41 am
To calculate the maximum attempts it would take to crack a password. You need to take the number of allowable character values and raise that value by the number of populated positions in the password. This calculation only works if each position in the password allows the same number of character values. Ahh its to early for this math.
Examples:
For an online calculator go to http://www.motionnet.com/calculator/
Now if you are wondering how long it will take to crawk a password, I will need to refer you to a site that tries to estimate it
http://www.lockdown.co.uk/?pg=combi&s=articles
David Bird
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply