Standing up Azure SQL Managed Instance & Connect to Storage Account

This is a quick article, related to connecting an Azure SQL Managed Instance to an Azure Storage Account.

When creating an Azure SQL Managed Instance, you have the options of creating a public endpoint and/or configuring the connection type of the private endpoint (as shown below). The default connection type for private endpoint is Proxy, however, Microsoft recommends using the Redirect method.

Using Redirect will create a Network Security Group (NSG) with various security rules. In the outbound rules, I have found, at most, 2 rules need to be added, which are highlighted in the screenshot below. One rule is to allow any connection from SQL Managed Instance subnet (172.x.x.x/27 as an example) to the subnet with the primary NIC of the storage account (172.x.x.0/24). The other rule is to allow traffic from the MI subnet to the ‘Storage.EastUS’ service.

Partial listing of outbound network security group rules in Azure for a SQL Managed Instance.

More investigation needs to be completed to tighten down these outbound rules, so they target specific ports, and ideally specific IP addresses. This will evolve…

Recent and Upcoming Presentations

The first set of blogs I posted on this site center around Row Level Security in Microsoft SQL Server. In addition to these posts, I’ve decided to present the topic at PASS events, including multiple SQL Saturdays. Below are the events I’ve already presented at in 2020 and am hoping to present at later this year.

Confirmed Events

Submission-pending Events

These are wonderful opportunities for multiple reasons. SQL Saturdays are unique events, in that they are hosted by the PASS community in each city, providing a free day of training (with the exception of a small fee for lunch). How crazy is that? With each event, I’m slowly trying to improve myself in the first 3 areas. Plus, everyone needs some fun now and again.

  • Networking – A few speakers are planning to be at a number of these events, which will give me an opportunity to meet and get to know them, as well as other speakers and event attendees.
  • Building Technical Skills – Just as I’m presenting on a relatively unknown topic, Row Level Security, there are others speaking about other, lesser-known topics. Of course, many of the presentations will cover better known topics, and we’ll get different perspectives from each speaker.
  • Building Communication Skills – For a long time, I had was quite terrified of public speaking, which has likely had a negative impact on my career. By “practicing” my public speaking skills, I can only improve, and this will help further my career, especially if I want to go into a management or product evangelist-type roles.
  • Seeing Different Cities – Who doesn’t like to check out different and see what they have to offer. Having a day or two free in each city will give me an opportunity to see what each has to offer.

Restricting Data Access with Row Level Security – Part 3

To this point in the series, the examples we’ve used are limited to the single Azure database for the Endless Timekeeping application. It’s great to define the concepts, but for an enterprise application, a few more tools need to be added to the tool belt. Endless Reporting isn’t large enough to really have good enterprise scenarios, yet, so we’ll rely on ACME Corporation, made famous by Wile E. Coyote. ACME has several divisions including Recreational Gear, Physical Security and Explosives, who receive materials and subcomponents from their vendors ACME Skates, ACME IronWorks and ACME Gun Powder, respectively. Each division produces their unique products, manage inventories, etc. The corporate accounting department is responsible for the finances of all their divisions. With the implementation of Just-in-Time inventory, the vendors have to be responsive to ACME’s needs.

Extending the Limits of Row Level Security

Mapping Users to Divisions

ACME is fortunate to use an Enterprise Resource Planning (ERP) system that allows each division to be split into separate “companies.” Knowing data security would be important (or maybe it was dumb luck) when the system was implemented, ACME did take advantage of this feature and laid out the divisions as follows.

  • 01 – Physical Security
  • 03 – Explosives
  • 04 – Recreational Gear

Of course, Row Level Security cannot be implemented in the ERP system directly, because it’s a 3rd-party application that was not designed to handle Row Level Security. However, the data is loaded into a data warehouse each day. Our examples will work with the inventory data mart.

The Accounting department, of course, has access to all companies in the system, as do all internal users of ACME Corporation. The vendors, on the other hand, should only have access to the ERP company they work with. Users from each vendor have Active Directory accounts in the ACME domain. We can take the companies and users and map them together in table named UserSecurity.CompanyUserMapping. We can continue to add users to this table, which will be time-consuming but possible. Several users are listed here.

CompanyIDADUserName
ALLACMECorpUS\AcctUser1
01ACMECorpUS\AIUser01
03ACMECorpUS\AGPUser01
04ACMECorpUS\ASUser01

In the data warehouse, there is a table, Dim.Company, that have a matching column named CompanyID. It turns out this table will play a key role with data access. A predicate function, like udf_CompanyFilterPredicate, can be created to filter Dim.Company, which is used in nearly every query against the data warehouse.

CREATE FUNCTION Dim.udf_CompanyFilterPredicate
(
    @CompanyID char(3)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
	-- Internal users who have access to all data (CompanyID = 'ALL')
	SELECT 1
	FROM UserSecurity.CompanyUserMapping m
	WHERE m.ADUserName = USER_NAME()
		AND m.CompanyID = 'ALL'

	UNION
	-- External users who have access to specific companies
	SELECT 1
	FROM UserSecurity.CompanyUserMapping m 
	WHERE m.ADUserName = USER_NAME() 
                AND m.CompanyID = @CompanyID
)

Let’s bind this function to the Dim.Company table.

CREATE SECURITY POLICY [dbo].[FilterDimCompanyPolicy] 
       ADD FILTER PREDICATE Dim.udf_CompanyFilterPredicate(CompanyID) ON Dim.Company
WITH (STATE = ON, SCHEMABINDING = ON)
GO

Assuming CompanyIDs are surrogate keys, defined as an IDENTITY, this solution won’t restrict data in the Inventory fact table, but it will be harder to determine what’s what, if the user doesn’t have access to all companies. Dim.Company is a small table, so when the table is filtered for each and every query, that filtering will be quite fast, compared to filtering DimCompanyID in the fact table. Of course, filtering Fact.Inventory would be the ideal solution, but there could be some performance issues, depending on the size of the table.

Filtering Data by Active Directory Groups

We started to key in Active Directory accounts into the CompanyUserMapping table for every user at ACME Corp. and for all their vendors’ users who need access to the data warehouse. That could be a lot of users to keep track of and a maintenance nightmare for the database administrator. A better solution might be to map the companies to Active Directory user groups, such that every member of a group has access to one or more companies. Let’s rename the table CompanyGroupMapping.

CompanyIDADGroup
ALLACMECorpUS\InternalUsers
01ACMECorpUS\ACMEPhysicalSecurityUsers
03ACMECorpUS\ACMEGunPowderUsers
04ACMECorpUS\ACMESkatesUsers

The predicate function we wrote earlier needs just a couple changes. Instead of comparing the current user’s login to the ADUserName field, the IS_MEMBER() function can be used, which determines if the current user is a member of specified database role or Active Directory (or the database server’s) group. The management of user access has moved from the DBA to the Active Directory administrators, by adding user accounts to the respective groups.

CREATE FUNCTION Dim.udf_CompanyFilterPredicate
(
    @CompanyID char(3)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
	-- Internal users who have access to all data (CompanyID = 'ALL')
	SELECT 1
	FROM UserSecurity.CompanyGroupMapping m
	WHERE IS_MEMBER(m.ADGroup) = 1
		AND m.CompanyID = 'ALL'

	UNION
	-- External users who have access to specific companies
	SELECT 1
	FROM UserSecurity.CompanyGroupMapping m 
	WHERE IS_MEMBER(m.ADGroup) = 1
                AND m.CompanyID = @CompanyID
)

These examples are not perfect, but I don’t know if there is a perfect example. However, my goal is to give you some ideas on ways to implement Row Level Security in a manner that might work for your enterprise.

In the next blog, we’ll look at some query patterns to follow and to avoid with Row Level Security. Stay tuned…

Restricting Data Access with Row Level Security – Part 2

Remain calm…Row Level Security is not that scary, if you read this blog series.

In Part 1 of this series, we introduced Row Level Security (RLS) and the table-valued function (TVF) that will be used to filter data, based on a specific user. The sample queries filtered data by cross-applying the table with the function. Manually filtering data is great, but RLS allows us to formalize the filter and be used for every query that uses the table.

Defining the Security Policy

In SQL Server, Security Policies are used to bind the TVF to one or more tables. Below is an example of a security policy in our Endless Timekeeping database.

CREATE SECURITY POLICY [App].[FilterUsersPolicy] 
ADD FILTER PREDICATE [App].[udf_UsersFilter]([UserID]) ON [App].[Users]
WITH (STATE = ON, SCHEMABINDING = ON)

Let’s create a template of the statement and break it down.

CREATE SECURITY POLICY <schema>.<name>
ADD <FILTER OR BLOCK> PREDICATE <Table-Valued Function>(<Field from table>) ON <schema>.<table>
WITH (STATE = <ON OR OFF>, SCHEMABINDING = <ON OR OFF>)

As with any SQL Server object, it needs to be named, and added to a schema, even if it’s in dbo. Next, define what type of policy is being implemented, either a filtering policy or a blocking policy. Filtering policies are used to filter SELECT statements, while blocking policies are used for INSERT/UPDATE/DELETE statements. In our case, we’re currently working with filtering SELECT statements. We’ll use the function created in Part 1 of this series, App.udf_UsersFilter, and “bind” it to the intended table, App.Users.

Finally, we need to decide whether or not to use schema binding. It is recommended to use SCHEMABINDING = ON, but it is not required. Should you choose not to use SCHEMABINDING, remember every user (or a group and/or role) will need to be given access to the predicate function, in addition to the table(s). If SCHEMABINDING is enabled, then users who have access to the table will have permission to the predicate function. For my database, I have no reasons to disable it, so I won’t and will leave it on. Once the security policy has been created in the database, our test queries from the previous blog can be modified and re-tested.

The image above has the relevant list of employees in our company, with each employee’s department name and the ID of their direct supervisor. As a refresher, or if you haven’t read Part 1 of this series, below is the predicate function we’ve defined for the App.Users table.

CREATE FUNCTION [App].[udf_UsersFilter]
(
    @UserID INT
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
    -- Allow access to the user's own record
    SELECT 1 FilterResult
    FROM App.Users u
    WHERE u.UserID = @UserID
          AND USER_NAME() = u.Username
    UNION
    -- Allow access to the user's direct reports' records 
    SELECT 1 FilterResult
    FROM App.Users u
        LEFT JOIN App.Users m
            ON u.ManagerID = m.UserID
    WHERE u.UserID = @UserID
          AND USER_NAME() = m.Username
    UNION
    -- Allow access to all users for members of the HumanResources role
    SELECT 1
    FROM App.Users
    WHERE UserID = @UserID
          AND IS_ROLEMEMBER('HumanResources') = 1
);

Our first test is with our office manager, Peggy. Her user is able to see just her record, as she is not a manager, nor is she a member of the HumanResources database role (cases 2 and 3 of the predicate function defined in Part 1).

To query the App.Users table, we can just call the table and not have to worry about the predicate function or having a WHERE clause that filters records appropriately.

EXECUTE AS USER = 'PeggyM'

SELECT u.*
FROM App.Users u

The second test case is with my database user, ErinD. In this example, my record and my direct reports’ records are returned (cases 1 and 2 in the predicate function).

EXECUTE AS USER = 'ErinD'

SELECT u.*
FROM App.Users u

From here on out, anyone who queries the table will receive a filtered list of data from the App.Users table. IMPORTANTLY, this includes database administrators. DBAs are not able to view all of the data in the table, unless they disable the security policy (not recommended) or they belong to the HumanResources database role. The “god” role of sysadmin does not take priority over RLS.

The examples shown so far have been developed after quite a bit of trial and error. In the next blog in this series, we’ll discuss the DOs and DON’Ts when it comes to Row Level Security. We will then investigate improvements to our design to better handle real-world, enterprise implementations of RLS.

Restricting Data Access with Row Level Security – Part 1

When starting a new company, and you’ve added at least one other staff member, one of the most important applications you can have is a time tracking system. Time tracking is even more important for a consulting company, so customers are billed appropriately and we get paid. Depending on the number of features of this system, there is likely to be personal information, such as hourly or monthly salary, perhaps a Social Security Number (in the U.S.), or other information that needs to be kept private for each employee.

Of course, the time tracking application will filter this data, as we build it, but we will have a lot of smart developers, who will know where the SQL server is and will likely have some level of access to it. As a result, we need to lock down the data in the database itself, not just in how the application queries and displays the data. As of SQL Server 2016, Microsoft has implemented Row Level Security, allowing DBAs to secure data, by limiting who can see specific records in a protected table.

Getting the hang of RLS took a little bit of time and experimenting. One of my sources of inspiration came from the great Steve Jones with one of his stairway articles on Row Level Security.

Components for Row Level Security

To implement Row Level Security, you will need the following objects in your database.

  • Table needing to be secured
  • Predicate function – needs to determine if the user has access to a record
  • Security Policy – ties the table(s) to a predicate function

Predicate Function

The first step is to identify a table for which to enable RLS. You may have a table identified already, as you’re thinking about RLS. In this blog, we’ll be working with the Users table, shown in the diagram above. Other blogs will work with the TimeEntries table for RLS, as that table will need to be locked down.

The next step is to create a predicate function. This is a table-valued function that needs to return 1 value to show the user has access to the requested record in the table. Through the use of the security policy, which we’ll cover in a later blog, this function will be applied automatically to every query that calls the table…regardless of user or level of access to the database.

To filter the Users table the way we want, at least initially, the table-valued function below will fit the bill. The function has 3 union’d queries, each returning the value “1” for records a particular user has access to.

CREATE FUNCTION [App].[udf_UsersFilter]
(
    @UserID INT
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
    -- Allow access to the user's own record
    SELECT 1 FilterResult
    FROM App.Users u
    WHERE u.UserID = @UserID
          AND USER_NAME() = u.Username
    UNION
    -- Allow access to the user's direct reports' records 
    SELECT 1 FilterResult
    FROM App.Users u
        LEFT JOIN App.Users m
            ON u.ManagerID = m.UserID
    WHERE u.UserID = @UserID
          AND USER_NAME() = m.Username
    UNION
    -- Allow access to all users for members of the HumanResources role
    SELECT 1
    FROM App.Users
    WHERE UserID = @UserID
          AND IS_ROLEMEMBER('HumanResources') = 1
);

We’ll test each section of the function, running them as different users. Currently, we have three users we’ll test. In the first case, Peggy is our “office Mom,” or office manager, who keeps everyone in line but she doesn’t have any direct reports. The query returns just her record User record.

EXECUTE AS USER = 'PeggyM'

SELECT u.* FROM App.Users u
CROSS APPLY App.udf_UsersFilter(u.UserID) p

REVERT

I’m the President of the company, and Peggy and Jill report directly to me. Running the query with my login, three records are returned, my own record, as well as Peggy and Jill’s records. Sections 1 and 2 of the function are being tested.

EXECUTE AS USER = 'ErinD'

SELECT u.* FROM App.Users u
CROSS APPLY App.udf_UsersFilter(u.UserID) p

REVERT

The final test is for our Human Resources team, and Sarah Jones in particular. As a member of HR, she has the ability to see all employees, because she’s a member of the HumanResources database role.

EXECUTE AS USER = 'SarahJ'

SELECT u.*
FROM App.Users u
CROSS APPLY App.udf_UsersFilter(u.UserID) p

REVERT

To this point, we’ve written our filtering predicate function for the App.Users table. This is great, but we shouldn’t have to worry about this function and applying it each and every query that hits the Users table. Besides myself, as the developer of the function, any other developers don’t need to be aware of the function, but we need to filter the table each and every time.

Tying the function to the table will be in the next blog. Stay tuned…

Getting Started

As Endless Reporting gets up and running, we will be sharing tips and techniques we’ve found to be effective, related to Microsoft SQL Server, Azure and related technologies. Whether it’s for an “internal application” or for a “customer’s project”, we will share patterns and code snippets that were useful to us.