A Fine Slice Of SQL Server
Daniel Janik takes us through a change in SQL Server 2019:
Some time back I wrote about the new data classification features in Azure and SQL Server Management Studio. If you’ve done quite a bit of work classifying your data using the extended properties, guess what?
That’s right. It’s changed in SQL Server 2019 and for the better!
Read on to see how this has become simpler to do.
Comments closedKenneth Fisher checks a box I really like checking:
I get asked this every now and again, along with the companion When was the last time this login was used? It’s a pretty easy question to answer but there are some caveats. First of all you need to have your system set to log both successful and failed logins. You can probably get away with successful only but personally I want to know a failed attempt just like I’d want to know a successful one.
This is a thing that we tend to avoid because of how many events it adds to the Security event log, but is critical in understanding whether that person trying to log in as sa gave up or stopped due to a successful login.
1 CommentTomaz Kastrun shows us row-level security in Power BI:
Row -Level Security or managing roles in Power BI is not something new. But environments, where there is a need for securing read access for end-users based on their account name, are very frequent. Row Level Security is omitting and controlling access to a user or group (or distribution group in active directory) to rows on a single dataset (or table in SQL Server) and all the relationships to this dataset.
There is a performance cost to this, but if you need it, it’s there. Power BI row-level security can also work with Analysis Services row-level security and (to an extent, and this is new) SQL Server row-level security.
Comments closedGauri Mahajan shows how we can read data in Azure Blob Storage from Azure Databricks:
Since our base set-up comprising of Azure Blob Storage (with a .csv file) and Azure Databricks Service (with a Scala notebook) is in place, let’s talk about the structure of this article. We will demonstrate the following in this article:
1. We will first mount the Blob Storage in Azure Databricks using the Apache Spark Scala API. In simple words, we will read a CSV file from Blob Storage in the Databricks
2. We will do some quick transformation to the data and will move this processed data to a temporary SQL view in Azure Databricks. We will also see how we can use multiple languages in the same databricks notebook
3. Finally, we will write the transformed data back to the Azure blob storage container using the Scala API
It’s just a few lines of code. One of the best things Microsoft and the Databricks team did for Azure Databricks was to ensure that it felt like a first-party offering—everything feels a little more integrated than Databricks for AWS.
Comments closedGilbert Quevauvilliers walks us through the groups which can set permissions in Power BI:
As you can see from above it is good to know which groups can be used to assign permissions in the Power BI Service.
If there is anything I have missed, is wrong or needs updating please let me know via the comments section below.
Thanks for reading!
Gilbert has a nice matrix as well as lots of screenshots establishing the matrix’s veracity.
Comments closedWe are announcing support for Azure AD pass-through and password hash authentication for Azure SQL DB (single database and database pools), Managed Instance, and Azure Synapse (formerly SQL DW).
– Azure AD password hash authentication is the simplest way to enable authentication for on-premises Active Directory users in Azure AD. Users are synchronized with Azure AD and password validation occurs in the cloud using the same username and password that is used in on-premises environments. No additional infrastructure is required.
– Azure AD pass-through authentication provides a password validation mechanism that validate users directly with on-premises Active Directory, outside the cloud. Pass-through authentication does not require ADFS or other third-party federation services.
– Each of these authentication methods can be configured by Azure AD Connect, allowing you to provision users in the cloud.
Read on to see what this means for you.
Comments closedSolving for data exfiltration can become an unmanageable problem if the PaaS service requires you to store your data with them or it processes the data in the service provider’s network. But with Azure Databricks, our customers get to keep all data in their Azure subscription and process it in their own managed private virtual network(s), all while preserving the PaaS nature of the fastest growing Data & AI service on Azure. We’ve come up with a secure deployment architecture for the platform while working with some of our most security-conscious customers, and it’s time that we share it out broadly.
Click through for the architectural pattern.
Comments closedStuart Moore introduces dbaSecurityScan:
How easy it to audit them? If someone asks you the DBA exactly who has access to object A, can you tell them? How do people get access to that object, is it via a role, a schema or an explicit permission?
Is that information in an easy to read or manipulate manner?
How do you ensure that permissions persist between upgrades? I’ve certainly seen 3rd party upgrades that have reset database level permissions. Do you have a mechanism to check every permission and put them back as they were?
We’re all doing the devops these days. Our database schema is source controlled, and we’re deploying it incrementally in pipelines and testing it. But are we doing that with our database security?
So in the classic open source way, I decided to scratch my own itch by writing something. That something is dbaSecurityScan, a PowerShell module that aims to offer a solution for all of the above.
Click through to see what dbaSecurityScan covers today, how to call it, and what you can do to get more info.
Comments closedThomas Rushton shows us how to migrate logins from one server to another using just T-SQL:
There are three things to look at:
1. Creating Logins from Windows accounts
2. Creating logins that are authenticated by SQL Server
3. Assigning membership of the appropriate server rolesNote that we’re just creating a snapshot of what’s there – we’re not aiming to keep these things in true synchronisation between servers. Note also that this script will only create accounts that don’t exist. If there’s an account already there with the same name, the script generated will not recreate it with new options.
We get the final script as well as a nice walkthrough of each component.
Comments closedJason Bonello shows how easy it is to integrate Azure Key Vault into Azure Databricks:
In Azure Key Vault we will be adding secrets that we will be calling through Azure Databricks within the notebooks. First and foremost, this is for security purposes. It will ensure usernames and passwords are not hardcoded within the notebook cells and offer some type of control over access in case it needs to be reverted later on (assuming it is controlled by a different administrator). In addition to this, it will offer a better way of maintaining a solution, since if a password ever needs to be changed, it will only be changed in the Azure Key Vault without the need to go through any notebooks or logic.
If you don’t use Key Vault, Databricks does include its own secrets storage, so there’s really no reason to keep them in plaintext.
1 Comment