Press "Enter" to skip to content

Category: Security

Authenticating to Fabric APIs via Sempy and Service Principals

Published 2024-05-15 by Kevin Feasel

Gilbert Quevauvilliers links everything together:

I have been doing a fair amount of work lately with Fabric Notebooks.

I am always conscious to ensure that when I am authenticating using a Service Principal, I can make sure it is as secure as possible. To do this I have found that I can use the Azure Key Vault and Azure identity to successfully authenticate.

Read on for some of the advantages of using Azure Key Vault for this sort of credential management, as well as how to get it all working.

Comments closed

A Review of the Microsoft Fabric Security Whitepaper

Published 2024-05-13 by Kevin Feasel

Kevin Chant takes a look:

To manage expectations, Microsoft do openly state during the introduction that this white paper was created by combining multiple online security documents together.

Which probably explains some of the repetition. However, multiple references are better than none.

Plus, in the introduction they provide a link to the main Microsoft Fabric security page. Which is good starting point if you know what security feature you are looking for.

Anyway, the content itself is good. It provides some really good explanations and diagrams relating to certain areas. To help demystify certain aspects of security for some people.

Read on for Kevin’s first impressions of the whitepaper.

Comments closed

Table Audits with Temporal Tables

Published 2024-05-08 by Kevin Feasel

Erik Darling is keeping an eye on you:

Sort of recently, a client really wanted a way to figure out if support staff was manipulating data in a way that they shouldn’t have. Straight away: this method will not track if someone is inserting data, but inserting data wasn’t the problem. Data changing or disappearing was.

The upside of this solution is that not only will it detect who made the change, but also what data was updated and deleted.

Read on to see how it works. I’ve used temporal tables for this type of scenario, and they’re fine for stable table designs.

Comments closed

The Benefits of Separating Data from Content in Microsoft Fabric

Published 2024-05-08 by Kevin Feasel

Tom Martens drives a wedge:

This article is solely about one question: what has to be done if a content creator needs to create and publish reports but the content creator is not allowed to see all the data?

This seems to be a simple requirement: develop content (finally publish the report), but with Row Level Security (RLS) applied.

To answer the question, I think it’s necessary to understand the following core principle, at least to some extent:

  • Workspace roles

Read on for more information about how workspace roles work in this domain.

Comments closed

Security and the Weakest Link

Published 2024-05-07 by Kevin Feasel

Brian Kelley explains:

When I look at a system and think about its security model, the first thing I start poking around at is where I think security is weakest. For instance, if my target is a Microsoft SQL Server box, I don’t generally look for a weakness in SQL Server itself. I start looking at the operating system, I look at accounts that may have access, and since I’m really worried about the data being taken, I look to see how backups are handled and where they are written to.

Read on for more information, as well as a link to Brian’s 2019 video on attacking SQL Server.

Comments closed

Central Management Servers and SSMS 20

Published 2024-05-03 by Kevin Feasel

Greg Low works around an issue:

I’ve recently been doing work with a site that makes extensive use of Central Management Servers. And that’s an issue if you upgrade past v19.3 of SSMS.

Here’s my counter-argument: how frequent is it to find organizations that have enough SQL Server instances to make a Central Management Server worthwhile and also do not have any sort of certificate management process?

And more importantly, why don’t they have certificate management processes in place for SQL Server? This isn’t 2008 anymore—everybody (for some slight exaggeration of the term “everybody”) has certificate management in place for websites. It’s incredibly rare to find websites without TLS certificates, so somebody in your organization is managing certificates somehow. Why are these people not also managing certificates for SQL Server? Because once you have proper certificates in place rather than self-signed certs, there is no SSMS problem.

And if money is the issue, money is not the issue. Note that Daniel’s post is over 6 years old (and here’s me self-linking for street cred), meaning any company without the budget for proper certificates could have put this into place anytime over the past 6 years.

Self-signed certificates are okay for debugging purposes on personal machines. But they should not be acceptable for connecting to SQL Server in any environment. Certificate-driven encryption is a critical part of securing data movement over the wire, and a trusted certificate chain is critical for ensuring attackers cannot sit in the middle of that connection and read the data.

Comments closed

Exporting and Sharing Power BI Reports in Fabric

Published 2024-04-25 by Kevin Feasel

Sandeep Pawar distributes PDFs like candy:

With the proposed solution below, you will be able to :

  • Export a Power BI report, or a page of a report or a specific visual from any page as a PDF, PNG, PPTX or other supported file formats
  • Apply report level filters before exporting
  • Automate the extracts on a schedule
  • Save the exported reports to specific folders
  • Grant access to individual folders in the Lakehouse

Click through for the solution.

Comments closed

Cannot Open Backup Device with SQL Managed Instance and SAS Token

Published 2024-04-23 by Kevin Feasel

Sam Garth troubleshoots an issue:

On a recent case, a customer was trying to restore a database from a storage account using a SAS token when they received the below error.

An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)

Additional information:
Cannot open backup device
‘https://storage.blob.core.windows.net/container/dbbackup_2024_03_21_121901.bak‘
Operating system error 86(The specified network password is not correct.).
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3201)

Read on for the troubleshooting steps Sam followed to solve the problem.

Comments closed

sp_CheckSecurity

Published 2024-04-22 by Kevin Feasel

Jeff Iannucci announces a new tool:

Maybe you have some scripts you found on the internet to check some SQL Server security settings or look for odd permissions. Or maybe you don’t. Well, sp_CheckSecurity checks about 40 different objects, configurations, and permissions for possible issues. You can read more details about it on the sp_CheckSecurity page.

I’m glad to see a new tool in the security space. Chris Bell used to have sp_woxcompliant when he was still consulting, but that’s lost to history now (though I do have a copy on my PC, as one does). You can run CIS checks in dbachecks and Chrissy LeMaire has a Powershell module for DISA STIG auditing, but I’m not sure how easy that is for a DBA or consultant to use.

H/T Jeff Iannucci, who gave me the friendly reminder to add the blog.

Comments closed