SQLServerCentral Article

Rants about the sa account!

,

Rants About the sa Account

Updated - May 1, 2001

I do think this is a problem that is rampant in development. And one that should be addressed!

I know from experience that the use of the administrator account in network installations is often chosen

for convenience and poses a huge security risk. I hope by rereleasing this article, more people

will read about the dangers of using sa (or Administrator) and modify their habits to build better

applications.

Updated - November 5, 2000

I have received a great deal of feedback about this article, some positive, some negative. It appears

that a number of people thought this was specifically about Great Plains Software. If this is the case, I

apologize to them and Great Plains Software. I used Great Plains as an example of a problem that I have seen

in many software applications.

Great Plains was kind enough to provide me with some information regarding their SQL Server based products

and the "sa" account. Here is what they provided to me:Great Plains Response.

Introduction

Let me say at the start, that this an article written in frustration and anger. However, it

is also an issue that I constantly see implemented or am asked questions about. It is also something

I feel strongly about. The sa account is the overriding ruler of the SQL Server world. Unless you are

encrypting the data inside the tables(something that is cumbersome and difficult to implement),

the sa account must be the most trusted and sacred user account for a SQL Server. It should be treated as

such and not used for most SQL tasks.

Background on the Article

A little while ago I had the great pleasure of working with a consultant during the installation of

a Great Plains accounting system. The consultant was more of an accountant than a technical user, and

kept swearing that he needed the "sa" password to add users and install the system. From what I could

gather after working with him, most of the security withing Great Plains is table based with only a standard SQL login being

required for the addition of the users. No problem, I have a SQL 7 server, so I grant his user the

"Security Admistrator" role on the server and off we go. Guess what?

No go! He cannot use Great Plains to add users. The client application has the buttons greyed out, and

my consultant smugly asks for the "sa" account again. I am stunned and cannot believe this, so we call Great Plains and work with the support

group. Guess what I find out? The application is hard-coded to look for the "sa" login before the !@$@%#%#$

VB buttons will activate.

Brilliant design (this needs a very sarcastic tone), hard coding an account name into your application that you will distribute to (hopefully)

thousands of desktops. And require that every accountant, none of which I have ever known to be very

computer-saavy, to have complete access to the SQL Server!! Needless to say, this is where the frustration and

anger came from.

Then, top it off with sending lots of nicely formatted .SQL scripts to "fix" bugs that are found during

an installation. Of course, we would not want a non-technical person to run these scripts, so you have

to buy a support contract from a value-added reseller. No big deal, except the VAR is not required to

employ technical people!!!!! My brilliant consultant did not know the difference between a login and a user! He

also was confused when I sent him a document where I specified a field as an integer (and included the max size 4+B)

and kept telling me that he had to "fit" this number into a 50 character field and how could he do it?

By the way, if you work for Great Plains or Solomon software (or know someone who does), it's not likely

you will ever come to work for me. Allowing development to proceed in this manner is inexcusable! UPDATE:

I received a response from Great Plains aka Microsoft as of this update, and have included the response in

another article.

Why Is This A Big Deal!

You may be wondering why I am concerned with our CFO having the sa password? Well, it fundamentally

strikes me as a bad idea. I do not think he would maliciously do something with the server, but he could

inadvertantly do something. Also, since he is less technical and busy, the temptation to "log" in a

consultant or give out the password is there. I hope that everyone knows that the more people that know

a "secret", the less "secret" it really is. The same applies here. Someone could easily log in as "sa" and

make changes to any data or view data that they should not be able to alter/view.

Another problem is that I have other applications on this SQL Server in other databases. The Great

Plains answer "Our clients usually have a dedicated SQL Server for Great Plains" (use a haughty voice to

get the effect). $$^&##%#@&$%#@ idiots! The whole point of the login/user separation with SQL Server is to

allow different databases to be hosted in a cost effective manner. The stress of Great Plains software on my

server is noise compared to most other applications I have. Besides, who made you IT lord of SQL Server

database planning?

I am very concerned about anyone that is not trusted and knowledgable about SQL Server having Enterprise

Manager, Query Analyzer, or any administrative interface with my SQL Servers. Not because I do not trust anyone, but

the potential for mistakes is too great. One thing I have learned over the years is that T-SQL allows me

to make mistakes much quicker than VB or some other tool (Ever issue a "delete xxx" without a where clause?). And

I know what I am doing!!!!!!

I have also seen enough #$&%$$@@@ code from third parties to be very wary of any fixes they send downstream.

I take a few minutes to look at their code before running any patches on the SQL side. At least that way

I can have an opportunity to catch a mistake. (Who knows how many of these patches are generated on the fly

by tech support people?).

This problem is not limited to Great Plains and I do not mean to pick on their software, they were just the

catalyst for this article. This is also not limited to third party vendors. I got an email from someone who recently got a DBA job

and was told he could not change the sa password (which was blank) because too many applications were hard

coded with this account. How many times has anyone seen this? I told him to get it changed or start looking

for a job. Because something will break and he will be responsible. I got told at a previous job that the

developers needed sa rights. That lasted about two days until I got a developer in front of the CEO, CFO, and COO and

had him explain why he needed rights. The IT track record of "quick fixes" that resulted in downtime were

quite enough to get that changed.

So What Can You Do?

For one, if you develop applications and use "sa", then pick up a pen from your desk and smack yourself on the

opposite wrist. It's just plain dumb. If anyone can even remotely think of a reason why this is not true, let me

know and if I agree, I will post your response with a HUGE apology in 72 pt type!

