Let’s Talk About Group Managed Service Accounts

SQL Server service dialog, Log On tabWe all know the best practices for SQL Server service accounts–domain account (if you’re using Active Directory), non local admin, different one for each service (and server/instance), etc, etc. These are, of course, good best practices and they should be followed as closely as possible in Production and on servers/instances that house Production data.

A problem arises if you have more than just a couple-few servers or run some of the BI components, however. The number of service accounts involved in your SQL Server plant could be very large, necessitating an incredible amount of overhead when it comes to managing those accounts. This goes beyond simply creating and assigning them–chances are good that there are policies in place that require changing passwords. User accounts, service accounts, and other automation accounts likely all fall under this umbrella. If you’re lucky, maybe non-user accounts have a longer change interval, but it’s still something that is going to need to be done on a regular basis. In large environments, this could take an excruciatingly awful amount of time to do.

All of this is not to mention the human factor involved here. One of the recurring themes in a couple of my presentations is making an effort to automate as many things as possible to remove the human from the process. Not that we’re bad, but there are some things, especially tedious and repetitious tasks, where dumb things go wrong simply because of the nature of the work. Changing a bunch of service account passwords is definitely one of them. There used to be two types of sysadmins: those that have changed a service account’s password but forgot to update and restart the service itself, and those who will.

Enter Group Managed Service Accounts

Group Managed Service accounts (gMSAs) are a way to avoid most of the above work. They are special accounts that are created in Active Directory and can then be assigned as service accounts. They are completely managed by Active Directory, including their passwords. This means no more manual work to meet the password-changing policy–the machine takes care of that for you.

There are other security-related controls that can be gained with them, but this is the major feature.

I’ll also note that you–the DBA–are likely to need some help from your AD admin to get these set up. They’re going to need to actually create the accounts for you in the system, and there may be some changes needed to their AD configuration in order to support them. They’ll also need to have a Windows Server 2012 (or R2) domain controller in their domain, but I’d hope today that’s not going to be a hurdle to overcome.

Since I’m mostly here to talk about SQL Server, I’ll note a couple of different support situations. gMSAs are supported from SQL Server 2014 and on running on Windows Server 2012 R2 and on for everything you can do with SQL Server–standalone instances, Failover Clusters, Availability Groups. Just plain Managed Service Accounts (MSAs) can go back a little further, but they only support standalone instances of SQL Server.

From a non-SQL Server perspective, one of the major disadvantages of gMSAs is that one can’t just use them everywhere. Services have to be specifically designed to support the use of these accounts, and that’s not going to be the case everywhere.

Since this isn’t exactly a new feature, there’s plenty of documentation and blog posts out there to read about this feature and the various requirements to implement. There’s a great overview and setup blog post on MSDN here: https://blogs.msdn.microsoft.com/markweberblog/2016/05/25/group-managed-service-accounts-gmsa-and-sql-server-2016/

That post links to this old TechNet article, which still is a pretty good resource for understanding what these things are and a little more detail on what is going on in the back-end: https://technet.microsoft.com/en-us/library/hh831782.aspx

Finally, my coworker Joey has a slightly older writeup here, https://joeydantoni.com/2012/12/14/group-managed-service-accounts/, that walks through the process of setting this up. Note that some of the requirements have changed since that was written, but the general process remains the same.

gMSAs are a nice feature that aren’t too onerous to setup, but go a long way to make your life easier and your data far more secure.

PSA: SQL Server 2016 SP1 CU7 is a Security Update

This week (Thursday the 4th), SQL 2016 SP1 CU7 was released. Right, fine, nothing out of the ordinary.

Except there is something out of the ordinary here. CU7 includes the patches for SQL Server related to the Spectre and Meltdown vulnerabilities (for more info on these as they relate to SQL Server, see Joey’s post here and Allen’s post here), and as a result, it is being flagged and published as a security update from Microsoft as KB 4058561.

So What?

Since this is a security update, that means it will be pushed down by/via Windows Update like other normal security patches. 4058561 says that, “This update will be provided via Microsoft Update at a future date”, but it’s probably safe to assume that this “future date” will be next Tuesday, January 9th, as that is the normal Patch Tuesday this month.

All that said, then, if you are or support a shop that likes to test their SQL CUs before they get deployed, if there aren’t also positive controls on what Windows Update does, it will be a good idea to start testing this update now. Sure, the Venn diagram of “people who test CUs” and “people who let Windows Update go to town on their SQL Servers, whether it be right off the bat Tuesday night or on the next weekend” probably don’t overlap much, but in case you fall into that category: Heads up.

