Don’t “Test” Against Production

A few months ago, I was participating in a threat hunting exercise on the security side. The gentleman leading the exercise was discussing some scans to run. However, before we did anything, he made sure to state that we should run against a non-production environment first. Apparently, some of his clients needed to be reminded of this rule.

He had a good tale as to why he personally believed in this IT mantra. He was doing vulnerability scanning for a manufacturing firm at one of their plants. Before he performed the scan against the full floor, he wanted to test in a non-critical environment. During the test, one of the key components for manufacturing in this test environment went off-line. When they checked the device, it was just about a brick. I say just about because they were able to reload the firmware. Fully bricked and they wouldn’t have been able to even do that. To verify that it was the scan, he ran the scan against just another of the same type of device again. Same result. The scan was the culprit. Now imagine if they had run the scan against the production floor without testing. It would have ground everything to a halt.

While the work we do may not have the scale of losses as this gentleman’s could have, the reality is that we should have the expectation that we keep production as stable as possible. Part of that is testing everything first in a like setup that is NOT production. We want to know where stuff is going to break. We want to have the luxury of being able to troubleshoot without having to immediately roll back. Rolling back in production leads to both a loss of reputation and an increase in rework. An increase in rework means we have less time to do new work. Combined loss of reputation as well as less new work being accomplished results in a potentially negative career altering event. That potential goes up based on how much money the organization loses until the rollback is successfully completed.

If your working under an Agile methodology you still cannot ignore this mantra. Agile relies on consistency, such as consistency between environments. Agile also relies on faster feedback loops (AKA adequate but rapid testing). Agile doesn’t mean skipping testing. If you roll directly to production, you’re skipping testing. As time pressed as we can be in IT, there are few instances when we simply don’t have the time to test first.


Automating Everything with PowerShell – Getting Started #tsql2sday

I try to automate everything I can with PowerShell. Whether we’re talking SQL Server, WSUS, Active Directory, or any other product with active support in PowerShell, I try to write scripts to do everything. I believe in the old proviso that good engineers are lazy engineers (thank you, Andy Leonard). And lazy engineers try not to do the same type of work more than once. That’s why automation is key.

What have I automated recently?

  • Restarting WSUS app pools in IIS along with the main WSUS service because, well, there are known performance issues that keep cropping back up.
  • Checking Active Directory Federated Services services and making sure they are started (which they sometimes fail to do after patching).
  • Exporting users and key security groups out of AD and into SQL Server, which makes that information more accessible for developers writing apps that need that information.

I’ve been asked recently how to get started using PowerShell. Essentially, it’s no different than learning anything else in IT.

  1. Have a problem you want to solve.
  2. Have a location to test (this is NOT production).
  3. Find some resources that give you insight into solving the problem.
  4. Try to build a solution.
  5. Test!

With respect to PowerShell, there’s a ton of resources. Of course, there’s the Microsoft documentation. And if you want that documented help locally, there’s a great cmdlet update-help. From there you can use get-help and the cmdlet to find out what the syntax is. If you use the -ex flag, you’ll get the examples. There’s also a large number of scripts and blog posts on just about everything you can think to do. Just do a search with your first keyword being PowerShell and then the rest of the search being what you’re trying to do. For instance, if you’re trying to figure out how to export data out of SQL Server using PowerShell, a good search to run would be: PowerShell SQL Server export data.

There’s no shame in starting with someone else’s script. Most folks do. I often do as well, even though I’ve been writing scripts for a long time now. It’s known as time savings. It’s also known as being a lazy engineer (see above). But don’t just grab a script and run it. Walk through it using the documentation. Try to understand what each line is doing. Figure out what you have to modify for your own environment. And most importantly, don’t be afraid to ask for help.

I can’t emphasize that last statement enough. You’re not going to suddenly be a PowerShell master because your Google-fu is sharp. And learning anything new takes time and effort and, sometimes, a helping hand. If you’ve tried and you aren’t getting it, ask. There’s no point holding on to your pride if you aren’t getting the job done, right?

On a closing note, yes, you can learn PowerShell if you don’t have a problem to solve. However, my experience is that I learn better when I have a goal. If that goal is solving a problem that’s causing me discomfort, I have greater motivation to get the job done. Therefore, try to think of what manual processes you’d like to get automated so you don’t have to deal with them any longer.

Want to learn more about PowerShell and SQL Server? Go here to the original hosting post. You should see links / trackbacks in the comments.

Article: Auditing for New SQL Server Agent Jobs

Latest article at

Auditing for New SQL Server Agent Jobs
Given that a lot of jobs run with sysadmin rights, we want to know when new jobs appear. Also, I cover checking for change, in case someone changes an existing job (and the limits in doing this).

Randomness in Security Configuration

We were deploying a new web service. Because of the nature of the service, we wanted it to listen on a non-standard port. Security by obscurity doesn’t work against a real attacker or well-written malware. However, if someone was just attempting to check for a web server by doing the standard http:// or https://, they wouldn’t find the web service. It wasn’t the only countermeasure we employed, but it was the first. 

This brought up a discussion of what port to use. Immediately, 8443 was suggested. Except this port is little better than 443. Then someone threw out 1701. That isn’t a well known port, at least not for a web server. However, that was still not a good choice. The person who made the suggestion was a huge Star Trek fan. This wasn’t a secret. Therefore, from a social engineering perspective, an insider could possibly guess what the port was by knowing the security pro in question. 

