SPEAKER_00 0:06

Welcome

Welcome And The Security Goal

SPEAKER_00 0:07

to Microsoft Community Insight Podcast where we share insights from community experts to stay up to date with Microsoft. In this podcast, we will dive into different ways to secure fabric as well endpoint.

Meet John Minor

SPEAKER_00 0:19

Today we have a special guest called John Minor. Could you please introduce yourself?

SPEAKER_01 0:24

Yeah. Hey Nicholas, thank you for having me. My name's John Minor. I'm a senior data architect at Insight, which is a US company. I've been a data platform for 10 years, MVP. And today we're going to be talking about securing the Fabric SQL analytic endpoint. And if you're curious, you can get in contact with me at john.miner at insight.com. Also, I'm an avid writer and learner, lifelong learner. You can actually see my blogs on fabric on SQL Server Central. Uh Steve Jones has been gracious enough to publish them. And any of the content, including today's slide deck and code, will be published on my GitHub repo. And that'll be John Minor and then number three, because I'm the third in my line. So thanks again for having me, Nicholas.

SPEAKER_00 1:15

Okay, no worries. So before we get started, do you want from Chris?

Becoming A Data Platform MVP

SPEAKER_00 1:20

Do you want to tell us a bit about like how you get started as a data platform? Like how what interesting about data to become an MVP?

SPEAKER_01 1:30

Sure, interesting. Um, you know, I've always been into computers. My undergraduate was in computer science applied math. I uh initially started doing uh computer electrical engineering, but I found I loved more compilers and languages and databases than that. As for an MVP, I was lucky enough. I was invited from uh a company called uh that mates web focus, and they sent me to a user group, which was being run by Grant Fritchie at the time, and it was Rhode Island SQL Server Users Group, and uh we hit it off, and so he was transitioning to Red Gate at the time, and they needed a leader, and I picked it up, and I started blogging and helping the community and the rest is history, and so uh yeah, I was gracious enough to be adopted to the and water to the MVP program, and it's a great program to be there. There's a lot of professionals who are like mind, who like learning and teaching, and um, you know, I'll definitely be sad one day in which you know I will no longer be a part of that group. I'm sure eventually I will retire in my life, but uh I see that maybe five to ten years away, not uh right away, so thank God.

User Groups And Community Learning

SPEAKER_00 2:46

Okay, do you want to tell us a bit more about user group? Is it um online or is it in person?

SPEAKER_01 2:51

Yeah, good question. If you look at if you type a Google Rhode Island, so RI and then data platform user group, we call it DPUG. It's a meetup page. Uh this month, Christian Coty, he's a friend of mine from Canada, will be talking about fabric and how to get uh data into fabric. Stop from the top.

SPEAKER_00 3:13

Yeah, that's fine. I can cut it off.

SPEAKER_01 3:16

Awesome.

The Security Topics Roadmap

SPEAKER_01 3:17

Good afternoon. My name's John Minor, and I'm a data MVP. I work for a company called Insight that's in the US, and today I'm going to be talking about securing the Fabric SQL analytic endpoint. Connectivity, road level security, column level security, and data masking. Okay, let's get into the objects that can be secured.

Fabric Objects And Read Only Endpoints

SPEAKER_01 3:42

There are many different types of objects in the fabric ecosystem. Today I'm going to focus on the lake house, the mirrored database, the SQL database, and the warehouse. The interesting thing is that three of the four have read-only endpoints. The SQL database actually is duplicated. There's one copy which is really OLTP, and there's another copy that's OLAP, which is a duplication of the data into Delta files. That main point, which is the database, is read-write. Same thing with the warehouse. There is no duplication because you're already in Delta Lake, but it's read write. Okay. The last two objects that a lot of people play with are KQL and Symantic. Both of those do not have SQL analytic endpoints. Okay.

What A SQL Analytic Endpoint Is

SPEAKER_01 4:27

So let's talk about what is a SQL analytic endpoint. And I kind of told you a little about it. The purpose of a SQL analytic endpoint is to allow people to query delta tables using the TSQL, Transact SQL language. Okay, that's Microsoft's language. TSQL commands can be defined in query objects, but not manipulate and modify the data. You can create functions, views, and implement object level security. And that's what we're going to talk about today. By default, the endpoint is case sensitive. So I suggest using the workspace settings to change this before you deploy anything. Next

