Press "Enter" to skip to content

Category: Security

Querying Audit Log (.xel) Files in Azure SQL DB

Published 2024-10-14 by Kevin Feasel

Tanayankar Chakraborty reads an audit log:

A recent issue was brought to our attention that customers could not query .xel log files in an Azure SQL DB using t-sql command. The customers complained that when they ran the command, they received column headers but no content whereas they know that there is content in the logs because they were able to open them with SSMS using Merge Extended Event Files. Here was the T-sql command used by the customer:

select * from sys.fn_get_audit_file (‘https://mydbastorage.blob.core.windows.net/sqldbauditlogs/servername/dbname/SqlDbAuditing_Audit_NoRetention/*.xel’, NULL, NULL);

Click through for the solution, which came down to two separate issues.

Comments closed

Permissions and BACPAC Files in Azure SQL DB

Published 2024-10-10 by Kevin Feasel

Roberto Yonekawa diagnoses an error:

We had a support request where the customer was getting an error when trying to export his Azure SQL Database to a bacpac file using the SqlPackage command-line utility.

Error message:

Microsoft.Data.Tools.Diagnostics.Tracer Error: 19 : 2024-08-21T16:10:56 : Microsoft.SqlServer.Dac.DacServicesException: One or more unsupported elements were found in the schema used as part of a data package.

Error SQL71627: The element Permission has property Permission set to a value that is not supported in Microsoft Azure SQL Database v12.

Click through for the specific issue Roberto found. I’d imagine that there are other permission sets that are incompatible with Azure SQL Database and would cause this error message to pop up as well.

Comments closed

Data Masking in Snowflake

Published 2024-10-09 by Kevin Feasel

Kevin Wilkie puts on the mask:

The premise behind data masking is fairly simple – make it so that only certain people can see the data in a certain column. For example, social security numbers or credit card numbers probably shouldn’t be seen by everyone who has access to your database.

I like to set it up as follows: (Obviously, this can change depending on your business’ policies.)

  1. Admins have full access.
  2. Call Center Representatives have access to the last 4 digits of the social.
  3. All others see nothing useful.

Read on to see how. I’ll even save my normal “data masking isn’t really a security feature” rant. This time.

Comments closed

Constrained Kerberos Delegation with SSRS and Power BI Gateways

Published 2024-10-07 by Kevin Feasel

Rod Edwards doesn’t want just anyone to double-hop:

Ok, many of you will already be aware that in order to use Integrated Authentication successfully with SSRS particularly, that you have to configure Kerberos Authentication. At a very basic level, this allows the credentials of the user running the report, to be passed to the report server (hop 1) and then along to the target of the SSRS datasource (hop 2), also known as “Double hop” authentication. The delegation part of this signifies where the service (PBIG or SSRS) is allowed to pass these credentials along to.

  • anywhere, ie…Unconstrained delegation, or
  • to a restricted set of targets…Constrained delegation.

Read on to see how you can set up constrained delegation.

Comments closed

Using the Log Analytics Ingestion API with Sentinel

Published 2024-10-07 by Kevin Feasel

Abhishek Sharan explains how the Log Ingestion API works when feeding data into a Log Analytics workspace:

In this blog, I’m going to delve into ingesting and transforming application logs to log analytics workspace using Log Ingestion API technique. Before we jump into the details, let’s explore the different types of ingestion techniques based on the application log types.

  • If applications and services logs information to text files instead of standard logging services such as Windows Event log or Syslog, Custom Text Logs can be leveraged to ingest such text file logs in log analytics workspace.
  • If applications and services logs information to JSON files instead of standard logging services such as Windows Event log or Syslog, Custom JSON Logs can be leveraged to ingest such text file logs in log analytics workspace.
  • If an application understands how to send data to an API then you can leverage Log Ingestion API to send the data to Log Analytics Workspace.

Custom Text/JSON Logs are out of scope for this blog, I might write a separate blog dedicated to these techniques later. In this blog, my focus will be on streaming data to log analytics workspace using Log Ingestion API and transforming the data for optimal usage.

Note: This blog aims to demonstrate how to ingest logs using the log ingestion API. To keep things straightforward, I’ll refer to our public documentation.

Click through for details on the process.

Comments closed

An Overview of Differential Privacy

Published 2024-10-04 by Kevin Feasel

Zachary Amos covers a topic of note:

Data analytics tools allow users to quickly and thoroughly analyze large quantities of material, accelerating important processes. However, individuals must ensure to maintain privacy while doing so, especially when working with personally identifiable information (PII). 

One possibility is to perform de-identification methods that remove pertinent details. However, evidence has suggested such options are not as effective as once believed. People may still be able to extract enough information from what remains to identify particular parties. 

Read on to learn a bit more about the impetus behind differential privacy and a few of the techniques you can use to get there. The real trick with differential privacy is adding the right kind of noise not to distort the distribution of the data, while still not allowing an end user to unearth enough information to identify a specific individual.

Comments closed

Managing Orphaned Users in SQL Server

Published 2024-09-30 by Kevin Feasel

Jordan Boich asks for more gruel:

Addressing orphaned users is an important piece of SQL Server security management. When left unchecked, you can accumulate an overwhelming number of users that exist in a database but do not have a correlating login in the master database, thus preventing access to the server or the database at all. There are dbatools PowerShell modules out there that can help you accomplish what sp_FindOrphanedUser does. However, there are some situations where PowerShell may not be available to you for a multitude of reasons, and having an extra tool in the toolbelt never hurt anyone, am I right?

Read on to learn about orphaned users and how the sp_FindOrphanedUser procedure works.

Comments closed

TLS 1.2 (or Later) in Azure SQL

Published 2024-09-25 by Kevin Feasel

Sakshi Gupta provides a public service announcement:

From November 1st, any Azure SQL server left with the “Select an option” or “NONE” setting (where “NONE” means no enforced minimum TLS version) will only allow connections using TLS 1.2 and TLS 1.3. Connections using TLS 1.0 or TLS 1.1 will be rejected. It is critical for all customers to configure their servers correctly and ensure that their client applications can operate with TLS 1.2 or higher.

Pretty much any SQL Server client or driver that Microsoft released from 2016 forwards will support TLS 1.2, so for most organizations, this should be as simple as enabling the option in development and ensuring applications connect as expected.

Comments closed

Security Practices for SQL Server Reporting Services

Published 2024-09-23 by Kevin Feasel

Scott Murray locks down a service:

What are the various security ramifications when deploying and managing Microsoft SQL Server Reporting Services (SSRS)? What are some of the best practices when setting up security within SSRS?

Read on to learn more about to configure SQL Server Reporting Services in three separate tools: the SQL Server Reporting Server Configuration Manager, SSRS itself, and even SQL Server Management Studio.

Comments closed