Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Securing the SA Account in SQL Server 2005


Securing the SA Account in SQL Server 2005

Author
Message
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (7.5K reputation)

Group: Moderators
Points: 7480 Visits: 1917
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
Mike C
Mike C
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1535 Visits: 1168

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



Ken Johnson-162971
Ken Johnson-162971
SSC-Enthusiastic
SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)

Group: General Forum Members
Points: 180 Visits: 375

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.


Markus
Markus
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1890 Visits: 3736
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.



David Bird
David Bird
Old Hand
Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)

Group: General Forum Members
Points: 321 Visits: 1190

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:

4-character password with 17 allowable character values
17*17*17*17 = 83,521 attempts
174 = 83,8521 attempts
4-character password with 63allowable character values
634 = 15,752,961 attempts
72-character password with 17 allowable character values
1772 = 3.911310908 +88

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

My PC Quick Reference Guide
Mike C
Mike C
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1535 Visits: 1168

For comparison purposes:

1772 = 3.911310908*1088 max. attempts
6372 = 3.568778227*10129 max. attempts
9572 = 2.489428061*10142 max. attempts

The max. attempts calculation can be a little misleading since it is a maximum, and the average password crack attempt will probably succeed in about 1/2 that number on average.

Additionally, if there is any additional known information about the password you can cut down the # of max. attempts considerably. For instance with the prior knowledge that your password is composed of two GUID's cast to character format and concatenated, I automatically know that the following 8 character positions are always hyphens: 9, 14, 19, 24, 45, 50, 55, 60. I also know that the remaining 64 character positions only have 16 possible characters each (0-9, A-F). So the new calculation is:

1664 * 18 = 1.157920892*1077

The estimates in the article you linked to appear to be using Pentium 100 MHz class machines in their estimates. Those estimates should probably take common 2.x GHz (and higher) processor machines into consideration as well.


David Bird
David Bird
Old Hand
Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)

Group: General Forum Members
Points: 321 Visits: 1190

I was finally able to put together a simple script that generates a more complex password for the SA account. It is 128 characters long and uses 81 different values. You can add more special characters if you want.

The basis for this script came from http://www.sswug.org/see/T-SQL_procedure_to_generate_passwords_for_Standard_Logins-26104


DECLARE @String varchar(81)
SET @String = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz~`!@#$%^&*()_-+=?<>'

DECLARE @Cnt as int
DECLARE @Pwd varchar(128)
SET @Cnt = 0
SET @Pwd=''
WHILE @Cnt < 128
BEGIN
SET @Pwd=@Pwd + SUBSTRING(@String,CONVERT(tinyint,RAND()*81)+1,1)
SET @Cnt=@Cnt+1
END
SELECT @pwd
EXECUTE master..sp_password null,@pwd,'sa'



David Bird

My PC Quick Reference Guide
paule
paule
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 189
SQL 2005 newbie here dealing with this issue -

1. How would I use SQL Enterprise Manager from a remote location with Windows Authentication? I thought the only way to access remotely would be via mixed mode ?

2. Why isn't there an option to disable remote logins on a per-user basis ?
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (7.5K reputation)

Group: Moderators
Points: 7480 Visits: 1917
paule (2/20/2008)
SQL 2005 newbie here dealing with this issue -

1. How would I use SQL Enterprise Manager from a remote location with Windows Authentication? I thought the only way to access remotely would be via mixed mode ?

2. Why isn't there an option to disable remote logins on a per-user basis ?


For #1:
VPN, creating an account on the local system that matches an account on the SQL Server server (local to the SQL Server server), coming through Terminal Services, etc.

For #2:
Because SQL Server doesn't have the concept of what IP, like with MySQL. Therefore, the user is either granted or denied login. Location is irrelevant. The only way to control this within SQL Server is to disable the network libraries and use Shared Memory only to allow connections local to the SQL Server system only. You could, with IPSEC policies, accomplish blocking access from remote. The same could be said about ACLs at the network layer.

K. Brian Kelley
@‌kbriankelley
Avanish Panchal
Avanish Panchal
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 149
Hi Ken,

Excellent article. I am planning to give a try following your this article.

I would really appreciate if you can provide "Implication of renaming sa account". I believe it would be a great help for others.

Cheers!
AP
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search