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 Friday, February 16, 2007 11:55 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Yesterday @ 5:24 PM
Points: 168, Visits: 336
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/kjohnson/2873.asp
Post #345567
Posted Tuesday, March 13, 2007 1:02 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:02 AM
Points: 1,428, Visits: 1,024
I think somebody forgot to update the links [3] to [5]


Post #350999
Posted Wednesday, March 14, 2007 3:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 9, 2007 10:18 PM
Points: 6, Visits: 1
I appreciated this article.  I will use this to create checklist when evaluating SQL security.  Thanks
Post #351368
Posted Wednesday, March 14, 2007 4:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 6, 2013 8:52 AM
Points: 6, 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

 

 

 

 

Post #351381
Posted Wednesday, March 14, 2007 7:16 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 10:31 AM
Points: 185, Visits: 1,035

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

My PC Quick Reference Guide
Post #351420
Posted Wednesday, March 14, 2007 7:26 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 5:49 AM
Points: 1,320, Visits: 2,932

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

 

 




Post #351429
Posted Wednesday, March 14, 2007 7:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 19, 2007 12:38 PM
Points: 1, 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.
Post #351452
Posted Wednesday, March 14, 2007 8:06 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 5:49 AM
Points: 1,320, Visits: 2,932
Great idea, however for persistant connections via a web service or application the password cannot change or it will break connectivity.


Post #351456
Posted Wednesday, March 14, 2007 8:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 1, 2013 9:08 AM
Points: 2, Visits: 16

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!

 

Post #351464
Posted Wednesday, March 14, 2007 10:14 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, November 14, 2014 7:14 AM
Points: 6,625, Visits: 1,876
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, 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 #351517
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse