Press "Enter" to skip to content

Category: Security

Security Changes in ML Services

Published 2020-02-24 by Kevin Feasel

Dennes Torres goes over some of the security changes with Machine Learning Services in SQL Server 2019:

I have a confession to make. Why, in my last article about shortest_path in SQL Server 2019, have I used Gephi in order to illustrate the relationships, instead of using a script in R for the same purpose and demonstrate Machine Learning Services as well?

The initial plan was to use an R script; however, the R script which works perfectly in SQL Server 2017 doesn’t work in SQL Server 2019.

The change is a positive one from the standpoint of security, but it also makes life more difficult. I found this particularly tricky when installing TensorFlow and Keras in R via ML Services.

Comments closed

SQL Server 2019 and sys.syslogins Changes

Published 2020-02-24 by Kevin Feasel

Taryn Pratt goes into a change in the sys.syslogins system view in SQL Server 2019:

Sigh ok, something is really broken because this was working before we failed over.

The code for the login replication basically does the following via a cursor (yeah, I know, but it works…normally):

1. Select from the primary via OPENQUERY to query the logins and passwords
2. Using sp_hexadecimal convert the varbinary password to a string value
3. Create a string to be executed, i.e. dynamic SQL that runs a CREATE LOGIN

Read on for the whole story and how you can protect yourself as you upgrade to SQL Server 2019.

Comments closed

Tying a Database User Back to a Login

Published 2020-02-18 by Kevin Feasel

Dave Bland shows how to figure out which database users tie back to which logins:

Over the years I have had to provide information about logins and database users, most of the time per a request of an auditor.  Many times this is very easy to accomplish because the login name matches the name of the database user account. If you look at the “New User” screen you can see that I am able to enter a different User Name.

Because of this, I can have a User Name that doesn’t match the Login Name.  From an audit perspective this can create some confusion.  More importantly, it can make it difficult to provide accurate information to the auditors when asked.

Read on to see how you can tie these together even if the names don’t match.

Comments closed

Row-Level Security in Power BI Reports

Published 2020-02-07 by Kevin Feasel

Drew Skwiers-Koballa shows how to use an embed token to implement row-level security with Power BI:

To present a PowerBI report user or consumer with a securely pre-filtered dataset, row level security must be used. In a PowerBI embedded architecture where “app owns data”, implementing row level security (RLS) requires a modification to the token generation request. By specifying a role and user in the token request, we can generate an embed token specific to the user’s data access.

Click through for the instructions.

Comments closed

Data Lake File Formats and Security

Published 2020-01-31 by Kevin Feasel

Ashish Kumar and Jorge Villamariona continue a series on data lakes:

People from a traditional RDBMS background are often surprised at the extraordinary amount of control that data lake architects have over how datasets can be stored. Data Lake Architects, as opposed to the Relational Database Administrators, get to determine an array of elements such as file sizes, type of storage (row vs. columnar), degrees of compression, indexing, schemas, and block sizes. These are related to the big data oriented ecosystem of formats commonly used for storing and accessing information in a data lake.

It is a bit of a different world and it comes with trade-offs. The whole thing is worth reading.

Comments closed

Features for Securing Data in SQL Server

Published 2020-01-29 by Kevin Feasel

Jonathan Kehayias gives us a set of features we can use to secure data in SQL Server:

Recently I have been having quite a few discussions around security of data inside of SQL Server and how to prevent the massive data breaches that we have been hearing about on the news.  Like most things some people want “THE ANSWER” or THE SOLUTION to securing the data inside of SQL Server. Unfortunately there isn’t a single solution that solves all of the problems that are potentially out there.  Security of data requires defense in depth, starting with a secure configuration and installation of SQL Server.  Often times, defense in depth also means changes to the database, the application, and how you do business.

Also read the comments to understand why Dynamic Data Masking and Row-Level Security aren’t really security features. I totally agree that Dynamic Data Masking is not a security feature; Row-Level Security I’d argue is, but is quite limited in its scope, much more so than any of the features Jonathan lists.

Comments closed

Managed Identity with Azure Functions

Published 2020-01-28 by Kevin Feasel

Taiob Ali shows how you can safely store credentials which your Azure Function apps need:

With the announcement of Powershell support in Azure Functions, it has become easier for data professionals to use functions to manage cloud resources such as Azure SQL Database, Managed Instances. A common challenge when using functions is how to manage the credentials in function code for authenticating databases. Keeping the credentials secure is an important task. Ideally, the credentials should never appear in the code or in the source control.

Manged Identity can solve this problem as Azure SQL Database and Managed Instance both support Azure AD authentication. You can read mode about Managed Identity here.

In this article, I will show how to set up Azure Function App to use Managed Identity to authenticate functions against Azure SQL Database.

The example connects Azure SQL DB, but this is a general-purpose solution.

Comments closed

Auditing Login Events Using Service Broker

Published 2020-01-27 by Kevin Feasel

Max Vernon takes us through using Service Broker to audit login events:

Logging to the SQL Server Error Log or the Windows Security Event Log means you’ll need some kind of tool to slice-and-dice the data, postmortem. It’s difficult to respond to events as they happen with this kind of auditing, and hard to create simple T-SQL queries to inspect the data. You could create a login trigger at the server level, but that will only allow you to capture successful logins. Coding the trigger incorrectly can result in everyone being locked out of the server. You’ll need to use the Dedicated Administrator Connection, otherwise known as the DAC, to login to the server and disable the errant trigger. Not fun.

Luckily, there is a much better option; using SQL Server’s built-in Event Notification service to receive login events through Service Broker. This event stream is asynchronous to the login process, meaning it won’t interrupt or slow down the login process, and it allows you to capture both successful and failed logins to a table, either locally or remotely. For larger SQL Server infrastructures, it’s not uncommon to setup a single SQL Server instance to gather this information for central analysis.

This blog post shows how to setup a database locally for auditing login events via SQL Server Event Notifications and Service Broker.

Click through for a script-heavy post which helps you all the way through the process.

Comments closed

2 New Undocumented Items in SQL Server 2019

Published 2020-01-27 by Kevin Feasel

Solomon Rutzky takes us through a couple of undocumented additions to SQL Server 2019:

SQL Server 2019 introduced, among other things, two new filesystem-related items:

1. a system stored procedure, sys.xp_delete_files(), and
2. an instance-level configuration option, 'allow filesystem enumeration'

Both are undocumented, so let’s see if we can figure out what they do.

Click through to see how both work.

Comments closed