As a PowerShell developer (yes, I consider PowerShell coding development, when it’s for pre-ETL processing), there are a handful of very useful namespaces .Net.
System.Data – “basic” data containers are classes in this namespace, include DataTable and DataSet. These are generic types, regardless of source or destination database or data source.
System.XML – If you’re ingesting XML data, there are several classes here you’ll use. It’s likely you’ll use XmlReader to stream data or XmlDocument to load a document into memory to read or manipulate.
Microsoft.Data.SqlClient – Updated version to System.Data.SqlClient, used to access SQL Server, including Azure SQL Databases and Azure SQL Managed Instances. SqlConnection, SqlCommand and SqlBulkCopy are a data developer’s best friends.
OfficeOpenXML.Core.ExcelPackage – Working with XLSX files requires an assembly found in the PowerShell Gallery, among other places. Classes in this assembly allow developers to read, update and write Excel data.
This is a placeholder and this post will be updated at a later date…
I was getting some content out in 2019 and 2020, as well as starting to hit a stride with presentations at SQL Saturdays. Then a new bug came and rocked everyone, COVID-19. SQL Saturdays disappeared, as well as gatherings in general, for the next 12 – 15 months. By not having that motivator of speaking to people about SQL Server and supporting technologies, my blogging has been on hiatus. When blogging on a topic, I try my best to get the details right, so I can hopefully help those who come across the post. As a result, I tend to spend 6 – 8+ hours crafting a single post (some of that may be a little anxiety and over-analyzing). If I perceive there may be a likelihood of views dropping, because I’m not introducing myself to people, my lazy side comes out very quickly and says, “Why bother? Let’s go watch some TV or take a nap.”
That doesn’t mean life has stood still, and I haven’t learned anything. Quite to opposite, in fact. I have been extremely fortunate to work for a growing company in an key industry in this country. While the pandemic did affect us, it was the catalyst to grow in new directions. This required the data team (of which I’m one of three co-leads) to develop new ways to absorb the growth in our data, not just in volume, but with the increase in the breadth of information we’re managing.
Over the next several months, I’ll be posting about these new adventures, generalizing the concepts for the larger audience. You should see quite a bit coming about PowerShell and pre-ETL processing. Sprinkled in will be posts about Azure Automation and Azure Data Factory. By the time I get posts caught up to July 2022, I’m sure I will have learned, or been introduced to, new topics to explore with future posts.
Yesterday (July 7th, 2022), the PASS Data Community Summit announced general sessions and speakers for the 2022 event. My session, “Power-up the Data Warehouse with Pre-ETL Processing,” was chosen. I am incredibly honored to have the opportunity to speak at the Summit, in person. I did speak at the 2020 PASS Virtual Summit, pre-recording the session, so this will be my 2nd appearance at the “main event”.
I’ll try to keep train longer, providing new content.
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…
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.
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.
Check out an older blog series written while I was at Superior Consulting Services. It was started nearly 2 years ago, but the patterns and ideas are still relevant.
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.
CompanyID
ADUserName
ALL
ACMECorpUS\AcctUser1
01
ACMECorpUS\AIUser01
03
ACMECorpUS\AGPUser01
04
ACMECorpUS\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.
CompanyID
ADGroup
ALL
ACMECorpUS\InternalUsers
01
ACMECorpUS\ACMEPhysicalSecurityUsers
03
ACMECorpUS\ACMEGunPowderUsers
04
ACMECorpUS\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…
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.
Erin Dempster 