Press "Enter" to skip to content

Category: Security

Querying Private Blob Storage Containers with Azure Synapse Analytics

Published 2021-07-22 by Kevin Feasel

Dennes Torres looks at some private information:

The queries from the previous article were made against the public container in the blob storage. However, if the container is private, you will need to authenticate with the container. In this article, you’ll learn how to query private blob storage with SQL.

NOTE: Be sure that the Azure Synapse Workspace and the storage account with the sample files are set up before following along with this article. You will also need to replace your storage account URL each time that a storage account URL is used in the article.

There are three possible authentication methods, and these methods may have some variation according to the type of storage account and the access configuration. I will not dig into details about storage here and leave that for a future article.

Read on for the three authorization methods and a lot of detail on using SAS tokens (the preferred method) to access this data.

Comments closed

How Dynamic Data Masking Interacts with Bulk Copy (BCP)

Published 2021-07-21 by Kevin Feasel

Kenneth Fisher puts on a lab coat:

Hypothesis: If I have Dynamic Data Masking enabled on a column then when I use something like BCP to pull the data out it should still be masked.

I’m almost completely certain this will be the case but I had someone tell me they thought it would go differently, and since neither of us had actually tried this out it seemed like time for a simple experiment.

Click through for the experiment and its results.

Comments closed

Using Kafka for Security Situational Awareness

Published 2021-07-15 by Kevin Feasel

Kai Waehner continues a series on using Apache Kafka on security teams:

Apache Kafka became the de facto standard for processing data in motion across enterprises and industries. Cybersecurity is a key success factor across all use cases. Kafka is not just used as a backbone and source of truth for data. It also monitors, correlates, and proactively acts on events from various real-time and batch data sources to detect anomalies and respond to incidents. This blog series explores use cases and architectures for Kafka in the cybersecurity space, including situational awareness, threat intelligence, forensics, air-gapped and zero trust environments, and SIEM / SOAR modernization. This post is part two: Cyber Situational Awareness.

Click through for the high-level discussion.

Comments closed

Power BI Report Server and Query Authentication

Published 2021-07-14 by Kevin Feasel

Emanuele Meazzo shows us the power of configuration:

With the end of the IE support for Power Bi (and in general tbh), companies are scrambling finally to move their users from the legacy browser to modern ones; it was about time if you ask me.
However, there’s an edge case where using anything but IE is not as straightforward as it could be; in my case Power Bi RS worked fine for any report in any browser, except with direct query reports that were set up to authenticate via Windows Authentication as the user viewing the report:

Read on to see how to fix this so that it works well in browsers like Edge and Chrome.

Comments closed

Event Streaming for Security

Published 2021-07-09 by Kevin Feasel

Kai Waehner has a new series, and part 1 is all about using Apache Kafka as the backbone for a cybersecurity infrastructure:

This introductory post explored the basics of cybersecurity and how it relates respectively why it requires data in motion powered by Apache Kafka. The rest of the series will go deeper into specific topics that partly rely on each other.

Threat intelligence is only possible with situational awareness. Forensics is complementary. Deployments differ depending on security, safety, and compliance requirements.

Click through for the article.

Comments closed

Against sp_hexadecimal and sp_help_revlogin

Published 2021-07-07 by Kevin Feasel

Andy Mallon says it’s time to give up a couple of procedures:

We recently ran into some performance problems with our login sync, which is based on sp_hexadecimal and sp_help_revlogin, the documented & recommended approach by Microsoft.

I’ve been installing & using these two procedures since I started working with SQL Server, back at the turn of the century. In the nearly two decades since, I’ve blindly installed & used these procedures, first on SQL Server 2000, and then on every version since… just because that’s the way I’ve always done it. But our recent performance problems made me rethink that, and dive in to take a look at the two procedures to see if I could do better, which made me realize, OHBOY! WE CAN DO BETTER!!

Read on to understand how.

Comments closed

Required Permissions for Forcing Query Store Plans

Published 2021-07-07 by Kevin Feasel

Grant Fritchey reviews minimum requirements:

I was recently asked what permissions were needed to force plans in query store. I’m sure I knew at one point, but at the moment I was asked, I couldn’t remember to save my life. So, I went and looked it up. In the interest of sharing, especially for the poor person who I left hanging, here’s what I found.

Click through for the disappointing answer.

Comments closed

SQL Server 2019 on CentOS 7.5 Issues

Published 2021-06-25 by Kevin Feasel

Aaron Bertrand recaps some recent installation issues:

I’ve created countless Docker containers running SQL Server since I first wrote about it back in 2016, but I recently had my first foray into configuring SQL Server 2019 on a real live Linux machine.

It did not go as smoothly as I expected, so I wanted to share the solution to a particular problem I haven’t seen described elsewhere.

First, let me retrace my steps.

Click through for a summary of the issues.

Comments closed

Column-Level Encryption and Hashing

Published 2021-06-25 by Kevin Feasel

Eric Rouach shows off a pair of things:

Using as an example the AdventureWorks2014 database, the first script describes the process of encrypting the “CardNumber” column from the Sales.CreditCard table while keeping the data decryptable.

Our pre-requisite is the creation of a Master Key, a Certificate and a Symmetric Key.

Once having those created, we may proceed to the addition of a new column called “CardNumberEnc” (where the suffix “Enc” stands for “Encrypted”). This column has a VARBINARY(250) Data Type and is nullable.

Read on for an example of using column-level encryption, followed by how you’d decrypt the data. Then, Eric discusses hashing, though I disagree with the nomenclature of “encryption and make the data non-decryptable.” The reason is that encryption is, by its nature, a two-way process and necessarily requires the ability to decrypt. Hashing, meanwhile, is a one-way process without a direct means of reversal. Nomenclature aside, the examples are good and I appreciate Eric using one of the larger SHA2 hashing algorithms rather than MD5.

Comments closed

Logging Database-Level Security Commands

Published 2021-06-25 by Kevin Feasel

Kenneth Fisher keeps the receipts:

In my last post I talked about reasons why your permissions might go missing. One of the reasons, and in my experience, one of the more unusual reasons, is that a command was run that changed the permissions. SQL doesn’t natively log these. Well, technically it does. They can be found in the default trace. But I don’t generally consider that to be terribly useful because on a busy server what’s available in the default trace may not last long. Regardless I ended up creating a DDL trigger to collect any database level security commands run. There are other ways to do this but for various reasons I decided to go with a trigger. Primarily because I could create one piece of code and cover every database in the instance.

Read on to learn how to create an appropriate table and a trigger to log that data.

Comments closed