Guidance on Moving Off of SQL Server 2008 and 2008 R2

July 9, 2019 will be here soon. With it comes the end of support, including security updates for SQL Server 2008 and SQL Server 2008 R2 unless you either migrate to Azure or enter into an agreement program with Microsoft. I know quite a few folks are facing this situation, so I wrote a guide covering why to migrate (other than regulatory) as well as what to do if you can’t, over at Simple Talk: The End of SQL Server 2008 and 2008 R2 Extended Support.

Amazon’s Concept of Ownership and Technical Debt

I’m reading Think Like Amazon: 50 1/2 Ideas to Become a Digital Leader. In talking about Amazon’s pursuit of a second headquarters, John Rossman wrote the following regarding ownership:

Amazon’s second leadership principle is “Ownership,” by which leaders at Amazon strive to never sacrifice long-term value for short-term results.

Where I think this is appropriate is the issue of technical debt. Technical debt is when we choose a less efficient approach for expediency or where we have a situation where some aspect of our system needs an update. This can occur, for instance, as a particular software product we depend on is about to move into Extended Support or move out of support altogether.

Too often I’ve heard there is too much focus on features and new functionality. However, when this selection is done at the expense of paying down technical debt, we are sacrificing long-term value for short-term results. Technical debt carries with it the same concept as monetary debt. There’s an interest rate for technical debt. It may be in how long it takes folks to do some task. It could be in the additional cost to support a product. It could be that the organization is less responsive to change because the technical debt becomes a roadblock for moving forward. As a result, teams start using workarounds just to move forward, which incurs more technical debt. At some point, we have to address that technical debt to reduce what we’re paying in interest.

As an architect, I’m always going to push for this concept of ownership. We can look at the success of organizations like Amazon, Toyota, etc. which take ownership seriously. That track record is my justification for focusing on long-term value.

#tsql2sday – A Letter to My 20 Year-Old Self

Self,

I am writing this to you in my mid 40s. I know when I was 20, I wasn’t thinking about 30, much less 40. Here’s some advice I’d give you to learn from.

You’re going to have some doors close that you think should be open. That’s okay. When those doors close, others will open. You will be in ministry, but not full time. Again, this is okay. You will still be in IT. This is how you will be able to do what you do in ministry. Remember, we don’t live to work. We work to live.

Keep being eager to learn. At 45 you are not focused in any one technology area. You often joke that you haven’t been able to figure out what you want to be when you grow up. However, your myriad of skill sets is what allows you to do what you do. As an architect, being well-rounded is key. That’s true of IT in general.

Make peace with leaving the military. It’s not your path. Yes, you will always have a longing to be back. It’s one of those doors that will close. Take what you have learned from your time on Active Duty and apply them in the civilian world. It’s not good to live in the past. Rather, move forward into the challenges of the present for a more exciting future.

Work on your soft skills. You can’t fall back on the excuse of being an extreme introvert. You’re also going to find plenty of validation supporting you being the way you are. However, that doesn’t mean the people you need to influence are going to see your viewpoint unless you can have empathy enough to understand theirs.

Learn to balance your time between work and life outside of work. Work will always ask for more and more and more. You are going to be forced into the position where you will be Choosing to Cheat either work or family. Choose to be faithful to your family first.

That’s a good enough set of action items. Carry them out. Enjoy the journey as you do. And don’t forget to celebrate the wins, especially the wins of people around you.

Basic SQL Server Configuration Help for Involuntary DBAs

After my presentation at the Techno Security and Digital Forensics conference, I had a information security professional stop by to ask a few questions. He’s in the position where he supports other clients since he works in a third-party security operations center (SOC). The reason most of these clients pay for a SOC instead of developing one of their own is cost. Since they don’t have the money to splurge on a lot of IT positions, another one that’s usually missing is the DBA.

Often times, as a SOC provider, when they interact with clients they can tell fairly quickly that the SQL Servers aren’t configured well. However, they don’t have the knowledge to go in and help their clients in a quick and easy way. He asked for advice. I pointed him to something that we have in our community: sp_Blitz. It’s part of the First Responder Toolkit from Brent Ozar.

Why did I recommend that particular tool? There are several reasons:

1. It’s designed to provide a quick health check of your SQL Server.
2. It’s a free tool (yes, you have to register), meaning budget isn’t an issue.
3. The community has worked on and contributed to it.
4. It provides explanations and recommendations on how to fix what’s wrong.

For someone such as an involuntary DBA or a consultant trying to assist a client when that’s not your primary skill set, it lets you make solid recommendations immediately that will improve the SQL Server setup. And it’s not hard to setup and run:

If you haven’t looked at this tool before, grab it, put into a non-prod environment, and see if it can help you.

Mitre’s ATT&CK Security Framework

Mitre’s ATT&CK security framework was mentioned often at the Techno Security and Digital Forensics Conference. I admit that I’m not well-versed on it, yet. However, its purpose makes sense. It’s a knowledge base for Adversarial Tactics, Techniques, and Common Knowledge, which is what the acronym ATT&CK stands for. Mitre created a short video to explain about ATT&CK and why it was created:

