Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm asking this half as a canonical reference as I couldn't find anything through Google (feel free to edit in an aspect I didn't cover), and half to gain ammo to convince management that making this change is a Good Thing(tm).
–
Jon SeigelAug 8 '11 at 0:48

6

It's as good a practice as giving everyone a copy of your house key. ;)
–
Jeremiah PeschkaAug 8 '11 at 1:35

1

Not to mention, 'sa' is a publically known login name for SQL Server, therefore making it an easy target. No guessing really involved. I've seen companies change the name from 'sa' to something different. Even if only a small level, it makes it a bit harder to cyber intruders to get ahold of something.
–
Thomas StringerAug 8 '11 at 23:14

3

Your applications do not require it. Please tell us why you think they do.
–
sqlvogelAug 11 '11 at 5:45

Great question. If only other database professionals stopped and asked this same question, there'd be a lot less security holes.
–
Thomas StringerAug 22 '11 at 3:55

8 Answers
8

You have several different questions in here, so I'll knock 'em out individually:

"I've read that it's a best practice to not let users use the sa login directly, instead using Windows Authentication"

You're mixing two things here: the concept of SA, and the concept of SQL authentication and Windows authentication.

SQL authentication is a list of usernames and passwords that are stored in each and every SQL Server. The fact that it's stored in SQL is the first problem. If you need to change a login's password, you have to change it on every server (or maintain different passwords on different servers). With Windows authentication, you can centrally disable logins, change passwords, set policies, etc.

If you choose to use SQL authentication, then SA is just one SQL authentication login. It's the default administrator username, just like Administrator is in Windows authentication. It has local superpowers on that one instance, but not global superpowers across all instances.

Whatever authentication method you choose, ideally you want to follow the principle of least privilege: granting people the bare minimum rights they need to get their job done, and no more.

Don't think of them as just logins - they're people who can get you fired. If they drop the database or disable your backup jobs accidentally, they're not going to get fired, because by default, SQL doesn't track who did what. You're the one who will get fired because it's going to happen, and you're not going to be able to say which person did it.

"How does the best practice increase the security of my SQL Server instances?"

You want to do two things:

Stop people from breaking the server

When they do break the server, be able to identify exactly who did it

The first one is accomplished with the principle of least privilege: giving folks only the permissions they need, and nothing more.

The second one is accomplished by giving each person their own login, not allowing shared logins (like letting everyone use the same username/password), and ideally, auditing the logins. You probably won't do that last part right away, because it's kind of painful, but let's put the pieces in place first so you can add auditing later after somebody drops a database and your boss wants to know why.

I know what you're thinking: "But we're coding apps, and the app needs a login." Yes, give the application its own login, and the developers need to know that password, but that login should be so stripped of permissions that nobody in their right mind would want to use it. For example, it might need to be in the db_datareader and db_datawriter roles alone, nothing else. That way it can insert, update, delete, and select data, but not necessarily change schemas, add indexes, change stored procedures, etc.

"Does this only apply to production instances, or to our internal development instances, too?"

I think it applies just as much to development instances because I'm usually worried about people breaking things. People just love to break servers in development. And of course, when it's time to bundle up the list of changes to migrate to production, I need to know whether a particular index really is vital to the app or whether some bonehead just ran the Database Tuning Advisor and told it to apply all changes. Proper permissions helps lessen that pain.

SA on one instance can confer God rights on all instances because of linked servers, ntfs etc
–
gbnAug 8 '11 at 12:08

@gbn - I'm not sure I follow. Can you point to some examples of that? I can understand if you're talking about poor configurations (like all servers sharing the same service account) but I'm not clear on how you're accomplishing that when the servers are running under different service accounts.
–
Brent OzarAug 8 '11 at 17:33

A standard build would use the same domain account in large organisations. Even if they were different, they'd be a member of the same AD group (subject to regions, of course, perhaps) so have the same rights. I've seen both at large worldwide organisations (Investment banks)
–
gbnAug 8 '11 at 17:46

8

Okay, that's a different problem. In most secure organizations I've seen, it's standard practice to put each server under its own service account. That's also part of my SQL Server Setup Checklist online. Sure, if you ignore that basic obvious step, then you're less secure - but what's the point?
–
Brent OzarAug 9 '11 at 14:35

It will tell you that NO ONE should be using sysadmin privileges on their account. Your daily access account should be given minimum access, not explicit sysadmin rights. Given them CONTROL SERVER is actually close to what sysadmin is and then allows you to still DENY/REVOKE access where they do not need it. Allowing sysadmin rights to anyone becomes an issue if you are required to meet any IT compliance standards. Most of the standards require minimum use of the sysadmin role.

