Press "Enter" to skip to content

Category: Security

AD Authentication with SQL Server on Linux

Published 2021-12-03 by Kevin Feasel

Anthony Nocentino will have none of your SQL authentication:

In this post, we’re going to walk through configuring Active Directory authentication for SQL Server on Linux. We will start by joining the Linux server to the domain, configuring SQL Server on Linux to communicate to the domain, and then use adutil to create our AD users and set up Kerberos for SQL Server login authentication.

This does take a bit more effort than using Windows authentication, but if you want to use SQL Server on Linux, I’d consider it a worthwhile investment of time.

Comments closed

Checking Guest Account Status

Published 2021-11-30 by Kevin Feasel

Tom Collins does not want guests barging into SQL Server unannounced:

A common  SQL Server Security check is to identify if the  guest id is enabled.        By default  , the SQL Server  guest id is disabled , but for various reasons it can become enabled. 

To identify the guest status you can either use the SQL Server Management Studio (SSMS) or through SQL code. 

Click through to see how. I don’t think I’ve ever seen a use case for having the guest account enabled on a SQL Server instance.

Comments closed

Auditing Data and Data Access Quiz

Published 2021-11-17 by Kevin Feasel

Kenneth Fisher has a pop quiz for us:

I was honored to speak at Pass Summit last week (Thanks again Redgate), and if you’ve ever been to one of my sessions you’ll know there is always a “quiz” at the end. I.e. a crossword puzzle. Well .. here is the puzzle itself, and attached (at the bottom) is the answer key.

I answered “c” for all of the questions and it worked out really well. When in doubt, Charlie out!

Comments closed

Show Data as Table and Security

Published 2021-11-09 by Kevin Feasel

Chris Webb explains that hiding a column isn’t the same thing as preventing access to a column:

In the last few months the following issue has been escalated up to the Power BI CAT team several times: customers have deployed reports into production and then found that users are able to see data they should not be allowed to see by using the “Show data point as a table” feature. The question is: is this a security hole? It isn’t, and in this blog post I’ll explain why and how you should think about security as something that happens on the dataset and not in the report.

My official response is “Hmm…” I don’t disagree with Chris, but I do understand how people might not know this and get blindsided because they think they’ve prevented someone from seeing a sensitive column. I think part of my reaction is that this functionality isn’t blaringly obvious to report developers, and so there’s a little bit of “How could you know this could happen?”

Comments closed

Azure Synapse Analytics Shared Security

Published 2021-11-05 by Kevin Feasel

Hiram Fleitas explains the value of workspace and storage account segregation in Azure Synapse Analytics:

Well, why?… perhaps you prefer not spinning more resources to segmentate the environment or decouple the workloads, but you still need to enforce data security across domains.

Lets look at how to secure an HR container in a shared Azure Synapse Analytics workspace that serves mixed workloads by using only RBAC permissions at the storage, and at container level.

It’s recommended to use a separate storage account. I will explain and demo why.

Click through for the demo and explanation.

Comments closed

System-Versioned Ledger Tables

Published 2021-10-28 by Kevin Feasel

Randolph West has a series on ledger tables in SQL Server. First up is a primer on the topic:

System-versioned ledger tables leverage the same technology: there is a table with current data in it, and an underlying history table which keeps track of changes. However, it uses a cryptographic chain that provides digital forensic evidence of tampering. Yes, if you’ll pardon the use of this phrase, I’m talking about a blockchain.

This is not a cryptocurrency. No one is using expensive graphics cards to produce a fiat currency in someone’s basement. Instead, each transaction affecting the database in question is cryptographically hashed using a SHA-256 algorithm and then stored somewhere off-site.

Part two separates out the two types of ledger table:

This week we will look at the different types of ledger table: append-only and updatable.

Unlike temporal tables, a ledger table can be append-only which makes it immutable. You can only insert data and therefore it does not need a history table. In fact, you may be using append-only tables in your data warehouse already. While this is secure, it may not be practical.

