22

My first question ever, please be gentle. I understand that the sa account enables complete control over a SQL Server and all the databases, users, permissions etc.

I have an absolute belief that applications should not use the sa password without a perfected, business person focused reason why. Answers to This Question include a lot of my reasoning for an IT focused discussion

I am being forced into accepting a new service management system that WILL NOT work unless it uses the sa password. I never had time to work out why when setting up an evaluation but the server team tried to install it to use a fixed role I had set up incorporating db_creater and other permissions I thought it would require. which failed. I then let the server team install with the sa account but run under an account in the dbo role for its database but that failed too. Grumpily I tried to get it to run with an account in the sysadmin role but even that failed and not with useful error messages that enabled me to work out what was going on without spending more time than I had available. It will only work with the sa account and the password stored in clear text in the config file.

When I queried this and the server team talked to the vendor they got the worrying answer of 'What's the problem with that?' and then 'well we can look at scrambling the password' scrambling ffs

I know that there are ways and means to restrict access to the file but it is just another weakness in the security in my opinion

Anyway, My question is, could someone point me at some documentation that I can use to explain to the business the reason why this is a bad thing and should be a big no no. I work in a field that means that I need to take security seriously and have been struggling to make the business understand and ultimately may be out-ranked anyway but I need to try.

Community
  • 1
SQLDBAWithABeard
  • 635
  • 1
  • 6
  • 13
  • 1
    `sa` or any member of `sysadmin`, including windows logins? – Remus Rusanu Apr 03 '13 at 13:02
  • sa and sa only :-( – SQLDBAWithABeard Apr 03 '13 at 13:20
  • 1
    You need to get more information from the vendor. Specifically what things are they doing that require `sa` explicitly. – Aaron Bertrand Apr 03 '13 at 13:46
  • 11
    Often when a vendor says they need to login as sa explicitly it means they simply haven't tested their app any other way (or did once and it fell over with an error they didn't look into before deciding "we'll just stick with sa"), which is not something that would fill me with confidence. Don't take this tack directly with the vendor though, enquiring more diplomatically will get better results! – David Spillett Apr 03 '13 at 14:39
  • David has it correctly I believe from my brief dealings with the vendor – SQLDBAWithABeard Apr 04 '13 at 17:22

8 Answers8

22

It depends on your business, but the main thing in most cases is to make sure it is not seen as an IT issue. It is a security issue and while the two overlap massively business peopel are more likely to listen if you say "security" than if you are just "moaning about general IT stuff".

Do you work with any clients that have security requirements? That is a good place to start. If we ran an app with sa level access, or just app that didn't properly secure its credentials even if not using priveleged access (we strongly prefer Windows Integrated rather than stored user/pass where possible), and we were subject to a security audit, that audit would fail and we would risk losing customers and/or having to give money back as to our groups's clients (banking organisations in the case of the product I work on mostly, other parts of the group deal with the police and health authorities and so forth) security is part of our offering being fit for purpose. Business people will understand the severity of that potential threat even if they usually pay no more than lip service to IT recommendations.

Even ignoring client imposed requirements, if you strive to meet various industry standard security standards then again this sort of application authentication is going to fail you in the face of an audit as it is so far from best practise as to be something generally considered to be on the "simply shouldn't be done" list. Make clear to your business descision makers that security is an important part of the application, and the fact that this vendor does not seem to be aware of (or at least appropriately concerned about) makes you have doubts about what else they might not capable of dealing with: knowing about DB security best practise is (well, should be) part of their job and imlpementing it is not difficult.

Also point out that you (the purchaser) should be dictating reasonable security requirements to the vendor, not the other way around. It is your data, so they are not qualified to state what is considered secure enough.