I disable and rename the "sa" account, just like you would do with the built-in administrator account on the OS. Only to be used in emergency.

Developers are usually given full access to their developement environment. Then when their program is moved to pre-production instance, their access should be minimum or none; just like it would be in production. That is a perfect world. If however you are not in that and your developers have higher privilege than you feal they need, I would audit the heck out of their accounts. I would capture everything and anything they do to an extent. So in the event something goes wrong when they were on your production server, you can go back and find out exactly what they did.

There is an upside, it's just outweighed by the others: convenience. It's very easy for everyone to use sa (probably with "password" as the password), that's why it continues as a practice.
–
Jon of All TradesJan 20 at 16:44

Addressing your final question, we use SA accounts on all of our development databases (with the password "sa", at that!)

However, it's critical to note that we don't store the data and we don't generate the data. Our databases are solely used for a datastore for a C/C++ application. These development databases purely contain test data and are frequently created, dropped, modified, etc.\

Also, just as critical, all development databases are installed on development machines. (One copy per developer, at least!) So if someone messes up an entire installation, they've only messed up themselves, and no one else.

When it comes to an environment where you want to insure that your database, tables, and data are actually consistent and safe, then you want a nice, solid SA password. If you leave this weak, then anyone and everyone has full access to your data and can completely destroy your database.

For a bunch of developers with their own copies of the database that purely contain test data, I still have yet to find a solid argument for maintaining a strong SA account.

With sa, they could enable XP_cmdshell for example and then demand it goes into prod. And as I answered, it can confer rights on all servers. Dbo and partial sa (create db) etc: no problems
–
gbnAug 21 '11 at 15:23

In a development environment that doesn't generate or store customer data--an environment where all copies of the database are test copies, purely for development and deployment--I can't see this really being a problem. If there is ever a potential of bad code hitting production databases, then yes, I can see a bit tighter ship.
–
RichardAug 22 '11 at 1:28

I have two options in my mind right now, as to why one shouldn't have a weak SA account. If you have a weak/empty password for SA one evil person (translate that to 'friendly colleague') could:

enable xp_cmdshell system procedure and, using the privileges of the Sql Server service account, do damage to your local box (create/remove files..). Having low security for SA doesn't actually say much about the instance settings, but could imply that the service user could follow bad practices (like being an admin :-) );

enable mail on your instance and fast forward a small spam fun script (that will likely cause you some trouble either with your internet provider or with your colleagues..depending on where the mails go ;-) );

I won't point out the obvious facts that it can drop databases, logins ... etc.

Isn't that essentially the same thing? What are the advantages/disadvantages?

Not necessarily: eg - on the SQL Server instance from your laptop the difference between Windows authentication and SQL authentication means that, in theory, an external attacker could use only an SQL account, because the windows authentication can't be used outside your own domain account. A user could scan neighbor laptops from the mall where you're drinking your coffee and might see you have an SQL instance installed and started and could try to have some fun for free. It's not that it could happen often... but it's a possibility :-).

How does the best practice increase the security of my SQL Server instances?

As every best practice in this world does its job..decreases the chances of a possible downside (eg: the best practice of doing physical activity will decrease the chances of you being like me.. a couch potato) that could occur otherwise.

Does this only apply to production instances, or to our internal development instances, too?

Let's say that I'd suggest to implement security best practices (tested and modified to your environment's needs) at least in production. But if in development you also use production data (sensitive..etc) than it's a strong indication of you having to also alter your development practices.

-1 The question has really asked why to favor Windows Integrated authentication and avoid SQL Server authentication while it simply impossible to accomplish BUT not how or "why one shouldn't have a weak SA account"
–
FulproofNov 14 '13 at 22:01

@Fulproof: I understand what you're saying and thanks for the feedback. My interpretation of a weak SA account is a SA account with a weak/no password that's used by everyone in a company. But the question is old and I don't fully remember all details. And I was having an accent on the main question from the title - Why is it a bad practice to allow everyone to use the sa login?
–
MarianNov 15 '13 at 9:22

Any supplied default SQL Server system security parameter should be modified. It's recommended not to use the mixed mode (enables both Windows and SQL Server authentication) for authentication. Instead, switch to the Windows authentication only - that will enforce the Windows password policy - checking of the password length, life duration, and history. The feature of the Windows password policy that makes it different from the SQL Server authentication is the login lockout - after a number of successive failed logon attempts the login becomes locked and unusable for further use

On the other hand, the SQL Server authentication does not provide any methods for detecting brute-force attack attempts, and what's worse, SQL Server is even optimized for handling large number of rapid login attempts. So, if the SQL Server authentication is a must in a particular SQL Server system, it's highly recommended to disable the SA login