Press "Enter" to skip to content

Month: November 2021

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

Resource Governor and Scheduler Domination

Published 2021-11-09 by Kevin Feasel

Forrest McDaniel shares an example of Resource Governor blocking query activity:

Ok, I get it, scheduling queries can be complicated. See this and this and maybe this and this too but only if you have insomnia. I still thought I kinda understood it. Then I started seeing hundreds of query timeouts on a quiet server, where the queries were waiting on…what? CPU? The server’s at 20%, why would a query time out after a minute, while only getting 5s of CPU, and not being blocked?

It was Resource Governor messing up one of my servers recently in a way I never expected.

Click through for the story. I’m not sure I’ve experienced this when running Resource Governor, but Forrest has an easy demo which replicates the problem.

Comments closed

Reducing Image Sizes with Docker-Slim

Published 2021-11-08 by Kevin Feasel

Evan Seabrook puts Docker images on a diet:

If you’ve ever worked with Docker, there’s likely been at least one time when it started taking up significant storage space on your computer. For example, some of your images took a long time to download in a CI/CD pipeline. Some common approaches to this problem are to:

– Swap out the base image for something lighter

– Reduce the number of RUN statements in your Dockerfile

– Remove cached package manager artifacts as part of your Dockerfile

These steps, while helpful, can take up a significant amount of time and effort. Thankfully, there are open source tools that can automatically minify an existing Docker image. Enter docker-slim.

Looks like it can slim things down considerably. I haven’t tried this, but might give it a go and see how it works.

Comments closed

Azure Synapse Analytics Database Templates

Published 2021-11-08 by Kevin Feasel

Santosh Balasubramanian shows off database templates in Azure Synapse Analytics:

Azure Synapse Analytics is a limitless analytics service that brings together data integration, enterprise data warehousing, and big data analytics. It gives you the freedom to query data on your terms, using either serverless or dedicated resources—at scale. Azure Synapse brings these worlds together with a unified experience to ingest, explore, prepare, manage, and serve data for immediate BI and machine learning needs.

One of the challenges that users in key industry areas face is how to describe and shape the mass of data that they are gathering. Most of this data is currently stored in data lakes or in application-specific data silos. The challenge is to bring all this data together in a standardized format enabling it to be more easily analyzed and understood and for ML and AI to be applied to it.

Azure Synapse solves this problem by introducing industry-specific templates for your data, providing a standardized way to store and shape data. These templates provide schemas for predefined business areas, enabling data to be loaded into a database in a structured way.

Read on to see what they can do, and try them out in a Synapse workspace.

Comments closed

Lessons Learned from the Serverless SQL Pool

Published 2021-11-08 by Kevin Feasel

Teo Lachev has some thoughts for us:

I’ve architected and currently implementing a solution that uses Synapse (my last newsletter has the details, plus the architecture diagram). Synapse Serverless is the Microsoft answer to Amazon Athena but instead of using open-source tools like Presto, it’s built on SQL Server. In this project we extract many tables from 1,500 on-prem SQL Server databases and stage them in ADLS.

Read on for Teo’s notes on the topic.

Comments closed

Using the Dedicated Admin Connection

Published 2021-11-08 by Kevin Feasel

Reitse Eskens has the key for the back door:

I got a call this morning from a coworker. One of the database instances was unreachable with the message that the TempDB log file was full. No processes could login and the only way to get things going again might be to restart either server or instance. Restarting the instance should be the last resort, because after a restart SQL Server will plough through all the logfiles to find transactions to either roll forward or roll back. That will take more time that you want. But, we had the famous DAC backdoor installed.

Read on to see how the DAC can save the day.

Comments closed

Finding Queries with Index Hints

Published 2021-11-08 by Kevin Feasel

Aaron Bertrand wants to find queries using index hints:

Index hints should be a last resort, but we all know how that tends to go in SQL Server. If you have committed to using index hints, be aware that at some point you may have to update those explicit references. A specific index may get renamed or dropped, making the hints invalid, or a new and better index may be created. How do we find these references to remove, update, or at least document them? I see a lot of queries out there that will search the plan cache for index hints (often using fuzzy matching), or that just happen to use a specific index, but what about queries with explicit index hints that aren’t currently in the plan cache, and is index usage really what we’re after?

Click through for a detailed investigation, and also congratulations to Aaron for landing at Stack Overflow.

Comments closed

Finding Processes Waiting on Spinlocks

Published 2021-11-08 by Kevin Feasel

David Fowler helps us find processes waiting on spinlocks:

Spinlock contention is always a real headache to deal with. I recently saw an issue when spinlock contention on SOS_CACHESTORE was making the server virtually unresponsive. The issue was very intermittent with no obvious pattern but the assumption is that it was caused by a particular process in the application. Finding what that process was the tricky part, they don’t show up as waiting tasks so your usual scripts for looking for waiting processes may not work here.

It’s almost never spinlocks, but when it is, this is how you figure it out.

Comments closed

Thinking like an Escalation Engineer

Published 2021-11-08 by Kevin Feasel

Stacy Gray shares stories:

“You new?” asked with an amused grin.

“Yes,” I replied floating 2 inches off the ground with a huge, toothy smile.

“Which team?”

“SQL!”

“Good luck.”

I glanced at the badge.  It was blue.  My opportunity to get some secret, inside wisdom!

“I want to become a blue badge.  Do you have any advice on that?” The elevator doors opened.

“Solve your own cases,” was the reply.

Read on for stories, advice, and more.

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