I cannot tell you how many books I have read which use "sa" to demonstrate something. With a blank

password no less!!!!! Even dumber than doing it yourself is to promote this to others. I would think most people would know better. Have someone create

a user account and use that in your examples.

HUGE PROPS TO THE SQL 2000 TEAM

Someone finally built an application that requires a password on setup. In SQL 2000, if you want a blank

sa password, you have to check a box specifically to allow this. If anyone ever checks this box, they should

be fired on the spot. There is no excuse. At the very least, use your own network password as a temporary pwd.

Being a results oriented program, I have a few suggestions for everyone (especially the authors out there).

  • Before installing SQL, create a password for the sa. This can be the network admin password or

    the existing sa password, or a new one, but before inserting the CD, create one.

  • As soon as you create a database, before you do anything else, before tables, before starting development,

    anything, create a development role. If you need a start, use this:

    use mydatabase
    exec sp_addrole 'development'
    
  • Now, before you do anything else, create a user for this role that developers can use. If you

    never create any other user or anything, at least they can use this user.

    exec sp_Addlogin 'devuser', 'devuserpassword', 'mydatabase'
    go
    use mydatabase
    exec sp_adduser 'devuser', 'devuser', 'development'
    

There are probably more things you should do, but these represent a good start. I also would not grant DBO rights

to anyone, but that is another article. Development practices need more space than I can devote here. UPDATE:

Andy Warren wrote a great article (

Using the Public Role to Manage Permissions) about why you should not use the public role.

Now I also realize that you have to trust someone. The DBA and network administrators control access to most

data and must be trusted. You should also, however, have some auditing and redundancy built into the system

as well. We deliver a sealed envelope to the CFO with sa passwords inside so he can get them if something happens

to me. I also keep the network admins in the "Administrator" group on SQL Server on the off chance that I forget

the "sa" password and need to get in quickly. This also allows a technical person (my network admin) to act as

my "hand" in a remote support situation if needed. I could also use the CFO in a last resort as well.

We also have to trust the network admins and DBAs to be professional. If you do not trust the people you

work with, get rid of them. It's really that simple.

I know this is a bit strong, but I really think that too many people are too lax about security, especially

with the sa account. I am also not picking on Great Plains alone, lots of software developers do this, I

just happened to interact with them recently, so they are my focus right now.

As we grow more and more connected, especially across unsecure networks, it pays

to become a bit more careful. I hope everyone out there will pay a bit more attention to security and

please, NO MORE blank sa passwords!

Steve Jones - 10/26/2000 (Updated May 1, 2001)

Updates

I received so much feedback from this article, I decided to include some of the comments here.


Amen!

As a Great Plains VAR, my staff and I

have been pushing this point with Great Plains for years (obviously, not

successfully).  We have an IT staff full of DBAs that support our

Application Consultants on the back end systems and this has been a thorn in

our side for years.

We had hoped with the release of SQL 7 that Great Plains would address this

issue but it didn't.  Then along comes SQL 2000 and it seems the same

oversight has again taken place.  There is no logical reason for this to be

the case.  We stress with our own in-house developers (creating that 3rd

party code you were speaking of) to never hard code user accounts for

access, VERY lazy programming.  At least we have been successful there.

Keep up the good work!  My staff and I enjoy your articles- this one will be

passed around to many at Great Plains!  By the way, due to our relationship

with Great Plains and many of our customers, please don't use my name or

company name if you distribute my comments.  Thanks!

IT Services Manager


Enjoyed your rant on the sa account.  It was very timely and appropo for us.

A co-worker and I had decided just yesterday, that today we would finally

change that damned sa password on our sql server that runs Great Plains

Dynamics. (We'd been meaning to do this for a while now.)  A good part of

the accounting department already knew the password (thanks to the

reseller), and everyone that has ever visited us from the reseller that

supports us knows the password -- and with staff turnover -- believe me,

there've been plenty.

Another irritating thing is when the third party Great Plains seller/support

personnel come to your site and help themselves to user manager and set up

domain admin accounts for themselves instead of requesting a logon from the

network administrator and/or specifying where they need access.   Letting

them sit there and install/configure their apps after you've logged them

onto the server is one thing, but creating accounts so that they don't have

to "bother you" on return visits is a bit presumptuous.  And last but not

least, we had 2 sa logins:  sa and SA.  All we could figure was that because

the Great Plains Dynamics sa password (the one that half the world knows) is

in ALL CAPS, somebody decided that it would be convenient to have a matching

SA in caps. And of course, might as well make it a dbo while you're at it.

Talk about confusion.  (Not to mention the lame password they use.)  We

deep-sixed that login today as well.  We'll have a good chuckle next time a

support person or trainer comes on site and sits down and assumes he/she has

a login to use.   😉

Keep ranting.

Network Administrator


Hello Steve,

I must say I really enjoyed reading your article.....I must say that I have

had many unfortunate opportunities dealing with application developers that hardcode

the sa id & password into their app. Imagine trying to get them to change their code!!

It would have been easier to just kill all of them and get some good DBA's to rewrite

their code.....


Finally someone who understands the importance of the 'sa' account.

The only reason I am writing this e-mail is because you are one of the first

people I came accross who understands the importance of that @#!$ account.

I am a software developer for a company whos main focus is on SQL Server and VB

and even within the company people are using the 'sa' login as if it was nothing

more than a database user etc.

Thanks for the brilliant article and you can be sure that I will read them much

more often in the future.

--Software Consultant


Return to Steve Jones Home

 

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating