Take the 2-minute tour ×
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.

Even Microsoft discourages the use of SQL Server authentication mode, but our applications require it.

I've read that it's a best practice to not let users use the sa login directly, instead using Windows Authentication and allowing those accounts (or account groups) sysadmin privileges.

  • Isn't that essentially the same thing? What are the advantages/disadvantages?
  • How does the best practice increase the security of my SQL Server instances?
  • Does this only apply to production instances, or to our internal development instances, too?
share|improve this question
    
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 Seigel Aug 8 '11 at 0:48
5  
It's as good a practice as giving everyone a copy of your house key. ;) –  Jeremiah Peschka Aug 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 Stringer Aug 8 '11 at 23:14
2  
Your applications do not require it. Please tell us why you think they do. –  sqlvogel Aug 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 Stringer Aug 22 '11 at 3:55

8 Answers 8

up vote 42 down vote accepted

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.

"...and allowing those accounts (or account groups) sysadmin privileges."

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:

  1. Stop people from breaking the server
  2. 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.

share|improve this answer
1  
SA on one instance can confer God rights on all instances because of linked servers, ntfs etc –  gbn Aug 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 Ozar Aug 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) –  gbn Aug 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 Ozar Aug 9 '11 at 14:35
    
Very well explained. Thanks, Brent. –  Jon Seigel Aug 11 '11 at 4:27

sa = sysadmin

Logging in with sa gives the user the power to do all of the following: - drop and create databases - modify objects in the database - drop and create logins - change passwords - delete all the data

Granting a windows account sysadmin privileges accomplishes the same thing.

The list goes on, but this should give you a few good reasons to NEVER EVER EVER let a non-admin use sa.

You should create a windows or SQL account with the bare minimum privileges necessary to get the apps to work. (i.e. login, access stored procedures and views)

share|improve this answer

Actually if you read this whitepaper: SQL Server Separation of Duties Whitepaper

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.

share|improve this answer
2  
+1000 That whitepaper is excellent. I learned a lot from it. –  Jon Seigel Aug 8 '11 at 23:16

If you are subject to external regulatory controls or standards (SOX, PCI etc), then you

  • will fail an audit
  • are failing on your monthly PCI checks

Developers should have db_owner on a network SQL Server only for development.

If your SQL Servers are all on the network with a standard build (i.e. same service account) then sa in one confers rights on them all.

If the service account is local admin, then your developers have full control over the servers too.

There are no upsides.

share|improve this answer

The best practice is putting to sa some strong password and forget it.

share|improve this answer

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.

share|improve this answer
    
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 –  gbn Aug 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. –  Richard Aug 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.
share|improve this answer
    
-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" –  Fulproof Nov 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? –  Marian Nov 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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.