A Fine Slice Of SQL Server
Eric Cobb has a Powershell snippet for us:
I recently had a case where I needed to compare the Active Directory Groups of 2 users and determine which AD Groups both users were in. As it turns out, with a little prep work this is really easy to do in PowerShell.
Click through for the setup and the process.
Comments closedChad Callihan has a few tips for making that sa login safer:
Every SQL Server install includes the sa login as a sysadmin. This can be good for consistency; however, that also makes it a prime target for attackers trying to get into your SQL Server. That is one of many reasons why you should make the following changes to protect your sa login from being used in an attack.
Click through for the list. Troll me says the best answer is to rename sa (like Chad mentions), create a new account called sa, and write a script to add a rule to your firewall banning the IP of anybody who logs in with this account.
Comments closedChad Callihan reminds us of the other half of backups:
You setup TDE for your database. Great! Now you need to restore that database to a different server. Maybe you’re migrating off of an older server or maybe there is testing to be completed for an upcoming release. If we try to backup/restore or attach the database on a different server we’ll get some variation of an error stating “cannot find server certificate with thumbprint…” and the process will fail. There are a few steps to get the TDE database restored and we’re going to walk through them today.
Read on for those instructions.
Comments closedGreg Larsen takes us through the built-in set of server and database roles in SQL Server:
Managing Security for SQL Server is extremely important. As a DBA or security administrator, you need to provide access for logins and database users to resources within SQL Server. SQL Server has both server and database resources where access might need to be granted. Access to these resources can be granted to either individual logins or database users or can be granted to roles, for which logins or database users can be members. Granting access via a role is known as role-based security.
There are two types of roles: fixed or user-defined. In this article, I will discuss the different fixed server and database roles provided with SQL Server and how these roles can be used to support role-based security to simplify providing access to the different SQL Server resources. In a future article, I will discuss user-defined server and database roles.
Click through for more information. Or just give everybody sysadmin. I mean, that’s probably fine, right?
Comments closedMatthew McGiffen walks us through the intention of Transparent Data Encryption:
Transparent Data Encryption (TDE) was introduced in SQL 2008 as a way of protecting “at rest” data. It continues to be available in all versions of SQL right up until the present, until recently it was only available in the Enterprise editions of SQL Server but from SQL 2019 it was made available in standard edition.
Read on for more detail.
Comments closedJack Vamvas answers a question:
Question: I need to extract the securables for a user created SQL Server database role. For example , the Explicit Permissions including the Permission,Grantor,Grant,With Grant and Deny. And also the Securables – Schema,Name,Type
How can I get this information via t-sql?
The only downside in Jack’s query is that it enumerates the securables for the principal. But if the principal is part of an Active Directory group (or multiple groups), this becomes more difficult.
Comments closedAll the way back with SQL Server 2016 Microsoft released the Dynamic Data Masking feature in the database engine. It seemed like a huge step forward and promised so much, but there were severe limitations around the way that we could control who sees what masked data. It was a case of you either got to see masked data wherever it was configured, or you saw clear data, there was no granularity. I wrote about this and a few other things to do with Dynamic Data Masking all the way back in August of 2016 when I was at SentryOne. You can check that post out here. Also, back then I created several Connect items (blast from the past there), one of which was pulled over to the user voice replacement where I was asking for the UNMASK securable to be made more granular, you can check that out here.
So, why I am I writing this post? Well, it seems that our (my?) request has been granted. At least in Azure SQL Database. On March the 17th this year a little announcement slipped out stating “General availability: Dynamic data masking granular permissions for Azure SQL and Azure Synapse Analytics“. So, has this delivered on what we wanted, to really help this feature live up to its promise?
Read on to see how it works and what John thinks of the whole thing.
Comments closedYou’ve created a beautiful, wide-open Tabular model. You use USERELATIONSHIP() to switch relationships on and off. Everything works and everyone is pleased. Then RLS sneaks in, such as when external users need access, and you must secure on some dimension table. You create a role, specify a row filter, test the role, and get greeted with:
The UseRelationship() and CrossFilter() functions may not be used when querying ‘<dimension table>’ because it is constrained by row-level security defined on ‘<dimension table>’ or related tables.
Read on to learn what the issue is and one potential workaround.
Comments closedDaniel Osvath has a guide for us:
Kerberos authentication is widely used in today’s client/server applications; however getting started with Kerberos may be a daunting task if you don’t have prior experience. Information on setting up Kerberos with an SSH server and client on the web is fragmented and hasn’t been presented in a comprehensive end-to-end way on a simple local setup.
At Confluent, several of our connectors for Apache Kafka® support Kerberos-based authentication. For development and testing of these connectors, we often leverage containers due to their fast, iterative benefits. This tutorial aims to provide a simple setup for a Kerberos test environment with SSH for a passwordless authentication that uses Kerberos tickets. You may use this as a guide for testing the Kerberos functionality of SSH-based client-server applications in a local environment or as a hands-on tutorial if you’re new to Kerberos. To understand the basics of Kerberos before diving into this tutorial, you may find this video helpful. Additionally, if you are looking for a non-SSH-based setup, the setup below for the KDC server container may also be useful.
Click through for two approaches to the problem.
Comments closedChad Callihan takes us through an example of configuring Always Encrypted:
Always Encrypted can encrypt columns with deterministic encryption or randomized encryption. Your choice on which is better for you depends on how you plan to use the encrypted data. Deterministic encryption will produce the same encrypted value every time whereas randomized will not have the same encrypted value.
If you want to encrypt records but will also want to be querying encrypted records, you’ll want to choose deterministic for more efficient queries. Deterministic encryption will still allow point lookups, equality joins, grouping, and indexing when querying data.
Click through for the step-by-step process.
Comments closed