David Spillett
  • 29,657
  • 3
  • 43
  • 82
  • Ha. Didnt realise enter added the comment It is safe to say that where I work, security of all types is a significant concern, consider it the same as the police if you like. However, the decision makers do not see the sa as a concern. The audit is a good place to start and I will investigate that further. – SQLDBAWithABeard Apr 03 '13 at 13:19
  • +1 I particularly liked the comment that this is a _security_ issue not an _it_ issue. – Kenneth Fisher Apr 03 '13 at 13:46
  • 2
    I'd be hesitant to buy anything from a bunch of guys who think it's OK to leave the keys to the kingdom sitting in a config file in plain text. I know it's not your call, but if you can get The Deciders to see what a terrible idea that is, and what it says about the vendor, that might help your case. – mdoyle Apr 03 '13 at 14:45
  • Mdoyle - The problem is as I learn more about it is the Deciders see things in £ signs and as this is an upgrade from an ancient version it is coming cheap. I think I am winning the battle a little thanks to the good folks here. I have delayed the testing of the web server portion of this for the present at least – SQLDBAWithABeard Apr 04 '13 at 17:24
20

No application needs to have SA access - ever. (Unless its sole purpose is database administration of some kind.)

It is a general rule to never grant more rights to any login (application- or personal-) than the business requires that login to have.

No application is completely secure. Most have some kind of SQL Injection or XSS vulnerability. If an intruder gets to execute a statement of his/her choosing and has 'SA' access, there are a lot of things that can happen to your data that could kill the business instantly. (Especially if the data needs to be trusted because it is used by law enforcement.) Ask the stake holders what they would have to do, if someone was able to deliberately change even a single record and that information was leaked.

Now, with 'SA' access, not only the database of the application could be changed but every other database on the system as well. So, ask your stakeholders what they would do if you would make a copy of ALL your databases and send it to the newspaper. Because that might happen if a disgruntled employee figures out this security hole to exist.

The vendor said they could scramble the password. That is BS. The application needs to access the password in cleartext to use it, so the key to unscramble the password would be stored unscrambled right next to it. Also, as mentioned before, the real problem is not someone finding this password; it is that people (mis)using this system through a vulnerability will get full access to all your databases without ever seeing the password.

The most likely cause for SA being required is that the application needs to interact with SQL Agent. At least that is one of the harder functions to implement right and most people just take the "use SA" route to get around it. Requiring 'SA' itself might be due to the vendor not knowing how to check for sysadmin permissions.

There are two solutions that you can try:

  1. Rename SA (a security best practice anyway) and create a new account called 'SA' with restricted rights. (Never tried this, but it should work)

  2. Don't install the software. You as a profesional cannot bear the responsibility for that action, so you should not install it. To give you a comparison, ask a plumber to run the gas line directly through the fireplace instead of around it to save some pipe/money. You might be laughing at this image, but I believe it is an appropriate comparison - This software will blow up sooner or later, probably sooner. And when it does it might take the business down too.

If this all does not stop "them" from requiring this software, the last recommendation I can give you is to run. If something happens to the data you are going to be the first to be held responsible. So, with this application in place you probably don't have any job security, so find an employer that gives you at least some.

Jon Seigel
  • 16,673
  • 6
  • 41
  • 83
Sebastian Meine
  • 8,812
  • 1
  • 25
  • 32
12

I see two lines of attacking this.

  • Compliance. Is there any mandated compliance criteria in effect in your shop? Search carefully through its wording and see if you find anything that would be incompatible with the application 'requirement'. If you find anything that prevent the sa use by an application you have a bullet proof water tight case, as the application would make your business liable etc etc.

  • User Admin Access. Make sure you present clearly the case that an application that requires sa access in effect it gives sa access to all corporate users that have admin rights on the workstations where the application is installed. There is no way to hide the sa password from local admins where the application runs, that is a fact and no amount of 'scrambling' can prevent this. There is no local root of trust that a local admin cannot get to, if he wants. Make it clear that having an application that requires sa equates to giving sa privileged to all users running the application. Explain what that means, what effectively the users can do:

    • ability to read any data in the server, not only from this application but from any other database hosted on that server
    • ability to modify any data on the server, again from any other database on the same server
    • ability to erase any trace of his actions after a modification is made
    • ability to alter any audit and history to make it appear that certain actions were done by a different user
    • ability to use the SQL Server credentials to escalate an attack on any other resource that trusts this server. This can imply any other SQL Server but other resources as well, including but not restricted to file shares, Exchanges servers etc, as the SQL Server can be used just as a stepping stone.