Connectivity With TDS And SSMS

SPEAKER_01 5:09

one, connectivity. To connect to the SQL endpoint, we have to understand the protocol. The protocol is called the Tabula DataStream TDS and it's a proprietary application layer protocol used primarily by Microsoft SQL Server and Sybase. So this has been around a long time since I've been playing with SQL Server since 1996.

SPEAKER_00 5:31

Wow.

SPEAKER_01 5:32

This can be done either with the fabric or the graphical user interface. Today we're going to use SSMS for our journey.

SPEAKER_00 5:40

What does SSMS mean?

SPEAKER_01 5:42

Yep, SQL Server Management Studio. So we're going to get into that. So

Row Level Security For Departments

SPEAKER_01 5:47

row level security. What do you do? We want to allow only certain groups to have access to selected rows in the table. Row level security enables you to use your group membership and execute to context to control access to rows in the database table. For instance, you can ensure that works access only data rows that pertain to the department. So we're going to talk about sales today, okay? And we're just going to be three different users. We're going to talk about John. My middle name is Francis, so it's going to be Francis and Robin. Okay. And this can be done with again fabric and uh user interface. We'll get back to this. I'm going to go through all the slides because we're doing stream and I want to make sure I get those right and then we'll do the demos. Okay. Next one.

Column Level Security And Grants

SPEAKER_01 6:35

Column level security. Well, column level basically is we want to hide some columns from a user, okay, how to protect sensitive data. So instead of masking it, which would you know cover the numbers, like maybe Social Security and show the last four, or say a value, say like uh total sales, we saw a zero with column level security restricting them from actually even using it. Okay. Now, when you add people to any of these objects, they have to be part of the workspace and there's different roles, right? You have admin, member, contributor, and viewer. Okay, for column service uh level security at work, you have to use viewer. Not only that, Microsoft's flip-flop back and forth on how this was going to implement. Initially, it was going to be a grant and just the columns, but now it's a grant and a deny. Okay, so we'll talk about how uh AI got it wrong. Okay. Dynamic

Dynamic Data Masking Basics

SPEAKER_01 7:35

data masking. Okay, we want to allow certain groups to see full context of selected files in a table. While others can't see it, it'll be masked, okay? And so we'll talk about how we can implement that. And again, this can be done with both the graphical user interface and SQL Server Management Studio.

Direct Lake Fallback And Trade Offs

SPEAKER_01 7:55

There's some caveats to anything, right? One of the things that came out recently when Microsoft uh released Fabric about a year ago was Direct Lake. And Direct Lake is this high performance storage mode, and what happens is anytime you write to the Delta files in the lake, it automatically replicates that into the Power BI semantic model, which is your um X velocity engine, right? Vertipack engine sitting in there and it's column store. And the problem is when we start using SQL row-based security like grants denies, or we base things on views, Power BI is gonna fall back from direct lake to direct query. Okay, so if things start going slower, you know exactly why. And there's always trade-offs in life, right? So you have to talk to your business use and say, hey, you can either have the security on the endpoint, or you can push the security where the end user is, maybe Power BI. But again, you have to make those decisions.

Key Takeaways And Closing

SPEAKER_01 8:57

Last but not least, and talk about summaries, right? There is a fabric analytic endpoint for four of the six main products in the system. As a designer, you can use custom views to you know manage data to create or translate it, row level security, column level security, and data masking, okay, to secure the endpoint. Okay. If you're a big fan of direct lake mode, then you have to understand that it might fall back to direct query, okay? And it's up to the business to dictate if the security is applied at the endpoint or at the serving layer, such as Power BI. Okay. Worry about column level security with Power BI. You might want to use the view. There's data masking, and guess what? If you use a replication like uh open mirroring, things might take longer than you think. So again, Nicholas, thank you for having me. I appreciate it.

SPEAKER_00 9:45

Okay, no worries. So thanks a lot for joining this episode, uh, John. So hopefully everyone knows how important it is to secure your uh Fabric SQL and how you can and you learn something about debt replication. So exactly.

SPEAKER_01 10:02

Just a little to show you that it takes a while for it to happen. Um, you know, it's definitely one of those things, and yeah, please uh edit it because uh, you know, real-time demos are tough.

SPEAKER_00 10:14

Yeah, no worries. Thanks a lot.

SPEAKER_01 10:16

Bye. Thank you, Nicholas.