An example of how ATT&CK is a common body of knowledge which folks are striving to keep up-to-date is with respect to identified threat groups. As of this post there is information available about 86 groups, mainly nation state actors.

One of the things I try to do in my security presentations is help folks stop thinking in just what they’re good at. For instance, in my How I Would Hack SQL Server, I point out that as an attacker, going directly against SQL Server is an option of last resort. It’s much easier to find the data I care about on a file share, an Excel spreadsheet, or some other less secure spot. Compromising accounts and then using those accounts is the easier and safer road to success. What ATT&CK details is what attackers do. Therefore, if you’re in charge of security systems or applications, looking over the ATT&CK framework will help you look at your systems more as an attacker would.

Sysinternals – A Swiss Army Knife for IT Pros

I’m at the Techno Security and Digital Forensics conference in Myrtle Beach again this year. I sat in on a presentation about performing malware analysis. The analyst began with using two popular Microsoft tools: Dependency Walker and Process Explorer. He used Dependency Walker to do a quick, static analysis of the malware file, just to see what .DLLs it used. As malware continues to become more and more sophisticated, this type of analysis is limited. We see a lot of noise. However, by watching the behavior in a sandboxed, isolated environment, we can see what a malware does. With the right set of tools, we can even fool malware into thinking its properly online.

Process Explorer is the more interesting tool here because it allows us to see processes in real time. We can see the handles a process has open. We can also examine any built-in strings that could reveal information about what the malware connects to, maybe who the author was, etc. But Process Explorer’s primary reason for existence wasn’t to help with malware analysis. It, like most of the rest of the Sysinternals suite, is designed to by a toolset to help administrators troubleshoot issues on their systems. I have Sysinternals tools available whenever I’m looking at a system.

The two tools I use the most are Process Explorer and Process Monitor. Process Monitor keeps a log of all file system and registry access. This is great for figuring out why a particular application is failing. Often something is missing. Or, the key to figuring out why something is broke is stored in a configuration file or in a registry value. By seeing what a process attempts to access, I can usually find where the issue is. Combined with Process Explorer, I can get a good view of what an application is trying to do.

The best part of these tools is that they are free. They aren’t hard to learn how to use, either. And they aren’t considered “hacking tools,” meaning you can run them on your system, even if you’re a DBA or developer. If you manage Windows systems, I would definitely recommend familiarizing yourself with these tools, if you haven’t already.

Why Security Through Obscurity Is Bad (Alone)

Security through (by) obscurity is where we try to protect an asset by hiding it. Anyone who has ever played the game Capture the Flag knows that a motivated opponent will eventually find the flag. If there were no other deterrents in place, the opponent will scour the playing area and find the flag. If hiding an asset (the flag) doesn’t work for that simple game, it doesn’t work for information security.

However, Capture the Flag doesn’t just involve hiding the flag. In all variations of the game, all teams have attackers. Therefore, part of the deterrent is acting quicker than your opposition. In a lot of variants, each side also has defenders who have some ability to discourage or thwart attackers. Even if the particular variant doesn’t have the concept of defenders, a team can be sneaky. It can overload one side, trying to trick the opposing forces that the flag is hidden over on that side. Or some of the attackers could mock act in dismay when an opposing team heads into the wrong area of the playing area, leading that team to think they are close to the flag when they aren’t. In other words, there are always additional countermeasures.

The problem in information security with a strategy of security through obscurity alone is we are making the assumption that we are smarter than any adversary with plenty of time and opportunity on his or her hands. We don’t. Therefore, we need to have the other appropriate countermeasures (controls) in order to protect our assets. There’s nothing wrong with making an asset harder to find (obscuring it). However, that can’t be our only mechanism of protection.

Security Controls: CISA vs. CISSP

When looking at the Certified Information Systems Auditor (CISA) exam, we focus on teaching 3 types of controls:

• Preventative – keeps an incident from occurring
• Detective – Identifies the occurrence of an event and possibly the actor
• Corrective – Fixes things after the incident

However, the Certified Information System Security Professional (CISSP) indicates there are also 3 types of controls, but they are different than the ones listed as “types” by the CISA:

• Administrative – These are management type of controls. They are also known as soft controls and sometimes folks call these manual procedures.
• Technical – Also, logical. These are controls we attribute to software and hardware.
• Physical – Controls that protect the physical environment such as guards, locks, fences, and cameras.

So what does the CISSP do with the 3 listed by the CISA? Those are called control functionalities. There are 6 of those:

• Preventative
• Detective
• Corrective
• Deterrent – A control intended to discourage an attacker.
• Recovery – A control which returns the environment back to normal operations.
• Compensating – A control that provides an alternative means when another control isn’t/can’t be used.

In the CISA we often talk about compensating controls but we don’t list them as a specific functionality. However, I like the CISSP breakdown a lot better. Basically, we get a matrix between the 3 types and the first 5 functionalities, with compensating controls being understood to protect an asset when the primary control is unavailable or too costly.

The key takeaway is to understand how our controls are implemented and why they work. Classification helps us better understand what we’ve got protection-wise and it will allow us to spot gaps.