Monday 15 October 2018

Data is a Secure Asset, so Secure It.


Good day! This will be the last post in this series before I make a pivot to speaking on Graph Databases in SQL Server for a few. We will certainly return to the topic of data management in the future though, so stay tuned. On a personal note, I was recently let go without reason or notice from my position so, as of today, I am contemplating where I want to go next in my career, but I can assure you that it will be fun and exciting! I pray for success for my former employers, and that with time, they cultivate wisdom and peace in their endeavors.


On to the matter at hand: Data security. Securing our data is a matter of utmost importance. Every day, it seems, we hear about a recent data breach, and over a few months, we find out the reasons for it. We always shake our heads and ask how any company could let their data be so vulnerable, and then we look at where we are and ignore the evidence of the same sort of vulnerabilities. I have been a staunch advocate for data security throughout my career and in this, perhaps I can share a bit of what I know on the subject, so that it can help you to become the same.


Firstly, let us talk about the reasons. There exists, of course, the obvious reasons that we want to protect our data, as it is an asset to our company. That being said we can also look at a larger picture of data security. We have an obligation, in most cases, to the laws of the land to keep our data safe and secure, whether it be through GDPR, FOIP, HIA, HIPA or some other data protection act that has been passed in our jurisdiction. We also need the peace of mind that we are doing everything we possibly can, to ensure our data is safe for our clients, and pass that along to them.


I’ve said it in a previous post, but it bears repeating: data is an asset, and should be given the same organizational gravitas as cash. It is not an afterthought, and not something that should be left lying around. It also should be treated with the same diligence for chain of custody as cash and, luckily, we live in an era where the tools are available to us to do so.


Data security, like network security, is a matter of layers. A wise man once told me that obfuscation is no security, so we will step aside from that as a means of securing our data. We cannot just hide our databases and hope they are not found by bad actors; we must fortify them with as many layers of security as possible. For those of you coming from a background in network security and the OSI model, the majority of this will be dealing with layers 5 through 7, as we are going to assume that network security is doing their job well in layers 1 through 4.



Securing your data in layers involved a fairly easy stepped list:



1.    Know your zone!

2.    Understand your security!

3.    Use the tools!

4.    Avoid defaults!



Seems simple, so let’s dive into the details on these.



Knowing your zone involves being aware of your organization’s architecture, network security, and data needs. Know that your data should always be housed exclusively in the most secure zones for network security, usually layer 5. Find out what your application and data using structures need access to. Vertically align three-layer applications and avoid replication where possible. If you are housing data in layer 6, and it is mission critical or personally identifiable information, you should address that, and find ways to move that data to a more secure location. If you need to have layer 7 applications using that data, find a way to proxy it in a secure method. With the release of SQL Server 2017 we saw some new and interesting ways to manage proxy data without replication, which is the ideal way to manage these vertically aligned applications.

Understand your security! Don’t just know the layers of network security, but find out about what you are doing with your instances and databases in your zone. Dig in and find out what solutions are being used to address data at rest and data in motion. Find out how many linked servers are in place and find out why they are. Do what you can to address the biggest vulnerability of any data structures: surface area. Reduce it as much as possible. Think about what would happen if a bad actor got into a specific database. What could they do from there? What data could they access? Could they redirect, copy, export, or otherwise take data that would affect the organization? The customers? The staff?

Within SQL Server there exist many tools to secure our data, both at rest and in motion. If we have a database or instance that isn’t using a database master key, we have immediately failed at securing our data. This fundamental step is not optional, and should be activated on all databases, including our master, before any data goes into it. As a side note, I prefer 21-character generated passwords from an enterprise password manager to accomplish this and I will say, I do not recommend anything less than 14 characters. Ensure that you have a valid and current certificate for your instance connections, and they are encrypted connections. If you get a warning in SSMS or VS or ADS about an insecure connection to a database, that should be addressed immediately. Ensure you have implemented transparent disk encryption (TDE) on your database file systems, so the data files remain encrypted at rest. Use “Always On Encrypted” if possible, to ensure that your data in motion is encrypted, and make sure they are keyed differently all the way down each layer. Encrypt your file backups of certificates and keys from SQL server with a separate password, which I have begun to refer to as a Back-Up Key (BUK) after the infamous Buck Woody.