Make it clear to the decision makers that accepting this application implies entrusting every employee that has administrator access to workstations running the application with all the privileges mentioned above. The vendor will try to defend its stand by invoking some sort or other of 'encrypting' the sa password for the application. This does not hold water. There is no encryption scheme that can withstand an attack from an administrator. And make it clear that the amount of technical skill required to find a locally 'hidden' password is completely irrelevant. The employees are not going to do it themselves, one of them will google for it and discover the easy-to-use script that does it.

Remus Rusanu
  • 50,693
  • 3
  • 90
  • 168
7

First, the sa password stored plaintext? You should be voting with your wallet. Whoever thinks that is acceptable needs to be put out of business.

Here is an anology that might help you explain the issue: Employee Alice needs access to the first floor. Do you give her the master key to the whole building or just the key for the first floor? Answer: You give her just the keys to the first floor. Why? Because it reduces the chance of accidental or deliberate damage. If Alice can't get to the second floor server room in the first place then she will never do anything bad in there.

It is the Principle of Least Priviledge.

As to why the application needs to use the sa account, that is a question that PerfMon or Extended Events should be able to answer. Create a PerfMon trace using the T-SQL template, maybe filtered by application name.

Off the top of my head, here is another argument against using sa: Using the sa account requires the SQL Server service to be in mixed authentication mode. WIndows only authentication is better becase we can leverage all the secure features of Kerberos.

Greenstone Walker
  • 4,033
  • 1
  • 13
  • 23
4

No application should require the SA account and password to operate.

However, I have installed an IT Service Management product, and during the installation process you have the option to supply the SA account credentials to allow the installer to create the DB and attach an account to the DB for the software to use. The SA account credentials are not saved in the application or installer logs anywhere. This software also provides the option to use a pre-created database during the installation.

So I would just confirm if the SA account is required for the installation, or the operation of this IT Service Management software.

If Installation: create a temporary 'sa' account - do the install - and remove the account.

If Operation: Avoid that software like the plague. (or setup a standalone SQL server that will only house that one database.)

Brent
  • 41
  • 1
  • +1 for dedicated SQL server instance. That would be a good last resort alternative to granting sa on the real server. – Dan Aug 11 '15 at 18:27
4

From a technical perspective there's no reason that an application would need SA permissions. What probably happened is that the developers of the application probably check to see if their login has sysadmin permissions and if not, it simply throws an error message. This way they can claim that the application requires SA rights.

If you have to have this application then I'd run it on a separate instance that doesn't have anything else on it.

mrdenny
  • 26,776
  • 2
  • 40
  • 79
  • I think it probably checks if it is running as sa and then fails as it won't run under an account with sysadmin – SQLDBAWithABeard Apr 04 '13 at 17:27
  • 1
    Then it much be checking the specific userid. Odds are they developer is doing this because it works with sa and they didn't want to bother to see what permissions they actually needed, so this is the easiest way to prevent other errors. It's a totally crap approach and the vendor should be smacked around for doing it. – mrdenny Apr 05 '13 at 23:01
4

Perhaps your vendor is requesting/requiring "sa" because somewhere in their application they are using XP_CMDSHELL. (Don't get me started on the damage possible with unrestricted access to XP_CMDSHELL. Suffice it to say that this potentially opens up not just your data but the host machine and, perhaps, anything else on your network to admin-like access)

If there's a legit need, you could give restricted access via a proxy account. See, for example, BOL: http://msdn.microsoft.com/en-us/library/ms175046.aspx

TheDataBeagle
  • 41
  • 1
0

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

Ivan Stankovic
  • 637
  • 6
  • 6