Press "Enter" to skip to content

Curated SQL Posts

Setting Fabric Connection Properties with the ODBC Driver

Published 2026-09-22 by Kevin Feasel

Ryan Adams digs into configuration settings:

Some SQL Server ingestion scenarios depend on connection-string properties that the built-in SQL Server connector in Data Factory for Microsoft Fabric doesn’t expose. If you need to route reads to an Availability Group secondary, connect reliably across a multi-subnet listener, or read columns protected by Always Encrypted, you’ve probably hit that wall. This post shows a supported way around it: use the ODBC connector together with an ODBC data source name (DSN) configured on an on-premises data gateway (OPDG). The example in this post uses Always Encrypted in a Copy job.

Click through to see some of the things you can set. These are standard SQL Server properties, so they can also work with normal SQL Server installations that are making use of Availability Groups and Always Encrypted.

Leave a Comment

Configuring Row-Level Security with OneLake Security

Published 2026-09-22 by Kevin Feasel

Reza Rad secures some data:

If you’ve set up row level security in Power BI before, you know the usual drill: open the semantic model, add a role, write a DAX filter. But once your data lives inside a Microsoft Fabric OneLake structure, there’s a better way to do this. You can implement it directly in OneLake instead, so that every object built on top of that data, your lakehouse, your SQL analytics endpoint, your semantic model, and your report, all follow the same security setup automatically. Define it once, upstream, and everything downstream inherits it. This is called OneLake security, and in this post I’ll walk through exactly how to set up row level security (RLS) this way, from the lakehouse all the way to your Power BI report.

Read on for a video and summary with timestamps.

Leave a Comment

Broker Tasks on Query Store Cleanup

Published 2026-09-21 by Kevin Feasel

Erik Darling does some digging:

While looking at a client’s server, I noticed there was, at any given time, 2-3 background sessions with the command column saying BRKR TASK, which I found quite odd.

Only the Microsoft-shipped broker queues were present, and none were activated. Nothing uses Service Broker, Mirroring, or Log Shipping.

Read on for Erik’s findings. If you need a Misfits song to listen to, we are close enough to the end of October for Halloween to work.

Leave a Comment

Finding Unused Objects in Power BI Semantic Models

Published 2026-09-21 by Kevin Feasel

Chris Webb goes looking for unused objects:

A few weeks ago I wrote about how Semantic Link Labs now has tools with interactive UIs and showed how you could use this to view lineage and so Vertipaq-Analyzer-stuff in a notebook. I didn’t show what I think is the coolest new feature though: the ability to find the tables, columns and measures in a semantic model that aren’t used and which therefore could be deleted. There are tons of excellent third-party tools that do this available already of course but the advantage of using Semantic Link Labs for this is that you can automate the process of finding unused columns and run it from a notebook and, crucially, you have two ways of finding those unused columns: from analysing the structure of downstream reports and from analysing DAX queries captured in Workspace Monitoring.

Read on to see how it works.

Leave a Comment

Digging into Entity-Attribute-Value Tables

Published 2026-09-21 by Kevin Feasel

Greg Low has started a series on entity-attribute-value tables. The first post covers what they are:

If you’ve been working with databases for any length of time, you will have come across implementations of Entity-Attribute-Value (EAV) tables (or non-tables as some of my friends would call them).

Instead of storing details of an entity as a standard relational table, rows are stored for each attribute.

The second post covers pros and cons:

In an earlier post , I discussed the design of EAV (Entity Attribute Value) tables, and looked at why they get used. I’d like to spend a few moments now looking at the pros and cons of these designs.

Greg is very much against EAV, and I agree with this. I do like Greg’s alternative of using something like JSON, with the proviso that the database simply become a whole-record storage and retrieval engine rather than trying to strip out and splice in new JSON via T-SQL. Otherwise, spend the time on proper data modeling and take advantage of what the platform can do for you.

1 Comment

Thoughts on Databases as Honeypots

Published 2026-09-21 by Kevin Feasel

Andreas Wolter shares some thoughts:

CISA recently published guidance on using cyber decoys, tripwires, breadcrumbs, and honeytokens to detect attackers who are already operating inside an environment: Using Cyber Decoys to Strengthen Detection and Response

Looking at this through my SQL Server lens, I think it is worth considering how these concepts can be applied to database systems.

Before building any kind of database honeypot, you need to ask yourself:

Click through for that list of questions, as well as additional thoughts from Andreas.

Leave a Comment

Sysadmins Bypassing Disabled xp_cmdshell

Published 2026-09-18 by Kevin Feasel

Fabiano Amorim explains a vulnerability:

SQL injection inside Microsoft-signed system stored procedures is not supposed to happen. Yet, as I’ve been documenting in this series, it happens more often than you may assume.

This article walks through another one I found and reported to the Microsoft Security Response Center (MSRC). It’s a textbook SQL injection sitting inside sys.sp_MSdeletefoldercontents, a system stored procedure used by SQL Server replication.

What makes it interesting is not the injection technique itself (there is no clever Unicode trick this time), but what it enables: a working execution path for xp_cmdshell on a server where xp_cmdshell is explicitly disabled by configuration.

Admittedly, I kind of shrug my shoulders at this one as well. You already need to be sysadmin, and sysadmins can enable xp_cmdshell whenever. I understand that the point of Fabiano’s post is that the procedure call ignores xp_cmdshell’s status, so there’s something to it. But I have trouble thinking that the SQL Server team made the wrong call in deciding it’s a low-risk vulnerability.

Leave a Comment

Comparing xp_readerrorlog and sp_readerrorlog

Published 2026-09-18 by Kevin Feasel

Steve Jones looks at a pair of procedures:


I was creating a question on sp_readerrorlog and realized that this procedure is different from the one it wraps: xp_readerrorlog. This post digs into a few differences.

Click through for the answer, though Steve’s reference of sp_readerrorlog being “new” is, I think, not quite right. I’m pretty sure that both sp_readerrorlog and xp_readerrorlog were introduced in SQL Server 2005. I did dig up a Rodney Landrum article from April of 2010 talking about sp_readerrorlog, so it was at least in SQL Server 2008.

Leave a Comment

A Bug in a Frameless Window Aggregate

Published 2026-09-18 by Kevin Feasel

Hugo Kornelis digs into an execution plan:

The OVER clause, that can be added to aggregate functions to turn them into window aggregates, can come with or without a frame specification, in the form of an ORDER BY clause, plus an explicit or implied ROWS or RANGE clause. If there is no frame specification, then every row in a partition, or window, can see all other rows in the same partition or window for the purpose of the aggregation.

In plansplaining part 6, I looked at this specific form of window aggregation, and explained in detail all the steps that the execution plan takes to compute the aggregation result and add it to each of the rows, for every row in each window.

Click through for another look at the plan.

Leave a Comment