Avoid defaults like the plague. Every single person in the world, who has ever touched SQL Server in any way, knows about the SA account. Disable it, use your enterprise domain controller, and only use windows auth wherever possible. Everyone knows the default schema of [dbo]. Do not use it, where possible. Replace the dbo schema with one of your own and secure the schema. Always make sure your logins are secured to the schema for access where needed, and remove them when not. Remove any BUILTIN\ domain accounts so that if your network security is breached, your databases remain secured. Utilize separate accounts for file systems, I prefer gMSA accounts, but the method is up to you. And always, always, encrypt the instance, the databases and the data movement.

As a final note, I do not like linked servers. I will make no qualms about that, and will be happy to argue the reasons until the cows come home. Do not use them. Manage data movement through SSIS, which allows an encrypted data movement, and avoids increasing the surface area of your enterprise data. I cannot say it with more emphasis. Do not use linked servers in an enterprise.

Tuesday 9 October 2018

Data Driven means Data is an Asset (Pt. 2)


  To continue with my streak of slightly ranting/slightly advice columns on how to run a Business Intelligence program, today I will touch on Data as an asset. This is going to be a bit more on the ranting side, as I have a bone to pick with people who put the term ‘data-driven’ into their mission statement or board packages, and then turn around and do nothing to manage, care for, foster, or otherwise tend to their data as an asset. Too many times I have seen this term thrown around within organizations that treat data as a by-product, with all the respect and dignity they would industrial waste.

 Data is an asset. This means that it holds value to an organization. Like all assets, it depreciates over time. The nice thing about data, is it is generally a part of the business to generate data just through the daily operations. Monetary assets that the company generates, through sales, services, or other income streams, are more difficult to generate. This ease of generation in data, tends to cause many organizations to overlook proper care of this asset.

Proper caring for this asset, maintenance or upkeep if you will, starts from the bottom with a strong core of expertise in the organization. These are the people who know and understand data, and are passionate about keeping it, using it, monetizing it, and maintaining it. We layer onto that a strong foundation of technical infrastructure, and then ice the whole thing with data management processes into a delicious cake of data assets for the organization. (Is anyone else hungry?)

The last four companies I have been involved with professionally, including my current, have all stated without reservation during the interview process that they were ‘data-driven’ companies. None of those four companies could tell me during the interview, who their data stewards were. One was able to tell me their lead DBA (because he was on the interview panel), and two were able to accurately tell me, what type of data servers they primarily used as an organization. (One was Oracle, the other Microsoft.)

Let’s start with the people. I do not believe myself unreasonable to have certain expectations for a person who makes their career as a DBA. I have done it before, and I take what I do seriously. I expect that there are good working processes in place, good documentation of those processes, at least half of the business practices follow industry best practice. (I learned young that all the way is not a good expectation to have.) And finally, I expect there to be solid documentation on all data assets.

When it comes to the infrastructure, I have higher expectations. I expect solid HA infrastructure for primary assets, with backups offsite, that run on appropriate schedules. I expect good disk capacity and planning, and I expect that all servers are set up to best practice guidelines running current software, and up to date security patches. I expect there to be scheduled maintenance, a solid DR plan, strong security, and a sustainable retention policy in place and enacted with procedures.

Finally, the data management processes. The business of data. I have expectations that any organization that calls itself ‘data-driven’ has a solid data management policy for the enterprise, first and foremost. There should be stewardship and ownership baked into that policy and practiced in the organization, as a whole. I expect that there should be mastered data for the primary KPIs of the organization, there should be domain controls and audit processes set up for data entry, and there should be processes and procedures for all of these things. I expect the organization to treat data generation as seriously as data security, which they enforce and control with the same vigor.

