A Fine Slice Of SQL Server
Matthew 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 closedGilbert Quevauvilliers shows us an example of Object Level Security in Power BI:
My example which I am going to detail below will show you how I will restrict a user from viewing sales data. The same user will be able to see Quantity amounts. This becomes really powerful because not all users need to see all the data.
My goal here is to show you how to the basics on how to use Object Level Security. Yes, there are more advanced options to configure a combination of Row Level Security and Object Level Security.
By using Object Level Security, it means that I can now have a single model which can be used for Financial and Non-Financial reporting.
Read on for an example.
Comments closedRon the Polymath notes a change slipped into Windows:
The October 2020 Preview releases of Windows builds 1809 (KB4580390), 1903/1909 (KB4580386), and 2004/20H2 (KB4580364) include the following change:
– Adds support for the Transport Layer Security (TLS) 1.1 and 1.2 protocols when connecting to SQL Server using the data providers in Microsoft Data Access Components (MDAC).
Click through for Ron’s experience and another update of potential interest.
Comments closedScott Klein takes us through the process of enabling Always Encrypted:
Always Encrypted provides a separation between those who own the data and can view it, and those who manage the data but should have no access. Always Encrypted does this by allowing clients to encrypt sensitive data inside client applications while never revealing the encryption key to the database engine.
One of our clients utilizes Always Encrypted to protect sensitive data. In our work for this client, we realized that a walkthrough documenting the end-to-end process of enabling Always Encrypted would be helpful for everyone.
Click through for a walkthrough of the process.
Comments closedAttila Bukor takes us through the process of setting up role-based access controls on Impala tables:
After setting up the integration it’s time to create some policies, as now only trusted users are allowed to perform any action; everyone else is locked out. Resource-based access control (RBAC) policies can be set up for Kudu in Ranger, but Kudu currently doesn’t support tag-based policies, row-level filtering or column masking.
Click through for the process, as well as current limitations.
Comments closed