Press "Enter" to skip to content

Category: Security

Firewalls and TLS in SQL Server on Linux

Published 2024-01-24 by Kevin Feasel

I have a new video out:

In this video, we harden our SQL Server instance in two ways: by using a firewall to limit inbound traffic, and by using a certificate to force encrypted connections to SQL Server.

This was a video I enjoyed creating. It also shows the progress of SQL Server security: go back to 2005 (pre-SP1) and even SQL authentication over TDS was unencrypted by default. They fixed it so that the authentication would use a self-signed cert but the data you’d get back from query results was unencrypted. Nowadays, encryption is easy (if you’re okay with a self-signed cert) and some future version of SQL Server will make it mandatory.

Comments closed

Granting Users Access to Create Fabric Items

Published 2024-01-17 by Kevin Feasel

Gilbert Quevauvilliers is in a giving mood:

I was recently working with a customer where I was showing them the awesome new features of Microsoft fabric. I then created a workspace and attempted to grant the individual users access to the workspace to create fabric items or workloads.

When the users went into the app workspace with the fabric settings, enabled, the users could not create any workloads.

Read on to see what the problem was and how you can resolve it.

Comments closed

Adding a Service Principal to a Power BI Workspace

Published 2024-01-17 by Kevin Feasel

Marc Lelijveld reads the manual, disagrees with it, and stubbornly sets out to prove it right:

Today, I run into an issue by adding a service principal to a workspace. To my experience so far, this should be straight forward and a matter of adding with the right permissions in the workspace settings. However, for some reason I couldn’t get it to work. In this blog I will elaborate on what caused this issue and which tenant setting influences this.

Click through for the story behind this and the resolution to Marc’s issue.

Comments closed

Managing Azure Data Factory IP Ranges for Azure Firewalls

Published 2024-01-03 by Kevin Feasel

Meagan Longoria has a script for us:

While a private endpoint and vNets are preferred, sometimes we need to configure Azure SQL Database or Azure Storage to allow use of public endpoints. In that case, an IP-based firewall is used to prevent traffic from unauthorized locations. But Azure Data Factory’s Azure Integration Runtimes do not have a single static IP. So how do we keep the firewall updated so that ADF can access these resources?

One option is to run everything through a self-hosted integration runtime, which can have a static public IP. But that also means you have to install (and keep updated) a Java SDK if you are converting data to Parquet or ORC files.

Another option is to obtain the IP range list published by Microsoft and update the firewall. That is what I did for a recent project.

Read on to learn more about the latter option, including a Powershell script to do the work.

Comments closed

Using KQL in Azure SQL DB Audits

Published 2023-12-27 by Kevin Feasel

Josephine Bush tracks what’s happening on that Azure SQL Database:

According to Microsoft, “Kusto Query Language (KQL) is a powerful tool to explore your data and discover patterns, identify anomalies and outliers, create statistical modeling, and more. The query uses schema entities that are organized in a hierarchy similar to SQLs: databases, tables, and columns.”

Note: KQL is case-sensitive for everything. Also, remember to refrain from querying everything just like you wouldn’t with SQL — don’t do the equivalent of SELECT * from gianttable.

Microsoft also has a lot of documentation with best practices and a quick reference guide to the Kusto commands. This blog post covers the ones I use the most.

Read on for a primer on the language, specifically some of the things you can do when reading Azure SQL Database audit information.

Comments closed

After Login Triggers in Oracle

Published 2023-12-27 by Kevin Feasel

David Fitzjarrell performs some auditing:

In a world of ever-increasing access account security has become a major concern. More often than not accounts used for automated processes gather more and more privileges, on the assumption that they are used only by managed, automated processes and execute vetted code, The argument is that these automated processes can’t go rogue and do damage with these elevated privileges, which is true, sort of. In and of itself the database does not police where these account connections originate, which can pose a significant security risk should this vetted code, or passwords for these privileged accounts, become known.

Oracle has, for decades, provided after logon triggers, which can be utilized to ‘police’ accounts by reporting where they originate and, if necessary, disallowing such logons. Of course it will be necessary to record such connection ‘violations’ in a table so that they may be investigated. A “complete” script to do this is shown below:

Click through for that script, as well as additional thoughts from David.

Comments closed

Password Handling in Powershell Automation Scripts

Published 2023-12-18 by Kevin Feasel

Ajay Dwivedi gives us a tip:

I have been writing automations using PowerShell for many years now. One common issue I notice with people’s code is the improper handling of passwords. In this blog, I share how to set up a Credential Manager on a SQLServer and use the same for handling passwords in automation.

To setup Credential Manager, we need to download and execute the following steps using scripts from my Github repo SQLMonitor.

One point I’d like to clarify in Ajay’s scripts is that the passwords in the database aren’t hashed. Hashing is a one-way operation, so you’d never be able to decrypt it with a passphrase. The password is encrypted and the passphrase isn’t a salt—salts are a way of making a hash unique from the plaintext to prevent multiple users with the same plaintext password from having the same salt. Encryption instead of hashing is the correct answer here because you need the plaintext of the password to perform the automated operation.

As for ENCRYPTBYPASSPHRASE(), it’s okay if you’re running SQL Server 2017 or later. For 2016 and earlier, it uses Triple DES with 128 bit key length and that’s no good.

I’d also look into the Powershell SecretStore module and possibly integrate into an existing key vault if you have one.

Comments closed

SQL Server on Linux and the Built-In Administrators Group

Published 2023-12-18 by Kevin Feasel

Andrew Pruski goes messing around:

When I first started working with SQL on Linux one of the first things I did was to remove the default the [BUILTIN\Administrators] login. This is pretty much standard practice with SQL on Windows as we don’t want administrators on the server to have automatic sysadmin rights into the SQL instance.

But this login makes no sense on Linux as there is no administrators group, so it should be dropped…right?

Andrew then goes on to show us why that’s not right. Read the whole thing.

Comments closed

Security in Microsoft Fabric

Published 2023-12-12 by Kevin Feasel

Alex Lisboa-Wright talks security:

In Fabric, the basic logical structure for data services is the Workspace, in which users can create Items, which are the various resources that perform all the data operations available in Fabric, such as Lakehouses, pipelines, machine learning models and so on. Each workspace is a self-contained data storage and development environment, whose user access is controlled by both workspace admins and member users. User access controls include options to manage users’ workspace roles, which determine the permissions assigned to each user. Security permissions can be managed on the workspace and item levels in the Fabric UI. MEID authentication can also be employed within Fabric, as connecting Fabric items to other Azure resources requires MEID. MEID’s Conditional Access feature can also be configured for use in Fabric (see this documentation for best practice for Fabric resources linking to other Azure services).

Read on to learn more. Fabric is a broad set of tools and technologies, so security is both important and definitely non-trivial, even when you consider that it is a software-as-a-service offering and therefore doesn’t have much going on with user-facing networking or infrastructure security.

Comments closed

Databricks Security Analysis Tool

Published 2023-12-11 by Kevin Feasel

Advait Bhadane takes a look at a tool:

In today’s data-driven world a cutting-edge platform is required that seamlessly integrates with the cloud, embraces open-source innovation and prioritises robust data security. Databricks is a pioneer in this field. Not only does it provide a unified lake house platform, but it takes data protection to the next level with its Security Analysis Tool (SAT).

In this blog, we will unravel the power of Databricks’ SAT, focusing on the pivotal role it plays in generating daily health reports for your workspaces. It will also walk you through the step-by-step process of setting SAT in your workspace.

Click through to see what this tool can do for you.

Comments closed