Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Rants about the sa account!

By Steve Jones,

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

 

Total article views: 6743 | Views in the last 30 days: 1
 
Related Articles
FORUM

Mask Password

Password stored in plain text

ARTICLE

Someone Actually Thought This Through

A response from Great Plains Software on the use of the sa account in Dynamics software.

FORUM

sa with Great Plains SOX compliance.

Forgive my question if it has already been answered elsewhere. Would appreciate links to the forums ...

FORUM

Passwords

Storing passwords securely

FORUM

Are server account passwords available?

Can I recover static user passwords?

Tags
other    
rants    
security    
strategies    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones