A Fine Slice Of SQL Server
Chad Callihan looks at one option for securing a SQL Server instance:
This month’s T-SQL Tuesday topic comes from Matthew McGiffen, who asks us to talk about encryption and protecting data in SQL Server. To read the full topic invite, click the T-SQL Tuesday logo to the right.
For this month’s invite, I thought I’d write about Transparent Data Encryption (TDE) and give a reminder about how it relates to tempdb.
Read on for Chad’s reminder.
Comments closedDeborah Melkin asks some questions:
If we start expanding things beyond just the technology and functionality, we can really see where the concept of data protection becomes much larger and more complex.
I admit that I’m not really up-to-speed on the technical aspects of encryption or data protection. That doesn’t fall under a lot of the work that I do. But there’s another side to data protection that’s worth talking about. It’s about knowing your data. This is where I’ve been spending a lot of my time these days.
When I ask if you know your data, I’m asking if you can answer the following questions:
Read on for some of the types of questions you’ll want to think about.
Comments closedEncryption is a funny thing. Since the dawn of communication, whenever people have wanted to keep their secrets secret, they’ve used some sort of encryption. I’m sure parents started spelling things so their kids wouldn’t understand as soon as there was spelling. Using words their kids wouldn’t understand, while the kid sits there thinking “Oh, Dad, you’re so embarrassing, thinking I don’t know what that means…”. Encryption is all about keeping information away from people, particularly those who could use it against you. Ask the folk from Bletchley Park if you don’t realise how this can impact world events.
Rob links to Enigma and Bomba (the British system for decrypting Enigma messages) but there’s another interesting story out of Bletchley Park as well: the Lorenz cipher, which was cracked by a Polish mathematician early on, but decryption was quite slow, on the order of a message or so per day. This led to Colossus, the first digital computer in existence. The National Museum of Computing in Bletchley Park has a working rebuild of a Mark 2 Colossus on display and we got to see it (and get the story behind it) on day 1 of Data Relay this year, so that was fun to see.
As an interesting side note, the British never told the Soviets that they had decrypted the Lorenz cipher, so when the Soviets took hold of these machines near the end of World War II, they assumed that nobody had cracked the code, so they continued to use these for a while, allowing the British access to certain sensitive communications for a time.
Comments closedBen Johnston wraps up a series on row-level security:
As seen in the previous section, there are several ways bad actors can attempt to bypass RLS. Attacks range from removing RLS, getting data from other systems or straight brute-force methods using side-channel attacks. Mechanisms exist for each potential attack that allow you to avoid the attack or monitor for the attack when avoidance isn’t possible. This section covers those mitigations.
RLS can be bypassed or attacked using several broad categories. These include direct attacks, where RLS is modified in a malicious fashion or disabled, indirect attacks where information can be gathered without modifying the underlying RLS, and side-channel attacks that use specially crafted queries to derive data from RLS protected tables. Refer to the previous section of this series, RLS Attacks, for a full explanation of each attack type.
I’ve enjoyed this series and the end is no different.
Comments closedDavid Fowler performs one of the early steps of a penetration test:
Since witnessing a rather nasty cyber attack around a year ago, I’ve been thinking quite a bit about security. Do we really know how secure our SQL Servers are? Penetration testing is a great way to find out where our weaknesses and vulnerabilities are. Ideally you probably want to be getting regular pen tests conducted by external companies (although in my experience, some are better than others. I’ve known some who argue totally pointless issues and miss glaring holes which I know exist, but that’s a whole different story) but wouldn’t it be useful if we could conduct some of these tests ourselves?
In this series of posts, I’m going to try to knock together a little pen testing toolbox so that we can hopefully find some of these vulnerabilities. I’m no pen testing expert and this is never going to replace getting a professional pen tester in to test your setup but it might go some way to helping us understand some of our vulnerabilities and identify them.
Click through to see what David did, as well as an alert which helped pick out this port scanning operation.
Comments closedBen Johnston continues a series on row-level security in SQL Server:
As mentioned in previous sections, RLS is an addition to security and should not be used as the primary method to limit access to data. It is a supplementary layer, useful in specific scenarios. There are also instances where RLS can be defeated by an unauthorized user. The attacks listed below are broken down into direct attacks, indirect attacks, and side-channel attacks. The categorizations could be changed, but the important part of each is the vulnerability discussed.
The one scenario I’m a bit surprised about is the divide by zero attack, as I had figured the filter predicate would apply before the computation leading to a divide by zero scenario.
Comments closedDeepthi Goguri enables Azure Entra ID security on a SQL Server VM in Azure:
To enable the SQL Server 2022 on a virtual machine to use Azure AD, we need below things:
Deepthi then includes the list of requirements, starting with a managed identity and moving on to permissions and other configuration. It looks like a fair number of steps, but it’s not that onerous a task once you get to it.
And this also gives me a chance to rant about Microsoft marketing a bit, as they are in the process of switching the name Azure Active Directory to Azure Entra ID. Granted, Azure Active Directory isn’t Active Directory (although it does perform a very similar task in a fairly similar way). So to remove confusion that I don’t think really existed, they changed the name to Entra ID. Except that most of the Microsoft documentation still says Azure Active Directory, and we have about a decade’s worth of blog content talking about Azure Active Directory, so when you go searching for the resolution to a problem, you’ll have to search for Azure Entra ID as well as its former name, which means people will still link the product to Azure Active Directory—at least, until the point when there’s enough blog content and documentation in place to replace the large majority of those existing blog posts—and so you renamed a product for no reason. Plus, they picked an ambiguous name that people will pronounce multiple ways: is the “ent” in Entra like “enter the dungeon” or Entra like “a delicious entrée”?
But then again, considering how many pronunciations of Azure there are, maybe this is the plan…
Comments closedJose Manuel Jurado Diaz 86’d that option:
Today, I worked on a service request that our customer got the following error message: Cannot open backup device ‘https://XXX.blob.core.windows.net/NNN/YYY.bak‘. Operating system error 86(The specified network password is not correct.). RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3201). Following I would like to share with you some details why this issue and the activities done to resolve it.
Read on to get a better understanding of what this error actually means and how you can fix it.
Comments closedBrian Kelley tries out a product:
My organization typically moves production data to other environments. There are a variety of use cases:
- Testing with the amount and frequency of production data.
- Performing analytics on said data.
- Delivering production-like data to a third party for their use.
We do not want to move production data around. Instead, we want to deliver “production-like” data for these use cases. Sometimes, we work with multiple systems integrated with each other, and in those cases, we need the data to match up. In other instances, we need the sensitive data, such as personal identifiable information (PII), to be altered so it’s no longer sensitive, but there’s no requirement for it to be consistent across systems.
Read on for Brian’s full review. I should also note that this is most definitely a paid product.
Comments closedChris Webb shows off an interesting aspect of a new feature:
A few weeks ago an important new feature for managing connections to data sources in the Power BI Service was released: Shareable Cloud Connections. You can read the blog post announcing them here. I won’t describe their functionality because the post already does that perfectly well; I want to focus on one thing in particular that is important for anyone using Power BI with Snowflake (and, I believe BigQuery and probably several other non-Microsoft sources): Shareable Cloud Connections allow you to have multiple connections to the same data source in the Power BI Service, each using different credentials.
Read on to see what Chris means and how you can take advantage of it.
Comments closed