Securing the SA Account in SQL Server 2005

Question

Post reply

Securing the SA Account in SQL Server 2005

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Mike C SSC-Insane Points: 23224 More actions · Answer 1

For comparison purposes:

17⁷² = 3.911310908*10⁸⁸ max. attempts

63⁷² = 3.568778227*10¹²⁹ max. attempts

95⁷² = 2.489428061*10¹⁴² 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:

16⁶⁴ * 1⁸ = 1.157920892*10⁷⁷

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 SSCarpal Tunnel Points: 4669 More actions · Answer 2

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

paule SSC Veteran Points: 266 More actions · Answer 3

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 SSC Guru Points: 114642 More actions · Answer 4

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 Valued Member Points: 67 More actions · Answer 5

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

Bryan McFarland SSC Enthusiast Points: 109 More actions · Answer 6

Great article. I also second the idea of an "Implications of changing or disabling SA account" article. I've been looking for that information but haven't come across anything yet.

Another question, everyone so far has only mentioned using standard characters for the password. I use a password vault app (keepass) to store and generate passwords, and one of the options during generation is to use high ANSI characters. Using these extra characters will expand the character set available for passwords, but will it cause any problems? I typically don't use the SA account anyway and don't care about typing the password, copy and paste work fine if I really need it, is there anything else I need to worry about if using these characters in my passwords? SQL seemed to take the new password fine and I was able to authenticate again during a test. An example of a possible 120 char password is below, assuming is posts correctly.