Voice of the DBA

Tag: administration

  • Securing All Your Connections

    I read an interesting blog from the cyber security team at Microsoft, noting you should use TLS for your SQL Server connections. I would assume most professionals know that using TLS and secure protocols across the network is important. I would also assume few of us are willing to get real certificates for all of our SQL Servers, especially those in dev and test environments.

    Setting up a certificate for a server instance isn’t hard, but it’s also not easy. It’s also something that I don’t know how easy would be to automate in many environments. I know that you can use APIs from somewhere like Let’s Encrypt, but integrating that into a server setup process would be something. What about integrating this into instances in containers? I don’t know that I think many SQL Server organizations have DNS integrated for most of their database servers, much less asking them to also get certificates set up.

    I do think that Microsoft likely has better infrastructure in place, and better staff resources, than most of us. I can see this being something needed for Azure SQL DB and Azure MI. Heck, this might be something we want for all services that we get in the cloud. Since we don’t control much of anything other than the service, and Microsoft can automate the process of generating certificates, this makes sense.

    I think this might be something that is a good idea for production systems as well, ensuring that when a DBA or system makes a privileged connection to a database server in production, the person or process is sure of which database server is at the other end of the connection. I don’t know that I’ve seen an exploit in the wild where someone impersonates a prod server and captures credentials in some man-in-the-middle attack, but I wouldn’t be surprised if it happens.

    Do you think you also ought to avoid trusting the server certificate? I don’t think this is practical in many orgs, but I’d be curious what you think today.

    Steve Jones

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

  • Actively Choosing Compatibility

    SQL Server databases have had a compatibility level for a long time. This is a setting that enables the database to process code as if it were a particular version. The levels go from 80 (SQL Server 2000) to 160 (SQL Server 2022). Each time (almost) a new version of SQL Server has been released, there has been a new compatibility level.

    However, not all versions can support all compatibility levels. For example, my SQL 2022 instance can support levels back to 100 (SQL Server 2008). If I wanted to get a database to act like a version older than 2008, for example, 2005, I would need to install a SQL Server 2012 or older instance. There is a table of engine versions and supported compatibility levels on the ALTER DATABASE Docs page.

    When you create a new database, by default, it is at the current compatibility level. However, if you upgrade a database, the level might not change. There are some limits to which versions are supported, so an upgrade might change your database.

    An administrator might choose to keep an older level for compatibility purposes. Perhaps your code has an identifier that is now a keyword. Perhaps you expect some code to behave a certain way. However, not all changes are protected by the compatibility level. Most of the time, an administrator must manually change this, which is something that can slip through the cracks. If you don’t change this right away, likely it isn’t going to change.

    There was an interesting post from Brent Ozar recently that explained a bit about compatibility levels in a SQL Server database. I wonder how many people actually actively choose a level or they just accept the default level for that instance. Brent gives some advice in the post, and his recommendations vary a bit, depending on whether you are happy with the system or not. He also recommends measuring your system and then evaluating a change. Especially if this is a database for vendor software.

    I don’t think changing or updating this setting is a priority, but I also think that being aware of when your level doesn’t match the instance and documenting this is important. At some point, through many upgrades, you might find your level isn’t supported any longer. Then your database might have immediate issues. A good monitoring system can let you know when you have mismatches that can be evaluated when there is extra time to clean up or test changes to the system.

    Each version of SQL Server adds new features, like the changes for Intelligent Query Processing. In general, we want to take advantage of these if we can. However, not all workloads respond positively, so as Brent mentions, you need to test and evaluate your workload. Hopefully, you have a clear “things are better” or “things are worse” when changing levels. When you get some queries that perform better and some worse, then you have some choices to make. Often the default is “do nothing,” which may or may not be the best decision, but the devil you know is sometimes easier to deal with than the one you don’t know.

    That’s fine but consciously make that choice. Keep an eye on your system and don’t just accept defaults, whether those are from Microsoft or the ones you’ve left after an upgrade. Actively manage your systems to get the best performance you can for clients.

    Steve Jones

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

  • Scaling Up Monitoring

    Monitoring databases is important when it’s the systems that are in production. Operations departments know that catching issues early, being proactive, and having data to troubleshoot issues make their job easier. Not having these things makes their job much more stressful.

    Most of us work with data in some way and the availability of that is important. Certainly, security, integrity, and performance matter as well, but availability is key. Many organizations don’t have any monitoring systems set up. Instead, they troubleshoot problems when someone files a ticket or calls. I’m amazed at this, though I know building and managing a monitoring system is hard and purchasing third-party products can be outside of your budget. Still, having something in place makes everyone’s job easier.

    If you decide to build a system, then you can do it in many ways. I saw a description of how Amazon built a monitoring system for their Prime Video service. This is a more complex system than many of us deal with for databases, but I did find it interesting that they chose a distributed architecture that used multiple components. It didn’t scale, so they started to move from small functions, almost like microservices, to a bit more monolithic structure.

    I am not saying that microservices or functions or serverless are bad choices. They meet certain needs, and they can work very well. Azure SQL Database Serverless can work well in some situations. However, I do think that this was a case of engineers trying to be too clever and making assumptions about production loads from PoC-type experimentation.

    I would say that far too many software engineers think that their solution will scale without actually testing it. Too often their view is if it works here, it will work there, but the history of software has shown that working on my machine doesn’t mean working on another. That’s why we use Continuous Integration: for independent validation and verification. This is also a problem when databases are involved, as the level of data used for development and testing doesn’t do a good enough job of predicting how the system works under load. We need better test data management, which is becoming a whole new category of software practices and tools.

    We should ensure we include good instrumentation in our software for monitoring purposes, but we should also ensure that we start monitoring and evaluating how our system will perform in test and development environments, as that’s the idea of shift-left. Lastly, I think monitoring in production is important, but I wouldn’t build another system. I admit I’m biased, as I work for a company selling monitoring software. However, I also think the build v buy debate doesn’t make sense here unless your staff has a lot of spare time to spend maintaining a homegrown system. I’d like to think most of them have better things to do.

    Steve Jones

     

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

  • Validating Password Expiration

    I would guess that the majority of instances I’ve had to manage in my career were those that I didn’t initially install and configure. I’ve inherited more instances than I would bother to count, and I often need to double-check what’s been done in the past. As noted in the series on new jobs from Tracy and Josephine, there are a lot of settings to check and adjust to meet your standards.

    While backups are often my first priority, security is second. I usually want to know who the sysadmins are and ensure systems are patched and configured to reduce the attack surface area. There is one other security check that I think I haven’t always been overly concerned about checking: password expiration.

    There was a post from Steve Stedman recently that mentioned the way to alter logins and ensure they have CHECK_EXPIRATION set ot on, which ensures that passwords expire and need to get changed. This is especially important for sysadmins. I try to ensure those accounts in that role are secured with AD, but there have been times when SQL accounts are used. Usually, I disable sa, but I’ve seen other accounts, especially those used by monitoring systems who seem to think sysadmin is required. It’s not.

    I don’t know that I’ve run queries to check the value in the is_expiration_checked column is appropriately set. If it’s not, then Steve’s post above will help you change those logins. That’s a handy script to have set up and use to ensure that all logins have this set. In fact, this is one of those areas where new logins could be created by junior administrators and not set the option. Perhaps this is something you want to run on a regular basis, perhaps weekly, to ensure that if any new SQL logins are created, they are done so with the password expiration set.

    Ideally, no one would ever create logins without expiration set, but sometimes things happen. I’ve seen monitoring systems set up with sysadmin privileges and passwords that never expire. A surefire way to dramatically increase the risk to your database systems. It would be better to have a known, consistent process for setting up accounts. Some companies have specific scripts, or snippets, that administrators use when tickets are filed. One customer of mine had even linked a script to a Slack command in a sysadmin channel. Only admins could use this channel, but they could use Slack to kick off scripts to create logins, add roles, and force password changes.

    No matter how you choose to handle security at a process level, it is important to include monitoring and remediation for issues. Mistakes will get made, settings altered, and exceptions approved. Sometimes we can fix things, sometimes we cannot, but knowing what our environment looks like and where we have potential issues is important not only for getting the work complete but getting the approvals to make changes that ensure better security. My recommendation is that you ensure you have a way to regularly check your systems, automatically fix issues where appropriate, and report on those that need additional approvals.

    Steve Jones