I don’t see these things. I have seen parts working in some organizations, and other parts working in others. This is going to change. Over the last decade, data has become the buzzword. The idea that keeps boardrooms buzzing but doesn’t mean anything. Over the next decade, it will become a lynchpin. Any company that isn’t currently on track to engage with itself, and treat it’s data like an actual asset, will suffer for it. They will be left in the dust by the companies that do.

There are exceptions. There are industries as a whole that lag behind in this and will always be out of date. I don’t see that as an excuse, but rather as a challenge. As such, I will continue to fight for treating data as an asset, that it has value, and that it should be protected.



Business Intelligence is a Service (Pt. 1)


I am back, sort of. I continue to struggle with some very large blind spots on the right side of my field of vision. The doctors I have seen are still working together to try and figure it all out, and I have some follow ups remaining with additional specialists. But enough about that, let's talk about service.

Too often in my career, I have arrived at a new project, a new company, or a new position and found one of two major cultural issues that at once perplex and infuriate me. The first is a company that claims themselves as data driven, and treats data as an afterthought instead of an asset. The second, and in my opinion worse of the two, is a company or department culture that hoards data, and treats the release of data and intelligence as a gilded prize that only the select few could ever be allowed.

I want to address these, and I will circle back in the follow up post about data driven culture, and what that means to me. For now, I want to talk about data as a service.

Data is generated every second of every day in a company. A portion of the data is directly perceived as data, be it from an ERP or other enterprise application, or a software that collects and reports data, or even just the daily record keeping. However, in addition to these obvious and structured sources, there are also the unobvious sources, such as building automation controls, wi-fi tracking, emails (so many emails), phone queues, MDM trackers, and all sorts of other methods of collecting and storing data. These other areas of data are no less sources, and no less important, they just lack the direct correlation to the business processes for users.

Providing the data from any and all sources is the primary function of Business Intelligence, and it should always be thought of as a service. We provide a service to the organization and to the business users. In order to remain in the mindset of providing a service, the first step is to take away the concept that you, as a business intelligence professional, personally own the data, the analysis, or any of the reporting objects you have created. I have alluded to this before in my post on deferred ownership, and the reasons I use this method.

Next up, is to stop and listen. Listen carefully and closely to the needs of the organization, through the business users. Requirements gathering to build new reports, to provide new intelligence, to create new dashboards: that is where we separate ourselves. We need to be attentive, to listen, and to be prepared. Understand their context, their needs and beyond that, what it is they are trying to accomplish. That is where your expertise in the data will shine.

Ask your users for feedback constantly. As I have stated numerous times in past posts, we need the users to be involved in the entire process. They need to feel that they are a part of all decision making that affects them, and that they are in control. If they want pie charts, use pie charts. Perhaps explain to them alternatives or options because pie charts are not great for anything, but use them if they are what is requested. This is not your report; it belongs to them, and they will be accountable for it.

On that last thought though, I have never expected my business users to understand the nuances of data structures, entities, attributes, data modelling; heck, I could care less if they understand basic regression. I want them to understand their business, and to be able to tell me how they understand their business. I do not need them to dictate what to report, I need them to communicate to me how they manage their business, their people, and the processes. I can then translate that into the technical needs. If someone happens to be technically inclined, I will still steer them to describe the business first, and then their technical understanding.

Without much more further ado, the advice I offer today, in no uncertain terms, is this: Change the way you provide intelligence. Provide data as a service, and follow all the tenets of customer service in doing so. Provide data platforms as a service, reports as a service, analysis as a service, and don’t build anything for your users if you cannot give up ownership.


Reflections from the Summit

This past week I attended PASS Summit 2019 in Seattle. I had an amazing time and it was great to catch up with friends and colleagues, b...