Remember that CUs include all patches since their “baseline” (in this case, SP1), so you’re not going to just be getting the most recent updates–you’re going to be getting an additional six CUs’ worth, too.

Again…

Once again, as everyone else is (or should be) saying, this is a big-deal security vulnerability, and you (all of you, yes, even YOU) really need to apply this patch. For SQL Servers, see guidance from MS here: https://support.microsoft.com/en-us/help/4073225/guidance-for-sql-server

Seriously… patch your stuff.

How can I get old SQL Server CUs?

When one maintains SQL Servers/Instances for a living, one of the things we do on a semi-regular basis (or at least should) is install major updates to said instances. But, because usually we aren’t in a position to mash the latest and greatest one out to everything we support, where to find the specific update we’re looking for isn’t immediately obvious. This is because on all of the existing–say–Cumulative Updates pages, all of the download links to a generic “latest CU” page, where that’s all you can get.

But what if I need an older one?

Behold! Via the Microsoft Update Catalog, all updates that have been released for SQL Server, [almost] ever: www.catalog.update.microsoft.com/Search.aspx?q=SQL%20Server

From this site, you’ll be able to find what you’re looking for and pull it down. Easy peasy. Use the search box at the top to get a little more focused, keeping in mind that “Service Pack” and “Cumulative Update” are spelled out. If you’re looking for a specific update, it’s probably best to use the KB number to search for it (“KB4024305” for SQL 2016 SP1 CU4, for example).

The main page of the site is pretty awesome in a 1997 sort of way (it even looks like it’s from 1997). When you go to www.catalog.update.microsoft.com, basically all you get is a little welcome message, some feedback and a FAQ link, and a search box. That’s it. It’s great.

This isn’t just for SQL Server, either. Theoretically all updates for all mostly-recent MS products are available–for example, Windows XP SP3 is available for download, were you to need that. If you do, we need to have a talk. It also looks like you can’t get SQL 2000 SPs anymore, but all of SQL Server 2005’s are.

Anyway, this is a good Site/URL to keep handy, especially for times like when you’re just about to download SQL 2016 SP1 CU4, and “ah crap, CU5 just got released an hour ago and all of the download links changed.”

Hey, you! Don’t Forget to Enable DB Mail in Agent

Raise your hand if you’ve been there: You set up a new SQL Server instance, configure Database Mail (test it), and then set up a nice Agent job to back up your databases. You configure it to send mail on job completion (so you can keep an eye on it not matter what), but it’s not sending mail. You test DB mail again, and it’s working. What gives?

This is kind of a gimme, but a few weeks ago I configured a new maintenance job (NOT a Maintenance Plan 😉 ) on a new-ish non-production server that didn’t have any other jobs on it yet. When it wasn’t sending mail, I stood around for a lot longer than I’d like to admit before I figured out what was going on.

The kicker is that you have to enable the use of database mail within the SQL Server Agent–this isn’t on by default.

As with most things in SQL Server, there’s a couple ways to turn this on. First is the GUI. The Alert System page of the SQL Agent’s properties dialog is shown here, and you can see right on the top where the main “Enable” checkbox is, along with dropdowns for the DB Mail settings you want to use. Flip that on, pick your desired mail settings (probably only have one setup), restart the Agent service, and your agent jobs will start sending mail as-expected.

Agent Properties dialog showing Mail Settings

