Press "Enter" to skip to content

Category: Security

Capturing SQL Server Audit Events with Azure Monitor

Published 2022-01-14 by Kevin Feasel

Bruno Gabrielli connects Azure Montor to SQL Server Audit:

Today I am going to cover an interesting aspect on how to capture security audit events from both Azure and non-Azure SQL Server machines. Most of you probably know that SQL Server is capable of auditing security related information, such as access to a given database, record creation or deletion, configuration change and so on) according to the Audit configuration applied to a given instance or database.

In this post, we will not dig into SQL Server Audit configuration or capability. We will rather explore the steps and configurations necessary to collect data using Azure Monitor.

Read on for the process. You will need the appropriate agent for this, but that agent doesn’t necessitate that your machine be in Azure.

Comments closed

Permission Requirements for Temp Tables

Published 2022-01-07 by Kevin Feasel

Jeff Iannucci looks at permissions:

Managing permissions is a constant issue for Database Administrators, but rarely do DBAs consider permissions for tempdb. Everybody’s looking for something, but how often do you get requests for “access to read and write in the tempdb database”? Like…never?

OK, but what if you were asked the subject of this post in a job interview? Even if you’ve worked with SQL Server for ages, would you know how to answer this? Moreover, would you know why the answer should give you some concern?

Read on for the answers.

Comments closed

Flexible File Components with SSIS

Published 2022-01-07 by Kevin Feasel

Bill Fellows hides SSIS DNA in a can of Barbasol shave cream:

The Azure Feature Pack for SSIS is something I had not worked with before today. I have a client that wants to use the Flexible File Task/Flexible File Source/Flexible File Destination but they were having issues. The Flexible File tools allow you to work with Azure Blob storage. We were dealing with ADLS Gen2 but the feature pack can work with classic blob storage as well. In my hubris, I said no problem, I know SSIS. Dear reader, I did not know as much as I thought I did…

Click through for a whopper of a story. But be sure to read to the very end, as you don’t want to stop at using TLS 1.0.

Comments closed

Data Exfiltration Protection and Pip

Published 2021-12-30 by Kevin Feasel

I have a post borne from frustration:

I have an Azure Synapse Analytics workspace which uses a managed virtual network and includes data exfiltration protection. I also have a Spark pool. My goal is to import a few packages and use them in a Spark notebook.

Doing so is pretty easy from the Synapse workspace. I navigate to the Manage hub and then choose Apache Spark pools from the Analytics pools menu. Select the ellipsis for my Spark pool and then choose Packages.

From there, because I plan to update Python packages, I can upload a requirements.txt file and have Pip do its job.

But then it doesn’t… Click through to learn why, as well as the workaround for this. It’s stuff like this which makes me say data exfiltration protection is a feature administrators will (mostly) like and developers will hate. Especially because there’s no obvious indicator why this was happening in the error message itself.

Comments closed

Azure App Service Source Code Breach

Published 2021-12-23 by Kevin Feasel

Catalin Cimpanu reports on a security problem:

Microsoft has notified earlier this month a select group of Azure customers impacted by a recently discovered bug that exposed the source code of their Azure web apps since at least September 2017.

The vulnerability was discovered by cloud security firm Wiz and reported to Microsoft in September. The issue was fixed in November, and Microsoft has spent the last few weeks investigating how many customers were impacted.

Click through for the full report, with the upshot that if you run Azure App Services on Linux, you were probably affected.

Comments closed

Log4j and the Microsoft Data Platform

Published 2021-12-20 by Kevin Feasel

Andreas Wolter has some guidance for us:

Microsoft published guidance regarding Log4j 2 vulnerability for customers using Azure Data services. Please find the latest information here:

Microsoft’s Response to CVE-2021-44228 Apache Log4j 2 – Microsoft Security Response Center

The published list shows affected products only. 

For SQL Server, even components which use log4j, the version is old enough that it is not affected by the series of exploits, bugs, and exploits of bugs which were introduced to try to fix the prior round of exploited bugs. The big exception is Big Data Clusters and if you happened to install log4j on your own.

Comments closed

Copy Logins between SQL Server Instances

Published 2021-12-17 by Kevin Feasel

David Alcock wants to move a login:

Migrating SQL databases is fun, depending on your definition of fun that is. The process can involve having to move things such as login details that have been around for that long that nobody has a clue what they are anymore.

With domain accounts that’s pretty straightforward, the passwords are managed in Active Directory and not held in SQL Server and it’s just a case of recreating the account on the new instance.

SQL authentication is different and migrating an account as is means you also have to recreate the password as is which could be difficult if you didn’t know what the password should be. It’s worth saying at this point that the preference should always be to use domain accounts, they’re more secure and much more manageable and migrations are ideal opportunities to refactor things to be better but for the sake of this article let’s proceed with the scenario of recreating a SQL authentication login with an unknown password, and we’ll need to get creative.

And as you’d expect, dbatools makes a dramatic appearance.

Comments closed

Microsoft.DataFactory and Storage Event Triggers in Synapse

Published 2021-12-16 by Kevin Feasel

Cathrine Wilhelmsen troubleshoots an Azure issue:

I ran into an issue today while trying to publish a storage event trigger in Azure Synapse Analytics. After publishing, I got error messages that said “failed to subscribe” and “failed to activate”. The storage event trigger had been published, but it wouldn’t start. Help!

Click through for some resources on documentation, a few things which didn’t work, and what finally resolved the issue.

Comments closed