Press "Enter" to skip to content

Category: Security

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

Using Powershell to Set the SQL Server Port Static

Published 2024-04-19 by Kevin Feasel

Vlad Drumea doesn’t want a dynamic port number:

This post demos a script that I’ve put together to automate the configuration of the static TCP port for a SQL Server instance using PowerShell.

The script is derived from another PowerShell script that I’ve written to help spin up SQL Server test instances in my home lab.

This should be helpful you’re working in a restrictive environment where you can’t install additional PowerShell modules, and you couldn’t take advantage of dbatools’ Set-DbaTcpPort.

Click through for the script, but also use dbatools whenever you can because it’s a good product and I haven’t done any unpaid shilling for them in far too long.

Comments closed

Locking down Branches in GitHub

Published 2024-04-17 by Kevin Feasel

I have a new video:

In this video, I show you how you can prevent people from checking directly into a specific branch, as well as explaining why you might want to enable this feature and how it looks for administrators.

Admittedly, this video would work a bit better with multiple user accounts, but I think I got the general point across.

Comments closed

Enhanced Patching for SQL Server on Azure VMs

Published 2024-04-17 by Kevin Feasel

Taryn Pratt has an update:

We are pleased to announce the GA release of enhanced patching capabilities for SQL Server on Azure VMs using Azure Update Manager. When you register your SQL Server on Azure VM with the SQL IaaS Agent extension, you unlock a number of feature benefits, including patch management at scale with Azure Update Manager.

Read on to see what this does, how you can set it up, and how you can migrate from the SQL Server IaaS agent extension’s automated patching service.

Comments closed