Press "Enter" to skip to content

Category: Security

Ownership Chaining in SQL Server

Published 2020-04-22 by Kevin Feasel

K. Brian Kelley walks us through the concept of ownership chaining in SQL Server:

Ownership chaining is a security feature in SQL Server which occurs when all of the following conditions are true:

– A user (which could be an app through a login/service account) tries to access an object that makes a reference to another object. For instance, the user tries to execute a stored procedure that accesses other objects or a SELECT from a view that accesses other tables.
– The user has access to the first object, such as EXECUTE rights on the stored procedure or SELECT rights on the view.
– Both objects have the same owner.

In this case, SQL Server will see the chain between the object the user called and the object being referenced. SQL Server will also determine that the owner for both objects is the same. When those conditions are met, SQL Server will create the ownership chain.

Read on for an in-depth example of ownership chaining and how it solves certain problems around managing database rights.

Comments closed

Auditing Logons Using Extended Events

Published 2020-04-21 by Kevin Feasel

Jason Brimhall dumps a server-side trace in favor of extended events:

Some time ago, I wrote an article for SQL Server 2008 to help determine the use of the server since SQL Server 2008 was reaching End Of Life. In that article, I shared a reasonable use of server side trace to capture all of the logon events to the server. Afterall, you have to find out the source of connections and who is using the server if you need to migrate it to a newer SQL Server version. You can read that article here.

Soon after, from various sources, I received requests on how to perform a logon audit using the more preferred, robust, venerable, awesome tool called Extended Events (XEvents). In response, I would share a login audit session to each person. In this article, I will share my login audit solution and give a brief explanation. I use a solution like this on more than 90% of my client servers and I find it highly useful.

Click through to see how.

Comments closed

Power BI & Disabling Export to Excel

Published 2020-04-13 by Kevin Feasel

Marc Lelijveld explains why you might not want to let users export to Excel:

Export to Excel is a feature in Excel which is available in Power BI for a very long time. It allows report users to export the data from a specific visual in the report to an editable Excel file. After exporting, they can do whatever they want. For example, sending the data to others via mail, transforming or manipulating the data, start building new reports based on the Excel file and many other things. The export option can be used by clicking the ellipsis on the right top of a visual (if the visual header is enabled).

If you have all export functionalities enabled, users can both export underlying data and summarized data. The difference is mainly raw data or only data as visible in the chart where you clicked the export button.

Read on to understand why this might not be an unalloyed good.

Comments closed

Checking Login Usage

Published 2020-04-10 by Kevin Feasel

Kenneth Fisher checks a box I really like checking:

I get asked this every now and again, along with the companion When was the last time this login was used? It’s a pretty easy question to answer but there are some caveats. First of all you need to have your system set to log both successful and failed logins. You can probably get away with successful only but personally I want to know a failed attempt just like I’d want to know a successful one.

This is a thing that we tend to avoid because of how many events it adds to the Security event log, but is critical in understanding whether that person trying to log in as sa gave up or stopped due to a successful login.

1 Comment

Power BI Row-Level Security

Published 2020-04-07 by Kevin Feasel

Tomaz Kastrun shows us row-level security in Power BI:

Row -Level Security or managing roles in Power BI is not something new. But environments, where there is a need for securing read access for end-users based on their account name, are very frequent. Row Level Security is omitting and controlling access to a user or group (or distribution group in active directory) to rows on a single dataset (or table in SQL Server) and all the relationships to this dataset.

There is a performance cost to this, but if you need it, it’s there. Power BI row-level security can also work with Analysis Services row-level security and (to an extent, and this is new) SQL Server row-level security.

Comments closed

Accessing Blob Storage from Azure Databricks

Published 2020-04-02 by Kevin Feasel

Gauri Mahajan shows how we can read data in Azure Blob Storage from Azure Databricks:

Since our base set-up comprising of Azure Blob Storage (with a .csv file) and Azure Databricks Service (with a Scala notebook) is in place, let’s talk about the structure of this article. We will demonstrate the following in this article:

1. We will first mount the Blob Storage in Azure Databricks using the Apache Spark Scala API. In simple words, we will read a CSV file from Blob Storage in the Databricks
2. We will do some quick transformation to the data and will move this processed data to a temporary SQL view in Azure Databricks. We will also see how we can use multiple languages in the same databricks notebook
3. Finally, we will write the transformed data back to the Azure blob storage container using the Scala API

It’s just a few lines of code. One of the best things Microsoft and the Databricks team did for Azure Databricks was to ensure that it felt like a first-party offering—everything feels a little more integrated than Databricks for AWS.

Comments closed

Which Groups can Set Permissions in Power BI

Published 2020-04-02 by Kevin Feasel

Gilbert Quevauvilliers walks us through the groups which can set permissions in Power BI:

As you can see from above it is good to know which groups can be used to assign permissions in the Power BI Service.

If there is anything I have missed, is wrong or needs updating please let me know via the comments section below.

Thanks for reading!

Gilbert has a nice matrix as well as lots of screenshots establishing the matrix’s veracity.

Comments closed

Azure AD Passthrough and Password Hash Authentication in SQL DB, DW, MI

Published 2020-04-02 by Kevin Feasel

Mirek Sztajno announces two new security pieces for Azure SQL Database, Azure Synapse Analytics, and Azure SQL Managed Instances:

We are announcing support for Azure AD pass-through and password hash authentication for Azure SQL DB (single database and database pools), Managed Instance, and Azure Synapse (formerly SQL DW).

– Azure AD password hash authentication is the simplest way to enable authentication for on-premises Active Directory users in Azure AD. Users are synchronized with Azure AD and password validation occurs in the cloud using the same username and password that is used in on-premises environments. No additional infrastructure is required.

– Azure AD pass-through authentication provides a password validation mechanism that validate users directly with on-premises Active Directory, outside the cloud. Pass-through authentication does not require ADFS or other third-party federation services.

– Each of these authentication methods can be configured by Azure AD Connect, allowing you to provision users in the cloud.

Read on to see what this means for you.

Comments closed

Data Exfiltration Protection when Using Azure Databricks

Published 2020-03-31 by Kevin Feasel

Bhavin Kukadia, et al, explain how to prevent users from taking data from your Databricks cluster without authorization:

Solving for data exfiltration can become an unmanageable problem if the PaaS service requires you to store your data with them or it processes the data in the service provider’s network. But with Azure Databricks, our customers get to keep all data in their Azure subscription and process it in their own managed private virtual network(s), all while preserving the PaaS nature of the fastest growing Data & AI service on Azure. We’ve come up with a secure deployment architecture for the platform while working with some of our most security-conscious customers, and it’s time that we share it out broadly.

Click through for the architectural pattern.

Comments closed