Part three covers limitations:

Every choice we make is a trade-off. New features have limitations, and ledger tables are no exception.

Some of these limitations are perfectly sensible. For example, the whole point of ledger tables is to ensure that we can provide tamper evidence. This necessarily means you can’t turn it off once it’s enabled, unless you drop the database entirely — this is just one scenario where a full defence-in-depth strategy is required.

Part four is the one I’ve been waiting for—an explanation why you probably don’t need this:

After writing several posts about a neat feature in Azure SQL called system-versioned ledger tables, it reminded me about something I’ve wanted to say for a number of years now, outside of snarky tweets.

Here goes:

You don’t need a blockchain.

In the vast majority of use cases, you need a properly audited relational database system with ACID compliance and a good recovery strategy.

There are very specific use cases in which data hashes and ledger tables make sense.

Comments closed

Four DBA ToDos in a New Role

Published 2021-10-25 by Kevin Feasel

Lee Markum has a starting point for DBAs in a new role:

You’ve just been hired into a DBA role at a new company, or you’ve been given the DBA keys at your current company. Maybe you’re a SysAdmin and your boss has informed you that you are now supposed to manage the SQL Servers as well as everything else on your plate. In any of these situations, you may have some confidence in your skills, but especially in the case of being a new hire, you have absolutely no true idea of what you’re walking into.

In these scenarios, where do you start? Start with these four areas.

Click through for the four areas. I completely agree with Lee on these for DBAs, including the order.

Comments closed

Starting SSMS with a Different Windows Login

Published 2021-10-05 by Kevin Feasel

Jack Vamvas does a thing I wish we could do innately in SQL Server Management Studio:

I  am logged onto my desktop with my primary Active Directory ID.  I need to log onto a SQL Server with SQL Server Management Studio (SSMS) with another Active Directory ID – the alternative ID has   valid  SQL Server security privileges on the target SQL Server. 

How can I log onto the target SQL Server with the alternative login  , remain on my desktop and use the alternative Active Directory ID ?

By the way, if you need to connect to a remote domain but your machine isn’t a member of that domain, add the /netonly flag to Jack’s answer. I’ve had to do this before when VPN’d into a network with a laptop not registered on that domain. Another tip is that, if you do this a lot, you might want to create a Windows shortcut which includes the full command.

Comments closed

Setting File Permissions inside a SQL Server Container

Published 2021-09-27 by Kevin Feasel

Anthony Nocentino wants to set some permissions:

This post will walk you through setting file permissions on database files copied into a container. The SQL Server process sqlservr running in containers runs as the non-privileged user mssql. The appropriate permissions on files are needed, so the SQL Server process has the proper access to any database files, log files, and backup files.

Click through for the process.

Comments closed

Slot Machine Company Data Breach

Published 2021-09-17 by Kevin Feasel

Jonathan Greig reports on a data breach:

Nevada Restaurant Services (NRS), the owner of popular slot machine parlor chain Dotty’s, has disclosed a data breach that exposed a significant amount of personal and financial information. 

In a statement, the company confirmed that “certain customers” were affected by the breach and explained that the information includes Social Security numbers, driver’s license numbers or state ID numbers, passport numbers, financial account and routing numbers, health insurance information, treatment information, biometric data, medical records, taxpayer identification numbers and credit card numbers and expiration dates.

I don’t normally link to data breaches too often because if I did, this site would be renamed to Curated Data Breaches given how frequently they occur. But what I want to know is, why in the world does a slot machine company have passport numbers, health insurance information, and medical records? What are they doing with that information? Slot machines are pretty simple: put quarter in, watch the screen light up and speakers make a bunch of happy noises, repeat until you run out of quarters. Unless there’s some sort of business arrangement where they put slot machines in the Nevada hospitals…

Also, the fact that credit card numbers and expiration dates were lost makes me wonder if they were actually PCI compliant.

1 Comment