Episode 54 - Securing Fabric SQL Analytic Endpoint with John Miner

WEBVTT
00:00:06.959 --> 00:00:13.119
Welcome to Microsoft Community Insight Podcast where we share insights from community experts to stay up to date with Microsoft.
00:00:13.279 --> 00:00:19.039
In this podcast, we will dive into different ways to secure fabric as well endpoint.
00:00:19.440 --> 00:00:22.399
Today we have a special guest called John Minor.
00:00:22.719 --> 00:00:24.320
Could you please introduce yourself?
00:00:24.800 --> 00:00:25.039
Yeah.
00:00:25.199 --> 00:00:26.640
Hey Nicholas, thank you for having me.
00:00:26.800 --> 00:00:27.920
My name's John Minor.
00:00:28.239 --> 00:00:32.799
I'm a senior data architect at Insight, which is a US company.
00:00:33.359 --> 00:00:36.960
I've been a data platform for 10 years, MVP.
00:00:37.200 --> 00:00:42.079
And today we're going to be talking about securing the Fabric SQL analytic endpoint.
00:00:42.240 --> 00:00:47.200
And if you're curious, you can get in contact with me at john.miner at insight.com.
00:00:47.520 --> 00:00:52.159
Also, I'm an avid writer and learner, lifelong learner.
00:00:52.479 --> 00:00:57.679
You can actually see my blogs on fabric on SQL Server Central.
00:00:57.840 --> 00:01:01.200
Uh Steve Jones has been gracious enough to publish them.
00:01:01.439 --> 00:01:07.760
And any of the content, including today's slide deck and code, will be published on my GitHub repo.
00:01:07.840 --> 00:01:12.959
And that'll be John Minor and then number three, because I'm the third in my line.
00:01:13.120 --> 00:01:15.200
So thanks again for having me, Nicholas.
00:01:15.599 --> 00:01:16.640
Okay, no worries.
00:01:16.799 --> 00:01:20.239
So before we get started, do you want from Chris?
00:01:20.319 --> 00:01:25.280
Do you want to tell us a bit about like how you get started as a data platform?
00:01:25.439 --> 00:01:29.439
Like how what interesting about data to become an MVP?
00:01:30.000 --> 00:01:31.359
Sure, interesting.
00:01:31.519 --> 00:01:34.319
Um, you know, I've always been into computers.
00:01:34.480 --> 00:01:37.439
My undergraduate was in computer science applied math.
00:01:37.519 --> 00:01:47.599
I uh initially started doing uh computer electrical engineering, but I found I loved more compilers and languages and databases than that.
00:01:47.920 --> 00:01:51.120
As for an MVP, I was lucky enough.
00:01:51.280 --> 00:02:27.120
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.
00:02:27.199 --> 00:02:37.599
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.
00:02:37.680 --> 00:02:45.199
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.
00:02:46.000 --> 00:02:48.560
Okay, do you want to tell us a bit more about user group?
00:02:48.800 --> 00:02:50.800
Is it um online or is it in person?
00:02:51.280 --> 00:02:52.560
Yeah, good question.
00:02:52.800 --> 00:03:01.199
If you look at if you type a Google Rhode Island, so RI and then data platform user group, we call it DPUG.
00:03:01.280 --> 00:03:02.719
It's a meetup page.
00:03:02.800 --> 00:03:10.479
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.
00:03:12.719 --> 00:03:13.599
Stop from the top.
00:03:13.919 --> 00:03:14.800
Yeah, that's fine.
00:03:14.879 --> 00:03:15.680
I can cut it off.
00:03:16.159 --> 00:03:16.400
Awesome.
00:03:17.039 --> 00:03:17.599
Good afternoon.
00:03:17.759 --> 00:03:21.199
My name's John Minor, and I'm a data MVP.
00:03:21.360 --> 00:03:28.560
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.
00:03:30.400 --> 00:03:35.919
Connectivity, road level security, column level security, and data masking.
00:03:36.159 --> 00:03:40.000
Okay, let's get into the objects that can be secured.
00:03:42.240 --> 00:03:45.599
There are many different types of objects in the fabric ecosystem.
00:03:45.759 --> 00:03:52.000
Today I'm going to focus on the lake house, the mirrored database, the SQL database, and the warehouse.
00:03:52.479 --> 00:03:57.120
The interesting thing is that three of the four have read-only endpoints.
00:03:57.360 --> 00:04:00.080
The SQL database actually is duplicated.
00:04:00.240 --> 00:04:08.159
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.
00:04:08.479 --> 00:04:12.080
That main point, which is the database, is read-write.
00:04:12.240 --> 00:04:13.439
Same thing with the warehouse.
00:04:13.520 --> 00:04:18.240
There is no duplication because you're already in Delta Lake, but it's read write.
00:04:18.399 --> 00:04:18.639
Okay.
00:04:19.120 --> 00:04:23.040
The last two objects that a lot of people play with are KQL and Symantic.
00:04:23.120 --> 00:04:26.720
Both of those do not have SQL analytic endpoints.
00:04:26.800 --> 00:04:26.959
Okay.
00:04:27.360 --> 00:04:32.319
So let's talk about what is a SQL analytic endpoint.
00:04:32.560 --> 00:04:34.800
And I kind of told you a little about it.
00:04:35.120 --> 00:04:44.480
The purpose of a SQL analytic endpoint is to allow people to query delta tables using the TSQL, Transact SQL language.
00:04:44.560 --> 00:04:46.240
Okay, that's Microsoft's language.
00:04:46.560 --> 00:04:52.720
TSQL commands can be defined in query objects, but not manipulate and modify the data.
00:04:53.360 --> 00:04:57.040
You can create functions, views, and implement object level security.
00:04:57.120 --> 00:04:58.879
And that's what we're going to talk about today.
00:04:59.040 --> 00:05:01.759
By default, the endpoint is case sensitive.
00:05:02.399 --> 00:05:06.800
So I suggest using the workspace settings to change this before you deploy anything.
00:05:08.639 --> 00:05:10.480
Next one, connectivity.
00:05:10.720 --> 00:05:14.959
To connect to the SQL endpoint, we have to understand the protocol.
00:05:15.199 --> 00:05:25.199
The protocol is called the Tabula DataStream TDS and it's a proprietary application layer protocol used primarily by Microsoft SQL Server and Sybase.
00:05:25.279 --> 00:05:30.879
So this has been around a long time since I've been playing with SQL Server since 1996.
00:05:31.519 --> 00:05:32.079
Wow.
00:05:32.399 --> 00:05:36.800
This can be done either with the fabric or the graphical user interface.
00:05:36.879 --> 00:05:39.040
Today we're going to use SSMS for our journey.
00:05:40.000 --> 00:05:41.759
What does SSMS mean?
00:05:42.079 --> 00:05:44.319
Yep, SQL Server Management Studio.
00:05:44.560 --> 00:05:46.000
So we're going to get into that.
00:05:46.639 --> 00:05:48.879
So row level security.
00:05:49.199 --> 00:05:50.240
What do you do?
00:05:50.560 --> 00:05:55.439
We want to allow only certain groups to have access to selected rows in the table.
00:05:56.879 --> 00:06:05.120
Row level security enables you to use your group membership and execute to context to control access to rows in the database table.
00:06:05.279 --> 00:06:10.800
For instance, you can ensure that works access only data rows that pertain to the department.
00:06:10.879 --> 00:06:12.879
So we're going to talk about sales today, okay?
00:06:13.120 --> 00:06:15.120
And we're just going to be three different users.
00:06:15.199 --> 00:06:16.399
We're going to talk about John.
00:06:16.800 --> 00:06:20.720
My middle name is Francis, so it's going to be Francis and Robin.
00:06:20.879 --> 00:06:21.199
Okay.
00:06:21.519 --> 00:06:25.439
And this can be done with again fabric and uh user interface.
00:06:25.519 --> 00:06:26.240
We'll get back to this.
00:06:26.319 --> 00:06:32.160
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.
00:06:32.319 --> 00:06:32.560
Okay.
00:06:33.759 --> 00:06:34.720
Next one.
00:06:35.360 --> 00:06:37.040
Column level security.
00:06:37.279 --> 00:06:46.000
Well, column level basically is we want to hide some columns from a user, okay, how to protect sensitive data.
00:06:46.160 --> 00:07:03.040
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.
00:07:03.120 --> 00:07:03.360
Okay.
00:07:03.839 --> 00:07:09.519
Now, when you add people to any of these objects, they have to be part of the workspace and there's different roles, right?
00:07:09.600 --> 00:07:12.959
You have admin, member, contributor, and viewer.
00:07:13.279 --> 00:07:17.680
Okay, for column service uh level security at work, you have to use viewer.
00:07:18.079 --> 00:07:22.800
Not only that, Microsoft's flip-flop back and forth on how this was going to implement.
00:07:22.959 --> 00:07:29.519
Initially, it was going to be a grant and just the columns, but now it's a grant and a deny.
00:07:29.600 --> 00:07:33.519
Okay, so we'll talk about how uh AI got it wrong.
00:07:33.759 --> 00:07:34.079
Okay.
00:07:34.959 --> 00:07:36.879
Dynamic data masking.
00:07:37.120 --> 00:07:43.199
Okay, we want to allow certain groups to see full context of selected files in a table.
00:07:43.279 --> 00:07:46.480
While others can't see it, it'll be masked, okay?
00:07:46.800 --> 00:07:49.279
And so we'll talk about how we can implement that.
00:07:49.360 --> 00:07:54.800
And again, this can be done with both the graphical user interface and SQL Server Management Studio.
00:07:55.439 --> 00:07:58.160
There's some caveats to anything, right?
00:07:58.480 --> 00:08:05.759
One of the things that came out recently when Microsoft uh released Fabric about a year ago was Direct Lake.
00:08:05.920 --> 00:08:21.519
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?
00:08:22.000 --> 00:08:25.199
Vertipack engine sitting in there and it's column store.
00:08:25.600 --> 00:08:39.759
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.
00:08:39.840 --> 00:08:42.960
Okay, so if things start going slower, you know exactly why.
00:08:43.039 --> 00:08:44.720
And there's always trade-offs in life, right?
00:08:44.879 --> 00:08:54.559
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.
00:08:54.639 --> 00:08:56.960
But again, you have to make those decisions.
00:08:57.360 --> 00:09:01.279
Last but not least, and talk about summaries, right?
00:09:01.360 --> 00:09:05.919
There is a fabric analytic endpoint for four of the six main products in the system.
00:09:06.080 --> 00:09:17.120
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.
00:09:17.200 --> 00:09:17.360
Okay.
00:09:17.840 --> 00:09:24.559
If you're a big fan of direct lake mode, then you have to understand that it might fall back to direct query, okay?
00:09:24.720 --> 00:09:31.759
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.
00:09:31.840 --> 00:09:31.919
Okay.
00:09:32.240 --> 00:09:34.559
Worry about column level security with Power BI.
00:09:34.639 --> 00:09:35.679
You might want to use the view.
00:09:35.759 --> 00:09:37.519
There's data masking, and guess what?
00:09:37.600 --> 00:09:42.240
If you use a replication like uh open mirroring, things might take longer than you think.
00:09:42.480 --> 00:09:44.639
So again, Nicholas, thank you for having me.
00:09:44.720 --> 00:09:45.519
I appreciate it.
00:09:45.919 --> 00:09:46.960
Okay, no worries.
00:09:47.120 --> 00:09:50.559
So thanks a lot for joining this episode, uh, John.
00:09:50.879 --> 00:10:01.039
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.
00:10:01.120 --> 00:10:02.480
So exactly.
00:10:02.639 --> 00:10:06.480
Just a little to show you that it takes a while for it to happen.
00:10:06.720 --> 00:10:14.159
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.
00:10:14.399 --> 00:10:15.360
Yeah, no worries.
00:10:15.440 --> 00:10:16.159
Thanks a lot.
00:10:16.399 --> 00:10:16.720
Bye.
00:10:16.960 --> 00:10:17.919
Thank you, Nicholas.