A Fine Slice Of SQL Server
Marco Russo and Alberto Ferrari don’t want to let any information slip out:
Let us start with a simple challenge: we want to show the percentage of sales in Europe, compared to the sales made to all customers worldwide. It is a relatively trivial question, the kind of DAX code you learn at the beginning of your Power BI career, and it can be solved with a simple measure:
But what happens when the model includes security roles? Well, that’s what you’ll have to read on to learn.
Comments closedDaniel Calbimonte adds a new user two separate ways:
Open PGAdmin and connect to the server where you want to create the new user.
In the Object Browser, expand the Server and go to Login/Group Roles tree, and right-click on the folder. Select Create>Login/Group Role option from the context menu.
In addition to adding a user via PGAdmin, there’s also a script to add users via pgSQL, and that will look a lot more familiar to SQL Server administrators.
Comments closedDan English continues a series:
This is a bit overdue and a follow up to a few other posts I have regarding using Service Principal authentication with Power BI reports Power BI using Service Principal with Synapse SQL Pool and Power BI using Service Principal with Synapse Data Explorer (Kusto) Pool.
With the other two posts I did last year I had to use the SQL Server ODBC driver to get that to work and the big downside to that is that you need to use a gateway with that. Well in this case we are going to take a look at the new Shareable Cloud Connections that were announced earlier this year Streamlining cloud connection management for datasets, paginated reports, and other artifacts | Microsoft Power BI Blog | Microsoft Power BI
Click through to see what you need to get it working.
Comments closedRod Edwards shares some hard-earned guidance:
Some organisations are more strict on security than others. Thats just the way of the world, whether it be local policy, industry policy, paranoia or worryingly…just not considering it a priority.
This is why Microsoft have to offer BYOK, no, not the famous Icelandic singer from the 90’s and beyond either. I’m (very) tenuously referring to “Bring Your Own Key” which allows customers to let the encryption key to be handled by Microsoft for their encryption purposes, but create and use one of their own.
Read on to learn more about how it works, as well as a couple of important warnings you should keep in mind.
Comments closedMaheswaran Arunachalam gives us an update on data security in Microsoft Fabric:
Row-Level Security (RLS) and Column-Level Security (CLS) are methods that simplify the design and coding of security in applications by imposing restrictions on data access. CLS allows for specific users to access only certain columns of a table relevant to their department, protecting sensitive data. RLS, on the other hand, restricts users to accessing only data rows pertinent to their role or department. Both methods locate the access restriction logic in the database tier, applying the restrictions every time data access is attempted from any tier, making the security system more reliable and robust.
Click through for the process. This is pretty much the same as any SQL Server-based system.
Comments closedThe Instaclustr team reviews an announcement:
On October 11, 2023, the Apache ZooKeeper™ project announced that a security vulnerability has been identified in Apache ZooKeeper, CVE-2023-44981. The Apache ZooKeeper project has classified the severity of this CVE as critical. The CVSS (Common Vulnerability Scoring System) 3.x severity rating for this vulnerability by the NVD (National Vulnerability Database) is base score 9.1 Critical.
That’s a rather high base score and is comes about if you have the setting quorum.auth.enableSasl=true. Updating to the Zookeeper 3.7.2 or alter, 3.8.3 or later, or anything in the 3.9 branch will fix this vulnerability.
Matthew McGiffen talks encryption:
Huge thanks to everyone who responded to my invitation to blog on Encryption and Data Protection for this month’s T-SQL Tuesday.
I got what I hoped for, which was a wide(ish) interpretation of the topic. Posts from those who are clearly advocates of encryption, and from those who have scepticism around the encryption approaches that some people take.
We’ve also got discussions about the importance of basic data governance, and the protection of intellectual property.
Read on for links to several posts on the topic of security.
Comments closedBen Johnston gives us a high-level overview of SQL Server security options:
SQL Server security structure, mechanisms and methods are very thoroughly documented in the Microsoft documentation, but it is quite daunting if you don’t already know about the functionality. I recently had a request to explain some security features of SQL Server so that internal audits could be completed. While thinking about the request and preparing for the meeting, I realized how many security features are available in SQL Server. The purpose of this post is not to thoroughly explain how all of these items work but to give an introduction to these features and a few recommendations. Given how many security-centered features are available, I’m sure I missed a few, and new features are added all the time, but these are the main features at the time of this writing.
This is solid as a view into what options are available. I do have at least one moderate-to-large qualm with the article: cross-database ownership chaining is something you should never enable; use module signing instead.
Comments closedSrinivas Kandibanda announces a series of security updates for all supported versions of SQL Server:
The Security Update for SQL Server 2022 RTM CU8 is now available for download at the Microsoft Download Center and Microsoft Update Catalog sites. This package cumulatively includes all previous SQL Server 2022 fixes through CU8, plus it includes the new security fixes detailed in the KB Article.
I linked specifically to the SQL Server 2022 RTM CU8 blog post, but there are security bulletins for all versions of SQL Server going back to 2014. If you’re running SQL Server 2012 or earlier, no updates for you.
Also, the highest-risk CVE items are in SQL Server 2019 and 2022; for 2017 and below, the one security bulletin covers a moderate-severity denial of service attack.
Comments closedOf all of the various data protection options available to us in SQL Server, I argue that Transparent Data Encryption (aka TDE) is worthless Security Theater.
TDE encrypts your data files “at rest.” This means one cannot crack open a hex editor and just start reading your raw data. And did you know that you can use a hex editor and start reading string values right out of your MDF files? Go search on that – that’s a separate tangent you can go explore.
Read on to understand the ways in which Andy finds fault with TDE.
Comments closed