Press "Enter" to skip to content

Category: Security

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

Connecting to Power BI as a Guest User

Published 2024-09-20 by Kevin Feasel

Koen Verbeeck can only enter a tenant with explicit permission:

Sometimes your Microsoft Entra ID account (formerly known as Azure Active Directory) is added as a guest user in another tenant. This happens quite a lot when you’re a consultant and your client can’t create a new user in their own tenant, so they add the account of your own company as a guest instead. If you’re not a consultant, it can also happen after a merger or acquisition and you’re suddenly stuck with multiple tenants.

Yeah, this is a real annoyance with Microsoft Fabric / Power BI. Koen links to a 5-year-old feature request that I recommend upvoting.

Comments closed

Using Managed Identities in Azure Logic Apps

Published 2024-09-19 by Kevin Feasel

Koen Verbeeck doesn’t want to change a password yet again:

A stored procedure is executed on an Azure SQL Database. The connection to this database was configured using SQL Server Authentication. The goal of this article is to show you how you can connect using managed identities instead, which was left as an exercise to the reader in the previous article.

I recommend you to go through this article first if you don’t have a solid understanding of Logic Apps, or if you want to follow along as an exercise. It’s not necessarily a prerequisite to understand the concepts of this article and if you’re just interested in learning how managed identities work for Logic Apps, then keep on reading.

Click through to learn more about managed identities in Azure and how they can be so useful.

Comments closed

Working with Always Encrypted Data in SSIS

Published 2024-09-17 by Kevin Feasel

Rod Edwards continues a series on Always Encrypted:

So now, lets see how it plays with another one of those common toolsets that you may use alongside your Encrypted data. In this post, i’ll be talking about accessing and importing data using SSIS, nothing fancy, just reading data from an Excel sheet, and piping into our Always Encrypted table, encrypting as we go.

I’m not saying to use Excel for housing confidential data either!… as no one does that…oh no, not anywhere, ever….</sarcasm>.

As previously, this focuses on using Azure Key Vault for securing Encryption keys required.

Considering that all corporate data is in Excel someplace (some variant of which may eventually become Feasel’s Second Law), of course that sensitive and confidential data will be in a plain Excel file that people e-mail around.

Comments closed

A Reminder for Server Consistency

Published 2024-09-12 by Kevin Feasel

Chad Callihan resolves an issue:

I connected to the latest SQL Server, opened SSMS, and tried to restore from there. Sure enough, I was presented with the error:

Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists.

If you know that the service account can access a specific file, type in the full path for the file in the File Name control in the Locate dialog box.

Read on for the solution, which was easy enough, but serves as a reminder that having (and occasionally running!) idempotent configuration scripts can be quite useful.

Comments closed

Reading Always Encrypted Data in Power BI

Published 2024-09-11 by Kevin Feasel

Rod Edwards wants to make use of encrypted data:

This is where things start to get a little more interesting compared to Pt1, as now we have a different application in the mix for reading the data. So how can that application retrieve the key needed to successfully decrypt?

Read on to see how it all works. There are a lot of working parts here, though some of it pertains to using an on-premises gateway versus Always Encrypted as such, so you get even more bang for your buck.

Comments closed

Granting Entra ID Guest Users Access to a Semantic Model via Excel

Published 2024-09-05 by Kevin Feasel

Gilbert Quevauvilliers notes an implication:

Yes, you read that right, it is possible where you have shared Power BI semantic model with an external (guest user in Entra ID) they can now connect to the semantic model using Excel.

This has been around for quite a while I just did not know about it, here is the Microsoft documentation: Semantic model connectivity and management with the XMLA endpoint in Power BI – Power BI | Microsoft Learn

The first thing to note is that this currently only works in the Excel Desktop App.

I did try using Excel on the Web and unfortunately that did not work.

I then created my Excel file, uploaded to SharePoint Online and tried to interact with the Excel Pivot table and that too did not work.

Ok enough of the limitations let me show you how to get it working.

Click through to see how. Gilbert also has an important note about row-level security, co read the whole thing.

Comments closed