Voice of the DBA

Tag: security

  • Why Use the Principle of Least Privilege?

    This SQL Injection could be a problem in data entry

    SQL Injection is not the fault of the SQL Server. Brian Kelley pointed that out, and reminded me that SQL Injection isn’t an case of malformed SQL. It’s legitimate code, including SQL commands that we might use from any query connection, especially an administrative one. We regularly issue update and delete commands from our applications, and SQL Injection takes advantage of this to issue an update the we might not be expecting.

    Would you expect this input handwritten injection from an application? Or this table guessing attempt? You wouldn’t, but they can come through data entry in your application if the input isn’t well sanitized. Someone setting all your prices to $0.01 or all of your customers to “W3 0wnz U!” isn’t what you want to happen. You can’t necessarily prevent all of these patterns  or check for every permutation, but you can prevent things like ‘shutdown’ or ‘drop table’ from being run by your application. Even adding a new user to the database system isn’t something I would want to allow.

    Education is the key here. As Andy Leonard (blog | @AndyLeonard) would say, design patterns are important. When developers have an understanding of the issue, many of these things will be avoided. Having standard ways to begin building an application, checking for bad input, and setting up database users and permissions easily, should make this easy for anyone that wants to code against a database. We still have work to do here to build better frameworks, and ORM tools that require elevated permissions to the database are not the answer. They might become the answer, but they aren’t a better solution right now.

    Grant Fritchey wrote a nice piece about developers and DBAS, noting the need that we both have the same goals, but need to learn to communicate better.  This is one area where we ought to make an effort to communicate better, pass along education about security issues, and work to make life easier for developers to work with a database.

    That also means teaching them to work with the minimum privileges needed in order to make an application work, just in case someone plans on submitting some input you didn’t expect.

    Steve Jones

    The Voice of the DBA Podcasts

  • SQL Injection Everywhere

    I hope no one SQL Injects my washer

    I was doing the laundry the other day and thinking about SQL Injection. I have this fancy front load model that lets me load fabric softener and bleach into containers for release later, and it occurred to me that if my washing machine were connected to the Internet with some API, it’s possible someone could SQL Inject or buffer overrun a string that might release a stream of bleach into my colors.

    Not terribly dangerous, but it could be annoying, and it is exactly the type of hack some bored teenager would come up with. Then I started thinking about what else they might do. I wrote about the possibilities with cars recently, but what else could a hacker do in a connected world. What if someone could ignite my oven? Likely it wouldn’t do much more than cost me money. Turning up my fridge might make a mess of food, but not dangerous. However what if someone could turn off the lights when they saw you start running down the stairs?  That could be dangerous.

    What if they could remotely enable your sprinklers while you were at work. In some places that could result in a fine. Allowing that to happen a few times might get you arrested. Locking or unlocking your car doors (already a remote possibility) could endanger you. I’m sure there are more malicious possibilities I haven’t thought of, and as we move to a more connected world, I worry we will discover them only when some crime has been committed.

    I like the convenience of adding digital controls and remotes to more parts of our lives, but I do worry that we are doing so in a way that ignores security. Linking the convenience items of our lives to remote digital controls can be dangerous enough. Adding in more essential items, like heating, engines, etc., to the same control bus could be fatal.

    SQL Injection will likely be around for a long time, and it will get used in many new ways as more and more aspects of our lives are digitized. All developers should be aware of how an injection attack occurs, and code to be sure that we don’t allow any un-sanitized input into any of our databases, and that we also require separate authentication for the parts of a system that need more security.

    Steve Jones

    The Voice of the DBA Podcasts

  • Am I a sysadmin? (or other SQL Server role)

    How do you check if you are a sysadmin? It’s fairly easy to do in Management Studio. You can go to Security \ Server Roles \ Sysadmin, as shown here:

    sysadmin1

    You right click sysadmin and click properties to get a list of sysadmins. You can do this for any role, and that’s the easy way if you want to verify permissions.

    sysadmin2

    What if you have an open connection to the server, say in a Query window or Powershell session and want to verify your role. There’s a function to help you: Is_SrvrRoleMember().

    If you execute something like this: 

    SELECT IS_SRVROLEMEMBER('sysadmin');

    You’ll get a one back if you are a member of that role, and a 0 otherwise. That will allow you to easily determine your current permissions, or check permissions programatically and continue on with your work depending on the results.

  • Brian Kelley on Security at SQL Server Connections

    Random notes from Brian Kelley’s security talk. Brian spent a number of years working a a security guy for a bank in the Windows and infrastructure teams.

    Networking, the UDP port is 1434. This is the listener and browser for SQL.

    Default Tcp1433 is the default port for SQL. What about TCP 2433? In SQL Server 2000, the “hide my server” button moved the listener to 2433.

    If you are a worthwhile target, a hacker might take weeks to dig through the access points for your servers.

    Those hallways conversations matter. You never know when someone is actively listening to get information about your internal systems. Tom Clancy is a perfect example. “The Hunt for Red October” contained a lot of classified information about submarines, Mr. Clancy was questioned by the CIA to determine how he learned things. He did a lot of research, listened a lot around personnel and put various pieces of information together to understand how submarines work,

    These are hacking tools and you should use them with caution and with permission in your organization. use them carefully.
    Nmap is a port scanner.
    Quest’s Discovery Wizard – will find sql servers
    SQL Ping – similar to the Discovery Wizard
    Perl, Python, PowerShell scripting
    Nessus and other scanners

    What do you check? Blank sa or simple sa password. Using “sa”, “dba”, “password” or anything like this is a problem, once someone can get into one server, they often jump to another one,

    What was the last SQL server vulnerability? I didn’t remember, but Brian told us it was MS09-004, which affected SQL Server 2000 SP4 and SQL Server 2005 SP2. It allowed access through a replication extended stored procedure.

    People hop from server to server, so it’s a good reason to have separate service accounts for each instance.

    The standard hacker methodology is to find a weak point, attach, and then expand your attack. Move from machine to machine, or grow to more privileged accounts. This is why you need to provide a series of barriers, enforce auditing, and be diligent on vulnerabilities.

    Third party apps are a problem, often with weak passwords, sometimes stored on the file system, if someone can get that file, they can attack your sql server.

    Lots of scary stuff. It paints a picture that makes it seem like you cannot protect your serveres. After 45 minutes of scary ways to attack, Brian goes into a series of ways to protect your servers. Firewall your servers at the network layer, and remember to cover domain controllers, backup servers, dns servers, and other servers that your sql server might talk to.

    A intrusion detection system behind your firewall is a good idea.

    Most issues come from user workstations. Limit the accesss their systems have to the server, remove netbios, file shares, etc.

    Pen test your systems. Run real world tests. Watch out for maintenance crews, cleaning crews, weekend open doors, etc. Watch out for backup tapes. They are a source of vulnerability. Data center tours are an issue, if you have a large data center.

    Watch out for open sessions, remaining logged in, your password is in memory and if someone can get to the server, they can get logged in users’ passwords.

    SQL Server and Windows 2008 are much more secure. Many of the known vulnerabilities from the past will not work. A good reason to upgrade past 2003/2005.

    Overall i think that a good firewall protects most of your systems, but there is s lot of education needed among both administrators and non IT people to keep secrets secret, be aware of conversations away from your office, and dont take shortcuts, most security isn’t hard. It might be a pain, but often putting up with a little inconvenience can greatly increase your security.