Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Securing the SA Account in SQL Server 2005 Expand / Collapse
Author
Message
Posted Wednesday, March 14, 2007 10:17 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Thursday, April 03, 2014 10:06 PM
Points: 6,621, Visits: 1,851
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #351519
Posted Wednesday, March 14, 2007 10:23 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:18 PM
Points: 1,276, Visits: 1,132

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


 

 

Post #351523
Posted Wednesday, March 14, 2007 2:59 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 03, 2014 8:01 AM
Points: 164, Visits: 314

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.

 

Post #351622
Posted Thursday, March 15, 2007 5:37 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 17, 2014 8:43 AM
Points: 1,201, Visits: 2,661
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.


Post #351748
Posted Thursday, March 15, 2007 6:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 7:24 AM
Points: 182, Visits: 996

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
Post #351762
Posted Thursday, March 15, 2007 1:15 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:18 PM
Points: 1,276, Visits: 1,132

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.

Post #351919
Posted Monday, May 21, 2007 12:38 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 7:24 AM
Points: 182, Visits: 996

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
Post #367730
Posted Wednesday, February 20, 2008 8:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 18, 2014 11:08 AM
Points: 10, Visits: 140
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 ?
Post #458018
Posted Wednesday, February 20, 2008 9:27 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Thursday, April 03, 2014 10:06 PM
Points: 6,621, Visits: 1,851
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #458066
Posted Friday, August 07, 2009 6:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 6:43 PM
Points: 1, Visits: 126
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
Post #766880
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse