Press "Enter" to skip to content

Category: Security

Decrypting Stored Procedures in SQL Server

Published 2023-08-17 by Kevin Feasel

Steve Jones breaks the connection:

I had a client that was struggling with some encrypted stored procedures. They needed to decrypt them, which I know is a pain in the #@$%@#$@#$#@. I had to do this one. This post shows how I sent them some code to do this.

Note, SQL Compare 15 does this easier and simpler. If you own it, I’d use that instead. A future post will show how easy that it.

Stored procedure encryption is one of the more annoying features in SQL Server. The idea was, if you wanted to prevent end users from reading your code, you could encrypt the procedures. But in order to use the procedures, SQL Server needed to decrypt them and you needed this to work on restored backups, so the decryption keys needed to be available to that SQL Server. The infrastructure is a bit different from how Microsoft eventually landed Transparent Data Encryption, enough so that it turned out breaking these procedures is trivial, as Steve shows.

I didn’t know that SQL Compare did decryption. The couple of times I needed to do this, I had used a standalone tool which was released in the 2005 timeframe, so it’s good to see something still supported which does this.

Comments closed

Connecting to SQL Server 2022 via Azure AD

Published 2023-08-04 by Kevin Feasel

Deepthi Goguri makes a connection:

Applicable to-

SQL Server 2022 on-prem on Windows and Linux and SQL Server 2022 hosted on Windows Virtual Machines.

Once you install the SQL Server, there are three different authentication methods that you can use to connect SQL Server along with the Windows and SQL Server authentication. They are –

  1. Azure Active Directory Universal with Multi-Factor Authentication
  2. Azure Active Directory Password
  3. Azure Active Directory Integrated

Read on for the pre-requisites as well as a detailed guide on how to set everything up.

Comments closed

Creating a Power BI VNet Data Gateway

Published 2023-08-02 by Kevin Feasel

Meagan Longoria rolls up her sleeves:

If you are using Power BI to connect to a PaaS resource on a virtual network in Azure (including private endpoints), you need a data gateway. While you can use an on-premises data gateway (the type of Power BI gateway we have had for years), there is an offering called a virtual network data gateway that is currently in preview.

The VNet data gateway securely communicates with the data source, executes queries, and transmits results back to the service, just like the on-premises data gateway. But it doesn’t require us to provision a virtual machine in the same network (or a peered network) of our Azure data source.

Read on to see some important caveats, as well as a step-by-step guide.

Comments closed

Secrets Management in Docker Swarm

Published 2023-08-02 by Kevin Feasel

Andrew Pruski has a secret:

In a previous post I went through how to run SQL Server in Docker Swarm and in this post I want to continue delving into the Docker Swarm platform by looking at secrets.

Like Kubernetes, Docker Swarm has functionality to store sensitive data in secrets. When running SQL Server in containers we usually have to manually type out the environment variable for the MSSQL_SA_PASSWORD which isn’t great if we want to store that code in a public repository so being able to obfuscate that password is great!

Read on for a failed first attempt, followed by analysis and then success.

Comments closed

Mitigating Dynamic Data Masking Side-Channel Attacks

Published 2023-07-31 by Kevin Feasel

Ben Johnston wraps up a series on dynamic data masking:

This is the fifth and final part of this series on SQL Server Dynamic Data Masking. The first part in the series was a brief introduction to dynamic data masking, completing solutions, and use cases. The second part covered setting up masking and some examples. The third and fourth sections explored side channel attacks against dynamic data masking.

This final part covers mitigations to side channel attacks, additional architectural considerations and an analysis of the overall solution.

Throughout the entire series, Ben has done a good job of laying out exactly what dynamic data masking is good for—and what it isn’t good for. I tend to harp a lot on the latter but Ben keeps a reasonable approach throughout this series.

Comments closed

A Primer on Postgres Database Security

Published 2023-07-28 by Kevin Feasel

Murtaza Umair provides guidance:

Keeping your database up to date with the latest PostgreSQL release is vital in maintaining the security of your database. Once every year, PostgreSQL comes out with a new release, which includes new features, security enhancements, and performance improvements. Each major release is supported for five years, during which PostgreSQL releases quarterly minor updates to fix bugs and patch security issues. The schedule for new updates and more information is given on PostgreSQL’s website, at https://www.postgresql.org/developer/roadmap/. 

Nothing in this is earth-shattering but it is a solid overview.

Comments closed

Testing Multi-User PBI Row-Level Security in Excel

Published 2023-07-27 by Kevin Feasel

Gilbert Quevauvilliers performs a test:

I have been doing a fair amount of work on Row Level Security (RLS) where the requirements were quite complex to implement.

Once I had completed the implementation of RLS there were two outstanding items I needed to test.

  1. Make sure that the users are only viewing the data they should see.
  2. Ensure that the performance of a selected user is still as fast as possible.

I would like to mention that this certainly can be done using DAX studio to get the query performance stats, I wanted to test this using Excel.

Gilbert had to do a fair amount here, but there’s a nice walkthrough indicating how to do it all.

Comments closed

Power BI Dataset Data Source Credential Options

Published 2023-07-25 by Kevin Feasel

Angela Henry puts together a list:

If we are using enterprise-wide datasets, we don’t want Power BI datasets owned by individuals; we want them to be owned by a Service Principle so they aren’t relying on specific individuals when things go sideways (and because we all want to go on vacation at some point). However, it’s not always clear on what credentials will actually be used for our data sources in our datasets when using a Service Principle. In a previous post, I talked about how to set up a service principle to take over a dataset when using data gateways, but one of the pre-requisites I listed was that your data sources needed to be configured with appropriate credentials. That’s where this post comes in.

You essentially have three options for data source credentials, depending on your data source type.

Click through for that list of three options, as well as the trade-offs inherent in each.

Comments closed