Tired of the back and forth, which had gone on for the better part of an hour, I reached into my bookbag and pulled out these:

Dice. If we wanted a random port, one that couldn’t be tied back to one of our preferences, this method was as good as any for selecting the value. The six-sided die is intentional due to the max port value. And we rolled. That’s how we chose the port. 

When it comes to security configurations, we can fall into the trap of trying to be too clever. We devise a method that surely will fool everyone. However, in a lot of cases a determined adversary who has done his or her research can puzzle together our plan unless we seek steps to randomize things or intentionally break from known patterns. That’s what the dice did for us: it broke us from known patterns. 

That shouldn’t be all we do. Determining the port was the easiest thing we implemented to protect that web service. Also, when we make choices, we do still have to consider the operational ramifications. For instance, if we have a SQL Server that will be accessed by business users, does throwing it on a random port make sense? It likely doesn’t. In the case of the web service, it was only going to be accessed by back-end applications. Therefore, going to a non-standard port was perfectly reasonable. Had we expected end users to access it, that would have been a different story. 

Just Say No to Social Engineering Memes

These memes, from a security and privacy perspective, are nothing but trouble. Here’s an example I just saw a friend respond to:

The reason I say trouble is because if you play along, they reveal a tremendous amount of personal information about you. That information is often used to secure your information for healthcare, banking, investments, etc. Let’s play along with this one just to see what an adversary might obtain by seeing a social media post. 

John Doe posts, “I am an Oracle of Profound Wisdom!” If we know John looks to be 30-40 years old, we can conclude:

  • John was born in 1976 or 1986 (from profound)
  • John was born in January (combo of oracle and wisdom)
  • John was born on January 16-19 (also a combo of oracle and wisdom)

We get the last 2 because Capricorn stretches from December 22 – January 19. Oracle is 16-20. That rules out December. And since John is a Capricorn, that rules out January 20. 

In other words, someone looking to use this information has narrowed down John’s birthday to one of 8 dates. And if the challenge is birth month and year, the adversary only needs 2 guesses. Most systems allow 3 or more. Just by posting his response to this meme, John has given someone enough information to compromise him. What looked like a little fun is actually a bigger security issue. 

Therefore, don’t play along. These memes reveal information you’d never reveal willingly to most folks. Yet because at first glance it seems harmless, we play along. Meanwhile, someone willing to work through the choices gains the information. The only way to protect yourself is not to play. 

#TSQL2sday: Interviewing Patterns

T-SQL Tuesday LogoThis T-SQL Tuesday is hosted by Kendra Little.

I’ve been told interviewing is an art. Perhaps it is. I view it more as an information exchange. The organization you’re interviewing with is trying to obtain information on you. You should be trying to obtain information on the organization. The interview provides an opportunity to get that information first hand for both parties and from both parties. When it comes to interviewing, I only have two main suggestions.

Be Honest, to a Point

You want to be honest about your experience, your expectations, and your personality. The first two are self-explanatory. With respect to personality, let me give an example. If you do better working in a cave with little interruption, then you should make sure that’s known. The work environment at that organization may not be conducive to you if they believe in an open office work space. There you’ll be less productive, more miserable, and wondering why you took the job. If you’re trying to get a job, any job, it’s understandable if this isn’t a priority. But that’s part of your personality, too. What can you compromise on? What can you accept?

Where you need to hold your words is when it’s obvious that the interviewers are trying to use your knowledge to solve a problem they’re having. I’ve had several friends go to an interview, be given a “hypothetical situation” that clearly wasn’t hypothetical, give out the solution freely, and then not get the job. Actually, in each case the job was pulled shortly thereafter. In reality, the interviews were nothing more than attempts to get free consulting. Don’t fall for this trick.

Ask Your Own Questions

Always remember that an interview is supposed to go both ways. It’s not just to determine if the organization is interested in your services. The interview also exists to help you determine if you want to work in the organization on that team in that particular role. Therefore, make sure you ask questions like:

  • What’s the work environment like, with specifics like traffic, meetings, and work space?
  • What are the specific duties of the team?
  • What will your duties be?
  • What’s the management structure and how does it impact your team and your role?
  • What technologies will you be working with?
  • What is the corporate and team culture like?

The last one is a big one. I had a friend who ended up working in an environment where everything was kept extremely quiet. Almost all conversations were handled by instant messenger. Some folks thrive with this kind of work culture. Others wither up and feel trapped and isolated. You’ll want to know what culture is before you take the job.

Geek Sync on Wednesday: Taking Control of Your Organization’s SQL Server Sprawl

This Wednesday, July 26th, at 12 PM EDT, I’ll be giving a presentation through Idera’s Geek Sync series. You will need to register for the session.

Registration Link for Geek Sync talk

Here’s what I’ll be covering:

You have SQL Server sprawl throughout your organization. There are SQL Servers installed on servers in all of your environments, some of which you may not even be aware of. IT personnel and developers also have SQL Servers installed; even if they are approved, there’s no guarantee of a minimal configuration. How do you get your arms around this situation?

Previous Older Entries