SQL Clone
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
Ken Johnson-162971
Ken Johnson-162971
SSC Veteran
SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)

Group: General Forum Members
Points: 284 Visits: 375
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/kjohnson/2873.asp
henrik staun poulsen
henrik staun poulsen
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2213 Visits: 1225
I think somebody forgot to update the links [3] to [5]



Frank Thompson-290923
Frank Thompson-290923
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 1
I appreciated this article. I will use this to create checklist when evaluating SQL security. Thanks
Charles Hawkins
Charles Hawkins
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 19
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


David Bird
David Bird
SSChasing Mays
SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)

Group: General Forum Members
Points: 653 Visits: 1224

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.

  1. Domain is having issues.
  2. IT is changing domains.
  3. 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



David Bird
Markus
Markus
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4049 Visits: 3798

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





Milan Tesanovich
Milan Tesanovich
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 1
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.
Markus
Markus
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4049 Visits: 3798
Great idea, however for persistant connections via a web service or application the password cannot change or it will break connectivity.



Erik van der Aar
Erik van der Aar
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 26

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!


K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (13K reputation)

Group: Moderators
Points: 13788 Visits: 1917
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
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