Voice of the DBA

Category: Editorial

  • Delaying Patches is Problematic

    I was listening to a DevOps podcast from Josh Corman, of Rugged Software. Rugged Software aims to improve security by asking developers and sysadmins to adhere to their manifesto, which recognizes both the importance of software in the modern world, as well as the problems associated by not properly securing and patching software. These are duties that both developers and administrators should perform in their respective roles.

    The goal of the Rugged Software is to promote best practices and constant vigilence to create and deploy secure code, and ensure that software installations remain that way. This isn’t a security state, but rather a process. A way to approach software development, deployment, administration, and maintenance to improve the security of our computer systems. This maps nicely to a DevOps approach to software development, with a focus on security. The ideas here include not just patching, but fixing underlying issues, learning from mistakes and from other organizations, improving the skills of their developers and administrators. This is a holistic approach to ensuring security.

    This not only includes the software we write, but also the software products, platforms, libraries, etc. that we use. For many of us, this means the Windows host OS, the networking software and connections, http servers, vendor .NET libraries, and more. This also includes database software of all sorts, whether you use an RDBMS like SQL Server, perhaps a search service like ElasticSearch, a caching database like Redis, or some other NoSQL data store.

    The idea is that we not only code securely, but also make sure we patch our software. That’s a problem in many cases. In fact, MongoDB has had a security issue for the past few years that was well publicized but still affects many installations. Far too few people have patched their MongoDB databases, and it’s a problem. Data is exposed to anyone on the Internet, and it’s likely someone is taking advantage of this to access the bits. In fact, many hackers exploit flaws in software for which patches are often available. The updates just haven’t been deployed.

    Patching software is a challenge, and it seems that there is no shortage of ways in which companies deploy updates. Microsoft is pushing automatic patches. Some applications make these optional. Some apply changes in the background, some require downloads. If you use a service, then patches are seamless, but the changes may break functionality or force a new workflow on the user.

    Ultimately I think that we, as an industry, need to mature with the ways in which we patch software. Customers and clients should demand better, and the impact of applying patches should go down. Quality should go up, and while that has happened with some companies, I think we need better patterns and practices that are taught and adopted by more developers. Perhaps more people sharing their techniques, templates, and practices will help others become better at building and managing our software updates.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 5.5MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • Writing the Correct Query is Important

    There’s a saying in the data world: garbage in, garbage out. We use that when we can’t get good information from our database because the data we’ve stored isn’t as useful as we would like. That’s a problem, and it’s one reason why data professionals want to spend time thinking about the data we need to collect and how to store it. We want to be sure that we’ve at least made an effort to collect useful data that someone will use.

    We sometimes have the data we need, but still struggle to use it effectively. I think this is an area where machine learning and similar technologies may help in the future, but there is a lot of work to be done to allow most of us to take advantage of those tools. In the meantime, many of us make do with basic T-SQL to perform data analysis, generate reports, and provide the answers to questions. When we do so, it’s important that our queries actually work correctly to answer the questions we need.

    I don’t want this to be a political discussion, and I would appreciate that any comments be limited to the technical subject. I ran across a piece about a failure of the US government in determining the status of people being checked for immigration status. The interesting quote in this article was “… officials blamed computer code for the problem.” Leaving aside the implications in this case, the idea that computer code, likely some sort of query code, is not working as expected, querying the correct data, or isn’t being used properly is disturbing.

    I’ve run across quite a few stories like this from various consultants that were called in to help organizations, only to find out the queries that had been used for long periods of time were incorrect. They didn’t filter appropriately, didn’t convert or aggregate data as intended, or didn’t even query the correct data.

    We use databases and queries extensively in today’s world, and the growth is only going to increase. As much as I like the idea of DevOps and more frequent deployments, I also want higher quality for our software. This means that we need to ensure that our queries actually work as intended against databases. Code reviews, independent checks, using known data sets that evolve and include edge cases of data are all ways we can work to ensure we are actually writing the correct queries for our data. Above all, we need to be sure we are using test of some sort, preferably unit tests, that ensure the queries are actually the ones we want.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.8MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • Securing Your Instances

    I wrote a post about finding the port number of a SQL instance using PowerShell. Almost immediately I was taken to task by someone that noted port scanners can easily find SQL ports, so it’s silly to move off 1433. Just use it because applications expect it. I can see that, and changing ports doesn’t provide much security, but it does provide some obscurity, which may or may not be helpful. Certainly this also creates administrative and support burdens for a system. If you want other opinions, there’s a Q&A on Stack Exchange for this topic as well.

    Tom LaRock wote a post that this can be a way to obfuscate your database, prevent simple default connections, and potentially detect security issues before they become a problem. I tend to learn towards this approach as well, because these small changes can potentially provide a little protection. A port scan is quick, but firewalls are getting better at detecting these. Certainly criminals get smarter, but changing a port number isn’t intended to stop everyone. If it stops a few, then that’s fine.

    Security comes about because of layers, and limitations, and will never be perfect. There will always be ways that someone can get around security, but the more layers, the more obstacles you place in their way, the fewer people that will overcome all of them. I’d also note that plenty of attacks come from vandals. People that are just bored and looking for easy ways to get into a system. Most of these people are using pre-written scripts and programs that try out defaults. These aren’t concerted, directed attacks. They’re attacks from boredom.

    I’m curious today how many of you think a little bit of security from obfuscating ports is a good idea. I wouldn’t recommend the SQL Browser be open, but that legitimate connections know about the port needed, and use that in their connection strings. I think it helps, and if this limits some attacks, especially inside-the-firewall virus attacks from trusted machines, I think it’s worth doing.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.4MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • Backup Data Security

    It seems there is no end to the insecure ways in which people manage data. I haven’t seen this one before, but I’m sure it’s happened. In fact, I bet it’s happening right now in more than one company. A company was using rsync to keep data files copied between two insecure servers. Insecure because of a lack of username and password on the systems. In this case, the problem was a subcontractor that dealt with confidential US military personnel data.

    I appreciate that many of you are talented scripters that solve problems and build great solutions. I wonder how many of you actually think about security and the potential implications of small mistakes in configuration that others might make. When you build that PoSh script to copy backup files, are you ensuring the transfer takes place in a secure manner? Do you assume that because you use an IP address or server with no DNS entries to receive data that no one else can find it?

    All too often I find that sysadmins and developers make assumptions about the security of their process. They think because discovering the process or information would be hard for them or they wouldn’t bother, the data will be secure. And we find that time and time again that the ways in which we build systems without considering security aren’t secure. Someone will find a way to access the data, often with a fairly simple technique. Steal a laptop that’s unencrypted. Get a user to click on a link that installs keystroke logger or uses phishing to gain credentials. Scan a server for known software running with un-patched vulnerabilities or default accounts. I can’t tell you how many times I’ve logged into Oracle database servers with “System” and “Manager” in various organizations.

    Certainly our software platforms haven’t been well designed with security in mind. All sorts of expected, happy path behaviors are assumed by software developers, many of which are susceptible to attacks. While modern platforms are better designed and patches are becoming widely available, far too few companies apply these patches and consider security in the software they build on top of the OSes, databases, and other frameworks used for software. As an industry, we are far too guilty of granting more security than needed, opening more ports than necessary, re-using accounts too often, and assuming that our network paths are secure.

    Good security comes from having layers that don’t open our systems to a single mis-configuration or simple attack. Disks should be encrypted, minimal privileges granted, accounts not re-used across systems, and network communications, even for simple copies, secured. I know this can slow some development and be painful to implement, but as we become used to using secure credentials and techniques in all of our work, the complexity will fade into the background, and it will be as easy to deploy a secure system as an insecure one.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 4.2MB) podcast or subscribe to the feed at iTunes and Libsyn.