Author: way0utwest

Lawsuits and Data Breaches

After the breach of the Target payment systems, a class action lawsuit was filed against the company. The action alleges that Target failed to implement strong enough security. Regardless of the merits of this action, it does bring to light a few things that we, as data professionals, should be aware of.

The first is that if our companies store any PII, financial, medical, or other sensitive data, we need to ensure that our management is aware of potential security pitfalls we see, as well as the possibility for legal action if the data is somehow disclosed. The risk and mitigation actions taken need to be weighed by management, and we should approach this as we might any other upgrade or enhancement to a system. With logic, and rational discussion about the issues, providing guidance and potential solutions.

However we also should be aware that no matter what security efforts we undertake, criminals are going to be finding ways around our defenses. As this piece notes, Target likely had security in place, but it’s never going to be enough because the attack vectors and techniques are out-pacing the ability of security techniques to provide protection. The solution, or at least a potential mitigating action, is one that data professionals can help with.

We, and the businesses that employ us, should be incorporating analytics into our defenses to detect abnormal actions, by both external and internal, users. We should be looking for potential ways that data is disclosed, and perhaps even scanning the Internet for potential leaks of data. We won’t prevent all problems, but if we can detect them early, we can limit the damage.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.5MB) podcast or subscribe to the feed at iTunes and Mevio .

The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

February 25, 2014
Auditing Matters

There’s been some reporting about Yahoo Mail getting hacked and potential issues recently. I ran across this piece that talks about what happened and it’s an interesting read, but there’s one quote that stuck with me: “Yahoo reported on its Tumblr that it had detected “a coordinated effort”—basically, an attack—by somebody trying to gain access to user accounts.”

It’s not the notification or the proactive resetting of accounts, but the detection that resonates with me. We’ll never stop all hack attacks. We’ll never plug all the holes in software. We’ll never anticipate the ways in which our systems might be compromised, but we can detect issues. I think more often than not we can deal with any hacks or attacksif we are aware they took place.

We have some login auditing in SQL Server, an auditing and eventing framework, and the ability to capture and store this data. However we don’t have good proactive tools to help us detect issues. I’d like to see enhanced tooling to allow us to review log data, write alerts that look for patterns, and more. While much of this can be built by DBAs, it requires use to develop and maintain software, and even then it’s easy to miss potential attack vectors if you don’t constantly supplement your knowledge and enhance your tools.

Given what we have to work with, I’d encourage you to learn a bit about the different frameworks and gain some basic skills with the tools. I’d also encourage you to think about writing queries to look for potential hacking issues, like updating all of your lookup values to the same string, or embedding script tags in your data. I’d encourage you to write or speak about what you learn, and how you use the information. If you’d like to write software to make the task easier, that would be great.

I’d also encourage you to befriend your network or security administrators and teach them how to query their own lo data. Lots of their tools collect data, but provide poor query tools for the information. Perhaps you can even build them a data warehouse that allows them to tighten security by examining their own data.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.4MB) podcast or subscribe to the feed at iTunes and Mevio .

The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

February 24, 2014

The SQLCLR Impact

While I’m sure there are a few of you still using SQL Server 2000 instances, I think at this point that most companies have moved to SQL Server 2005 and later. Even if you still manage a pre-2005 instance, chances are you have a few 2005, 2008, or 2012 instances as well.

It’s been a little over 8 years since the CLR subsystem was added in SQL Server 2005. At the time this was announced, we had any number of DBAs worrying about the impact. Before SQL Server 2005 was released there were lots of concerns that developers would embed most of their database logic in SQLCLR procedures and cause lots of performance issues. That doesn’t seem to have been the case, but this week I wanted to ask you about your experience.

Have you had to review and implement custom SQLCLR assemblies on your instances?

I’m looking for an idea here of the impact of the SQLCLR in your environment. Do you have standard assemblies on multiple instances or have you dealt with an ad hoc object here and there? Do you think SQLCLR is a normal part of your applications or a one-off that’s rarely used? Do you go through the code and understand how the logic is implemented? Do you ensure error handling is implemented? Perhaps you’ve not dealt with it at all?

I haven’t seen much of a call for SQLCLR objects from the various developers that have worked on the SQLServerCentral site. Most of them seem content to use their traditional tools: they use .Net code in the front end or T-SQL code in the database.

Let us know this week. Has the SQLCLR been a regular part of your SQL Server environment.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.4MB) podcast or subscribe to the feed at iTunes and Mevio .

The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

February 21, 2014

Virtual Lab – Creating Differencing Disks

This is part of my series on building a virtual lab for use with SQL Server and Windows. You can see the entire series here: Building a Virtual Lab with Hyper-V.

The whole point of virtualization is better use of resources. Whether it’s CPU, storage, networking, power, etc., we are trying to save having to purchase 5 or 6 machines and connect them all, pay for them, etc. It’s much, much easier to use virtualization.

This is especially true of storage. When we build a lab, we don’t want to have to allocate a ton of space for our systems. In my case, for my design, this is what I want to end up with.

From my last post, I had the bottom two files already set up. These are my installations of Windows Server 2012 R2 Standard and Core, sysprep’d and ready to use.

Differencing – Saving Space

The idea in differencing disks is similar to the snapshot feature I used in VMWare to do this same process. I take a base disk, and create a “copy” of it. Except the copy doesn’t have everything from the base disk. Like a database snapshot in SQL Server, if I need to read something that’s in the base disk, I reference that. If I change something, or add something, I read from the copy.

As you can see above, I have 5 small disks at 4MB and my 2 large base disks. Each of the small disks is a differencing disk off either the Standard or Core base installations.

I created these by selecting New Hard Disk in Hyper-V Manager

After the intro screen, I make sure I’ve picked VHDX.

At the next screen, I choose the differencing option.

Now I need to pick a location for the copy. On my setup, I’m trying to keep this lab contained, so I have a WS2K12 Lab folder. I choose that as my location for the differencing disk. I give it a name, in my case, I’m using “Function” concatenated with the name of the Windows host. As seen below, my file server will be named “FileAvalanche.vhdx”.

Next I choose the parent disk. this is the base image. Depending on what I had in my design, I choose the Standard or Core images for various machines.

I get a summary where I can see all my options, and then click finish.

I do this five times and all of a sudden I have a lab.

A few things. Use some naming convention so you can tell which disk is which. Also, make sure you stick things in a location. I set a specific folder location for my VMs and disks, and then altered the Hyper-V Manager settings to default to this folder. On my machine that’s D:\Virtual Machines.

That helps me keep things contained, and it also means that when I want to copy a VM for a backup or to another machine, it’s easy.

February 20, 2014