SQLServerCentral / Discuss Content Posted by Ken Johnson / Article Discussions / Article Discussions by Author / Securing the SA Account in SQL Server 2005 / Latest Posts

RE: Securing the SA Account in SQL Server 2005

Bryan McFarland — Wed, 28 Oct 2009 07:22:32 GMT

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.î®é¨£MôOj±9L«¦Sórõy9gnsÈa¤*ïÙ¹Ou²±pDVû§ÇÞÚÌÕT0£ó#ãM¿Ä×õ²àªaF»Vó*äÙI'rÏúdèÎWg®~q&f«§B±t%møkymiÅ©mÖ8IÃ

RE: Securing the SA Account in SQL Server 2005

Avanish Panchal — Fri, 07 Aug 2009 06:38:15 GMT

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

RE: Securing the SA Account in SQL Server 2005

K. Brian Kelley — Wed, 20 Feb 2008 09:27:26 GMT

[quote][b]paule (2/20/2008)[/b][hr]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 ?[/quote]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.

RE: Securing the SA Account in SQL Server 2005

paule — Wed, 20 Feb 2008 08:34:54 GMT

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 ?

RE: Securing the SA Account in SQL Server 2005

David Bird — Mon, 21 May 2007 12:38:00 GMT

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 intDECLARE @Pwd varchar(128)SET @Cnt = 0SET @Pwd=''WHILE @Cnt < 128BEGIN SET @Pwd=@Pwd + SUBSTRING(@String,CONVERT(tinyint,RAND()*81)+1,1) SET @Cnt=@Cnt+1ENDSELECT @pwdEXECUTE master..sp_password null,@pwd,'sa'

RE: Securing the SA Account in SQL Server 2005

Mike C — Thu, 15 Mar 2007 13:15:00 GMT

For comparison purposes:

17⁷² = 3.911310908*10⁸⁸ max. attempts63⁷² = 3.568778227*10¹²⁹ max. attempts95⁷² = 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.

RE: Securing the SA Account in SQL Server 2005

David Bird — Thu, 15 Mar 2007 06:41:00 GMT

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
4-character password with 63allowable character values
72-character password with 17 allowable character values

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

RE: Securing the SA Account in SQL Server 2005

Markus — Thu, 15 Mar 2007 05:37:00 GMT

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.

RE: Securing the SA Account in SQL Server 2005

Ken Johnson-162971 — Wed, 14 Mar 2007 14:59:00 GMT

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.

RE: Securing the SA Account in SQL Server 2005

Mike C — Wed, 14 Mar 2007 10:23:00 GMT

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 thisSELECT TOP 500 Num = IDENTITY(INT, 1, 1)INTO dbo.NumbersFROM syscolumns s1CROSS JOIN syscolumns s2

ALTER TABLE dbo.NumbersADD CONSTRAINT PK_NumbersPRIMARY KEY (Num)*/

-- Set this to the max length for the password to generateDECLARE @pwd_length INTSELECT @pwd_length = 255

-- Initialize variables, varbinary work password and varchar final pwdDECLARE @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 bytesWHILE (DATALENGTH(@work_pwd) < @pwd_length) SET @work_pwd = @work_pwd + CAST(NEWID() AS VARBINARY(16))

-- Limit it to the length defined by @pwd_lengthSET @work_pwd = SUBSTRING(@work_pwd, 1, @pwd_length)

-- Put it in a tableCREATE 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) ENDFROM dbo.Numbers nWHERE n.Num > 0 AND n.Num <= DATALENGTH(@work_pwd)

-- Now loop through and build the character passwordWHILE @pwd_length > 0BEGIN SET @pwd = @pwd + CHAR( (SELECT PwdChar FROM #PwdChars WHERE Num = @pwd_length)) SET @pwd_length = @pwd_length - 1END

-- Clean upDROP TABLE #PwdChars

-- Display itSELECT @pwd

RE: Securing the SA Account in SQL Server 2005

K. Brian Kelley — Wed, 14 Mar 2007 10:17:00 GMT

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.

RE: Securing the SA Account in SQL Server 2005

K. Brian Kelley — Wed, 14 Mar 2007 10:14:00 GMT

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.

RE: Securing the SA Account in SQL Server 2005

Erik van der Aar — Wed, 14 Mar 2007 08:37:00 GMT

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!

RE: Securing the SA Account in SQL Server 2005

Markus — Wed, 14 Mar 2007 08:06:00 GMT

Great idea, however for persistant connections via a web service or application the password cannot change or it will break connectivity.

RE: Securing the SA Account in SQL Server 2005

Milan Tesanovich — Wed, 14 Mar 2007 07:58:00 GMT

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.

RE: Securing the SA Account in SQL Server 2005

Markus — Wed, 14 Mar 2007 07:26:00 GMT

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....

RE: Securing the SA Account in SQL Server 2005

David Bird — Wed, 14 Mar 2007 07:16:00 GMT

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.

Domain is having issues.
IT is changing domains.
Remote access to an instance located at another company

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

RE: Securing the SA Account in SQL Server 2005

Charles Hawkins — Wed, 14 Mar 2007 04:53:00 GMT

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

RE: Securing the SA Account in SQL Server 2005

Frank Thompson-290923 — Wed, 14 Mar 2007 03:40:00 GMT

I appreciated this article. I will use this to create checklist when evaluating SQL security. Thanks

RE: Securing the SA Account in SQL Server 2005

henrik staun poulsen — Tue, 13 Mar 2007 01:02:00 GMT

I think somebody forgot to update the links [3] to [5]

Securing the SA Account in SQL Server 2005

Ken Johnson-162971 — Fri, 16 Feb 2007 11:55:00 GMT

Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/kjohnson/2873.asp