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