Voice of the DBA

Category: Editorial

  • Has Everyone Had a Data Breach?

    Almost every time that I attend an event, I’ll end up meeting someone that has had security issues at their company. I’m always surprised how many people have had ransomware or other security problem that didn’t get well publicized. It’s not like everyone has had one, but out of 100 people, it seems there is at least one issue.

    Many of us work with third-party companies for either products or services. It’s become standard to use other firms for specific things your organization needs. However, since that’s the practice, many of those firms you partner with have their own partners. After all, they’re using other companies for specialized work just like you are.

    These third- and fourth-party relationships have changed our security and risk profiles for the worse. As the numbers of data breaches and security issues grow, it’s likely that someone in your partner network has had an issue, which might mean that you have an issue. This depends on what you have contracted with partners for, but it seems more and more often this is some sort of service provided, often with your data being shared with the partner. Which could mean your data is shared with their partners.

    An article recently noted that the number of partners are going up and many organizations are not aware of the risk this creates for them and their customers. There are more and more third- and fourth-party partners who have suffered data breaches, and if they have shared our data, we may have liability. The weakest link in a supply chain is the problem, and many of us have lengthened our supply chains quite a bit without paying attention.

    I don’t know that there are good solutions here, but I am seeing more and more companies demanding that suppliers of services prove they have strong security practices and protocols in place. It’s not perfect, but it does help us remember that security is everyone’s business, or at least everyone with whom we share our data.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

  • Nested Transactions

    One of the very common expectations from many SQL developers involves transactions. Many developers (database or application developers) think they can open a transaction, do something, open an inner transaction (nested), and then commit or rollback the inner transaction separate from the outer one.

    If you’ve worked with explicit transactions and experimented with this a bit, then you know that this doesn’t work. Recently Brent Ozar wrote a post on this as he had a client think that committing the inner transaction would release locks. It doesn’t.

    Knowing whether work gets committed or not is important to data integrity. We often need to ensure that multiple things happen or nothing happens. That’s key, and if we want to decide that thing A can happen without thing B, those are two transactions. In most cases, where we’d want the behavior I described at the top, these don’t need to be nested. They’re just two transactions.

    Understanding how data modifications work is important, especially if you work across different platforms and you need to ensure there is some level of durability. Some platforms use different locking strategies, some limit transactions even more, and digging into the details is important.

    As technical people, we know there are many ways to solve problems, and we often spend a lot of time ensuring that users of our systems have options. We would assume our users will learn and understand how the options work, which is no different that what we ought to do ourselves. Don’t assume. Ensure you know how the database will behave if you depend on it behaving a certain way.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

  • The Top SQL Server Engine Errors

    For many of us, SQL Server just works. We might get some syntax errors if we mistype things, but for the most part, SQL Server runs smoothly in many environments. However, there are some common situations that do occur regularly, and I wonder if you can guess which errors often occur?

    I saw a blog this week from the SQL Server Support group where they covered the top 25 errors that come in support calls. Their goal was to see if they could document and help people better solve problems themselves and reduce the support load.

    Can you guess what the first error was? I’m assuming these are in descending order, but that’s not clear. In any case, the top error was #18456, which I didn’t recognize at first. Reading the documentation page shows this is the “login failed for xxx” error, which is probably my most common error. Often because I can’t type a password correctly, but also because of an inability to select the right instance or user name. There are other causes, and it’s nice to see a long list of things people can check.

    The next error was 19407, which is a cluster communication error. If that’s the second most common error, then maybe clusters and AGs need a bit more resiliency or better setup guidance. Third is an OS error with NTFS, which I’ve never run into.

    If you flip through the list, I wonder how many of these errors are common for you. Do they come up often? I know I’ve seen people post on 912, which is an upgrade error and very annoying. I think some of the upgrade scripts for CUs aren’t that well written and should have better error handling inside them. That would seem like an easy one to fix and reduce call volume.

    There are plenty of network errors, including the “error occurred while establishing a connection” one. That one is usually is a typo from me or a misconfiguration of an instance after installation. Lots of other errors seem network or backup related, which may not be common, but those are errors that likely cause people to call Microsoft Support.

    Maybe the most interesting one is 9002, log out of space. While I know lots of people might not know how to manage space, I also see lots of accidental DBAs get caught here because they set up full backups and not log backups. Their databases are small, storage is cheap, and they encounter this a year or so after they’ve set things up. To me, this is really low-hanging fruit by making it really easy to have an automatic backup process added for each database. Just add tooling to help make this easier, or create a job when a new database is created. If this isn’t needed, let it be disabled, but for those that are installing SQL Server for some COTS application, make this a easy.

    A lot of these errors are ones I’d never call support for, but I can imagine others not feeling that way. Plenty of these are errors I’ve never seen, but I’m glad the documentation is more than just a description of what happens. These updated pages give some possible causes and things that the user can do. That’s something all of us would like in documentation when something goes wrong.

    Steve Jones

  • Stale Data Causes Security Issues

    Security has become better and better in many organizations. At the same time, hackers and malicious actors are doing a better and better job of finding new ways to attack systems. Some work to target specific individuals, often because of government or industrial espionage. Most of us aren’t likely to deal with those issues, unless we work with (or are) someone that is very important in a particular situation.

    Instead, many of us deal with wider spread attacks that look to exploit vulnerabilities in technology or humans at scale, hoping to find the weak links. Lots of people I know have dealt with viruses in the past that shut down systems, and more recently, had to rebuild systems crippled by ransomware.  Despite their best efforts, this often means lots of extra unexpected work, combined with the stress of falling behind on our commitments. We have plenty of other work to do.

    Windows has been vulnerable throughout its history, and it appears, lately through a data problem. There is a class of attacks that look to use approved, though old and unpatched, drivers as a vehicle for gaining a foothold inside a network. This was a problem (called the BYOVD issue) and Microsoft addressed this with a block list that was used to prevent the loading of vulnerable drivers. They updated this list through Windows Update.

    Except they didn’t. In database terms, we had an eventually consistent set of data, which was being updated at Microsoft, but not being sent to client workstations. There were over 3 years of the list not being updated, despite assurances from Microsoft that Windows 10 PCs were protected. There are instructions for manually updating your machine.

    I don’t envy this being a process I’d want to build. Getting data from security researchers or elsewhere, putting it in a database (I hope), then exporting this into a text format and getting that loaded into the Windows Update process, all while trying to ensure the process is secure along the way. That can’t be easy inside a large company like Microsoft. At the same time, not noticing this wasn’t working isn’t excusable. Likely there were issues, but my guess is someone didn’t want to admit a failure and get a bad annual review.

    Data sync issues are nothing new, and many of us struggle with these on a weekly basis. However, these are important issues. Replication can fill log files (and disks), broken ETL processes can cause execs to make poor decisions, and in the security space, not updating drivers and block lists leave us vulnerable.

    This situation isn’t excusable and Microsoft ought to be ashamed. Some of these execs ought to lose bonuses, at the very least. It’s also not excusable in our orgs. We ought to be sure we’re patching on a regular basis and minimizing the attack surface area we present. It’s the least we can do as IT professionals.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.