There’s also the T-SQL route, which is useful for adding this configuration to a general “initial” script (such as ours: https://github.com/DC-AC/SQL2016_Scripted_Install) so you don’t have to worry about this on new instances that you install/setup. It’s a quick SP call to enable mail:

EXEC msdb.dbo.sp_set_sqlagent_properties @databasemail_profile=N’Main Profile’

Assign the mail profile you want to use, and go. The UI by default and greyed out (at least on a few 2016 instances that I’ve checked recently) checks the “Save copies of the sent messages in the Sent Items Folder” option. This option can be driven with the email_save_in_sent_folder parameter on the proc. Set it to 1 to turn on that option. True story: I have no idea where that mail gets saved on a SQL Server; I assume it goes to the “Sent Items” folder in the mailbox the profile is configured to use, but I’ve never actually configured this with a mailbox that I have access to to see.

This T-SQL step assumes SQL Server on Windows. If you’re doing this on Linux…well, it’s different. I’m not going to reproduce that work here, because it may change since SQL 2017 is still in RC at this point. So, if you’re doing this on Linux, check out the official docs for that process.

Moral of the story here: Don’t be a dumbass like me; turn on DB Mail in the Agent!

Using Excel and Get Data to Find Fixes in SQL Server CUs

Lately, for whatever reason, we’ve had clients running into a small rash of bugs or bug-like behavior in SQL Server; soGetData Buttonsme in the Engine, some in SSRS (the SSRS ones have been fun). In one case, it occurred a day or two after SQL Server 2016 SP1 CU3 was released, so we (I was talking to Joey about it at the time) had a list of fixes to go through.

 

This is fine and all, but when one is looking for a fix for a specific behavior (“I’ve had this bug all summer, so I want to look through every CU release to see if it’s in there”), it’s a bit of a pain to go through the whole list just scanning for the, say, Reporting Services fixes. It’s even worse if the instance is behind and you need to look through multiple CUs for something. Another scenario is if you are just reviewing a newly-released CU and really only care about fixes that pertain to the engine…you get the idea.

These lists can get long

Business Intelligence to the Rescue!

Fortunately, there are some tools built right into Excel that make this a whole lot easier than scrolling through the list in your browser. Armed with nothing more than the URL to the CU’s KB article and Excel 2016 (or a few older versions) quick work can be made of generating custom filters for this data.

Here are the steps:

In Excel 2016, click on the Data tab of the ribbon. This is where the artist formerly known as “Power Query” lives, now referred to as “Get & Transform.”

Starting with the New Query button, navigate down through the menu to From Other Sources and then From Web:

New Query | From Other Source | From Web

 

This brings up a simple little dialog that asks for a URL. Paste in the URL for the CU page you’re interested in; here, I’m using SQL 2016 SP1 CU3’s URL: https://support.microsoft.com/en-us/help/4019916/cumulative-update-3-for-sql-server-2016-sp1

Clicking OK brings up the next dialog, a security-related dialog that allows you to provide any credentials that may be needed to access the material. Of course, in this case, no specific credentials are needed, as it is a public web page. Leaving Anonymous selected here is the way to go.

Web Page Security

Clicking Connect will bring up the real meat of Power Query Get Data, where we will choose what data we want to import, and optionally do some ETL-like transformations to it.

Whenever pulling in data from a web page/table for the first time, there is a bit of experimentation that needs to happen. For example, when the “Navigator” dialog opens for the first time, there’s a big list of Tables from the web page, and no data displayed:

Select Table to load data from

What has to happen, is you need to find which of those tables contains the data on the web page you’re interested in. In our case, we’re interested in Table 0, where we can see the data we’re looking for; mainly the Fix area column:

Populated Table 0

Quick note: The reason for so many tables of other data on this page is that down towards the bottom of the page, under the “Cumulative update package file information” link/collapsed menu are a bunch of tables that contain a bunch of information about all of the files that are modified by fixes in this CU. All of those tables are available here, too.

Once the table you’re interested in is selected, we can move on. The next step could be clicking the Edit button, where you’d be able to do all kinds of transformations to the data in this table… here, we don’t need to do that, so can skip that part and go straight to loading the data.

As we’re only looking to read through this data on its own (as opposed to loading it into a Power Pivot data model), we can just click the Load button.

The end result will be a table of data in Excel that contains all the fixes in the CU:

Populated Fixes in Excel table

The best part about this, and the whole reason we’re here, is Excel’s “Auto Filter” feature works on this table (and it is already activated, even). Clicking on the arrowhead in the “Fix area” column yields this familiar pop-up menu, where all manner of sorting and filtering can be done.

Excel Auto Filter dialog

Simply check the area of the product you’re interested in from the list, and you’ll be presented with a nice short list of fixes to look through.

Fix list filtered to Heckaton

Awesome!

Re-use

But, let’s say you’ve gone through this, and you’re thinking “that was kind of a pain, and won’t really save any time for all the more often that page needs looked at.” That’s possibly a fair assessment. Since all of these CU pages are identical (for now), the extract logic stays the same, with the only thing needing to change being the source URL. Once you’ve set up this workbook once, you can save the file and modify the URL it pulls its data from when the next CU comes out, but the amount of clicking required to do that is about the same as it takes to set this up the first time, therefore I’m not sure how helpful that would be.

Probably the best thing to do is to save this file off after you’ve created it and reference it as-needed, clicking the Refresh All button on the Data tab when you open this to make sure you have current data.