Press "Enter" to skip to content

Category: Security

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

TDE and Credential Rotation for Arc SQL Managed Instance

Published 2023-07-20 by Kevin Feasel

Nikita Takru announces a new public preview:

We are thrilled to announce the Public Preview of Transparent Data Encryption (TDE) and Service-Managed Credential Rotation for Arc-enabled SQL Managed Instance. With a strong focus on data security and management, this release introduces cutting-edge features that ensure your sensitive information is protected.  

Click through for more details, particularly on automating credential rotation.

Comments closed

CREATEROLE in Postgres

Published 2023-07-20 by Kevin Feasel

Muhammad Ali creates a role:

PostgreSQL is a robust open-source relational database management system that provides a wide range of capabilities to guarantee safe and effective data administration. One such privilege is the CREATEROLE privilege, which is vital to PostgreSQL database management of users and roles. We will examine the nuances of the CREATEROLE privilege, its use, and how it affects user administration in this blog post. So let’s investigate this privilege and comprehend PostgreSQL’s use of it.

Click through to see how this privilege works and what has changed in different versions of Postgres.

Comments closed

Finding Orphaned Users in SQL Server

Published 2023-07-20 by Kevin Feasel

Kenneth Fisher needs more factory workers:

When you create a user (a database principal) you have several options on what the user is associated with (usually a login/server principal), or it might not be associated with anything at all (created without a login). And a common problem is when that object you’ve associated your user with is no longer available and you’ve got an orphan.

Click through to see how Kenneth finds them all and has them working in his wallet-making factories. Kenneth is an inspiration to us all.

Comments closed

Migrating Column-Level Encryption to Azure SQL MI

Published 2023-07-19 by Kevin Feasel

Keshav Kiran performs a migration:

One of our customers came up with a requirement where they wanted to Migrate On-prem Database to Azure SQL Managed instance. The databases had traditional column level encryption enabled.

He has restored the database on the SQL Managed instance by Backup/Restore approach. Now when he was trying to read the encrypted column on the destination database, It was showing NULL values after decryption.

Read on for the solution.

Comments closed

Taking Over a Power BI Dataset with a Service Principal

Published 2023-07-18 by Kevin Feasel

Angela Henry takes it out of the user’s hands:

A little background for those new to using Power BI and Data Gateways. If the data source for your Power BI dataset lives on-prem or behind a private endpoint, you will need a Data Gateway to access the data. If you want to keep your data fresh (either using Direct Query or Import mode), but don’t want to rely on a specific user’s credentials (because we all want to go on vacation at some point), you will need to use a service principal for authentication.

Read on for the step-by-step instructions on how to do this.

Comments closed

Security Concepts: Who? and Where?

Published 2023-07-14 by Kevin Feasel

Kenneth Fisher is trying to figure out where he left his keys:

I was having a conversation with some friends the other day and Jen McCown (blog|twitter) asked about SQL Server security references and “What’s something that’s really difficult in SQL Server Security.” As happens sometimes I started thinking about this in the back of my head and I realized something. The two absolute hardest things that people run into with security (at least in my ever so humble opinion) are

  • Who: or Who am I when I try to access a resource?
  • Where: or Where am I when I try to access a resource? And Where is that resource?

Read on for Kenneth’s thoughts.

Comments closed

Users and Role Members for Azure SQL Databases

Published 2023-07-13 by Kevin Feasel

Peter Schott makes a list:

I ran into a concern to quickly audit all current users and role members for a set of Azure SQL databases, spread across multiple resource groups. Without an easy CMS concept or a way to quickly loop through an unknown set of servers, resource groups, and databases, that can be a little challenging. I have an account to use that should have access to all databases (but doesn’t) so put together some PowerShell that I could run locally to get that information and send the results to Excel.

Click through for a SQL script to get the data and a Powershell script to run this for each database and export the results into different tabs in Excel.

Comments closed