Voice of the DBA

Category: Editorial

  • Least Privilege

    I assume that most of you know about the principle of least privilege. If not, please read this short blog from Brian Kelley and make sure you understand how you should approach security. In the modern world, we also ought to adapt our systems for the zero trust model, which includes the least privilege principle.

    However, I wonder how many of your organizations really follow these security guidelines internally. Are you strict about adding limited access and removing it when people change jobs/roles? If you use Windows Auth (or Entra), are your admins doing that or just adding in new roles? Do you scope down database access roles in granular ways or just stick with 1-2 roles for the most common things people do?

    Maybe more importantly, do you use roles or are these systems that still have explicit grants for users?

    Microsoft had a major hack recently from a test account that had administrative privileges. While there certainly might be a need for a test account to have privileged access, I’d hope that any test account created had a limited lifetime. I’ve created privileged database access accounts for vendors, but usually set a reminder to myself to disable the account after xx days. When I got smarter, I wrote a one-time job to do that and scheduled it. These days, I’d also file a ticket for my team noting that this needs disabling as well.

    Humans get lazy and often don’t think about the future. If you’ve never had an issue with a test account, why think something might happen? Why spend the time writing a note or a job when surely you’ll remember or deal with it later? Maybe more common, why disable a login when the user might need access longer? We don’t want to deal with another phone call and enabling the account. That’s an interruption to our work week.

    What has been humorous to me is that I’ve seen quite a few people who are very security conscious get annoyed when some automated system or process disables their account and forces them to make a call.

    It is annoying. However, these little things, the details, the adherence to good practices are what help ensure we have better security. When we take shortcuts (like not enabling MFA), when we skip steps, when we do small favors for others, we’re increasing risk. Most of the time that’s fine.

    Once in awhile it really comes back to cause problems. I’m not sure the savings are worth it.

    Steve Jones

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

    Note, podcasts are only available for a limited time online.

  • The Stretch Database Retirement

    Stretch Database is finally going away. It is being retired. It was deprecated on Nov 16, 2022, from SQL Server 2022. Effective Jul 9, 2024, the supporting Azure service is retired. I saw this in an announcement on Jul 3, though I hope anyone using this service has been seeing lots of reminders over the last couple of years. I know I’m getting MySQL retirement notices for one of my services and need to migrate some workloads this month.

    If you tried this service, you might have realized that the pricing didn’t make sense for most of us. If you hadn’t tried it, it worked by moving some of the data in your tables into Azure, where it could be queried if needed. It was an interesting idea, though most of us would have wanted this to work between two SQL Server instances, not between SQL Server on-premises and Azure.

    In any case, if you’re on an older version of SQL Server, the recommendation is that you bring your data back on-premises. If you have SQL Server 2022, they recommend CETaS (Create External Table as Select), which lets you query data in Azure storage. This lets you put some data in text formats and query it as needed, reducing the use of expensive relational storage disks. Parquet is the recommended format here. There’s also a weird mention of Fabric in the announcement, which doesn’t seem to fit with the objective of the rest of article.

    I haven’t worked with this enough to know how well this performs or what patterns might fit here. I do know that reducing the queries from clients, especially SELECT * and unbounded queries across all your data helps. If your clients always want to query all data in a table, then nothing works well. Building systems that query hot data by default, and filter away from warm/cold data is always helpful. Having some good indexing is also important.

    I don’t know of anyone that uses Stretch Database, so I’m not sure how many people are affected here, but I hope they knew about this before Jul 9.

    Steve Jones

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

    Note, podcasts are only available for a limited time online.

  • An Upgrade Slog

    I saw a blog post from Randolph West recently that asked How do you restore a SQL Server 2000 database in the year 2024? It’s a bit of a process, involving an intermediate version and two restores. Randolph also points out the need to run DBCC after the first restore, which is a good idea. I wonder how many people would take the time to do this, or even think about it as an upgrade step?

    This was interesting to read as I had a customer ask me about doing this a few months back. They were trying to clean up their database estate and modernize some of their older systems. This was becoming a big project for them, as they had several pre-2017 systems, none of which were in support. Auditors, regulatory authorities, and even business partners see this as a large security risk and get concerned if you’re running older software.

    I’ve felt that in most cases, I ought to be able to run a database server for close to a decade. I certainly need to patch it with CUs in that time, but the support lifecycle says that you get mainstream support for 5 years and then extended support (paid) for 5 more. That extended cycle also includes security patches, so ten years seems reasonable.

    As a side note, the final support lifecycle for 2014 ends on 9 Jul 2024. That’s a decade if you upgraded in the first year of release.

    However, many of us have multiple instances, and upgrading those can be a chore. Perhaps you trust that nothing breaks, but I would say for many larger organizations, upgrades are a constant fact of life, and it is important to probably start testing upgrades at five years, knowing it might take 1-2 years to upgrade all instances of a given version. That’s if you don’t find issues in testing. If you test a 2017->2022 upgrade now and find issues, you might spend time mitigating these, or maybe wait for SQL Server 2025 (my guess) and hope you don’t have the same issues. There are also the challenges of in-place vs. side-by-side upgrades, and you might choose one in testing, but decide to change for the final upgrade for various reasons. All those things can cause delays.

    I still find myself a little nervous about the “evergreen” versions of SQL Server, where Microsoft patches them as needed. I know they try hard not to break any backward compatibility, but if they do, then you’re stuck. I prefer to schedule my upgrades and make them a normal part of the DBA job. That being said, don’t drag them out for years and years. If you still have SQL Server 2012 or older versions, you’re doing something wrong.

    Steve Jones

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

    Note, podcasts are only available for a limited time online.

  • Protecting Schools

    One very common targets for hackers are schools and universities. The latter may have plenty of resources for staff, but often school systems don’t have that same type of budget. In many ways, it’s sad that hackers would target schools that struggle to educate and help others. The staff often deals with low pay and high stress already, and losing access to computer systems adds to an already tough job.

    There was an interview this week with the director of technology at one district. Johnathan Kim works for the Woodland Hills school district and is a former staffer at the Navy Cyber Defense Operations Command. That’s the type of training that I think few school district employees have. I’ve known a few people who manage technology inside schools, and while they are often smart, capable people, they aren’t security professionals.

    The interview talks about a few of the changes that Mr. Kim has made, such as enabling two-factor authentication (2FA) and removing local admin rights for many teachers. I know these are the types of rules that frustrate many workers who use computers. In fact, I ran into someone who rarely upgrades software on their development machine because so many applications require administrative rights and they don’t want to bother opening tickets more than a few times a year.

    Two-factor authentication can be a pain, and I know I get confused sometimes as I have both 2FA and MFA with different processes for different systems. It’s good in that a few times in the last year I’ve caught a hacking attempt, but it’s also a pain to deal with when I’m doing something simple. I can see why people don’t like it when they don’t understand the challenges of securing systems. Every time I find myself frustrated, I stop and remember the problems others have had, especially those that have dealt with ransomware in their organizations.

    A good point in the interview is that education can help smooth the way for security practices that feel unnecessary or disruptive. It’s good to remind ourselves why we want the least privileges needed assigned to others, and why those of us with privileged access need a second account for that access. We also ought to come up with a good story to educate others when they complain, perhaps using a story of a breach or loss to help remind others that our systems are constantly under attack.

    Steve Jones