1 00:00:00,000 --> 00:00:09,200 Welcome to the Azure Security Podcast, 2 00:00:09,200 --> 00:00:12,320 where we discuss topics relating to security, privacy, 3 00:00:12,320 --> 00:00:16,640 reliability, and compliance on the Microsoft Cloud Platform. 4 00:00:16,640 --> 00:00:19,600 Hey everybody, welcome to Episode 54. 5 00:00:19,600 --> 00:00:21,280 This week is myself, Michael. 6 00:00:21,280 --> 00:00:23,560 I'm actually here by myself with our guest. 7 00:00:23,560 --> 00:00:25,680 We're trying a little bit of an experiment here. 8 00:00:25,680 --> 00:00:28,040 So for those of you who don't know, 9 00:00:28,040 --> 00:00:30,960 a few weeks ago, I actually moved into a new team. 10 00:00:30,960 --> 00:00:33,520 I moved out of Microsoft Services, 11 00:00:33,520 --> 00:00:35,680 I was working mainly with our customers on 12 00:00:35,680 --> 00:00:37,640 securing their Azure workloads, 13 00:00:37,640 --> 00:00:40,920 and I moved into the Azure Database Security team. 14 00:00:40,920 --> 00:00:42,640 Fantastic move for me. 15 00:00:42,640 --> 00:00:45,960 I've always had a love for engineering at the back end, 16 00:00:45,960 --> 00:00:50,000 and this is just an opportunity I just could not turn up. 17 00:00:50,000 --> 00:00:53,640 By the way, there's no coincidence there when Thomas Weiss was on. 18 00:00:53,640 --> 00:00:56,240 He and I had a few discussions in the green room, 19 00:00:56,240 --> 00:00:58,040 so to speak before we were recording, 20 00:00:58,040 --> 00:00:59,640 and one thing led to another, 21 00:00:59,640 --> 00:01:01,720 and next thing I'm working with Thomas. 22 00:01:01,720 --> 00:01:04,800 So anyway, this is a little bit of an experiment as I mentioned. 23 00:01:04,800 --> 00:01:07,880 Well, I think we're toying with the idea of having many episodes, 24 00:01:07,880 --> 00:01:10,320 where we focus on one little feature, 25 00:01:10,320 --> 00:01:12,000 or well, not necessarily a little feature, 26 00:01:12,000 --> 00:01:15,360 but one specific feature of a specific product. 27 00:01:15,360 --> 00:01:17,320 This week, we're going to look at 28 00:01:17,320 --> 00:01:20,240 SQL Managed Instance and authentication. 29 00:01:20,240 --> 00:01:22,800 So with that, let me introduce my guest. 30 00:01:22,800 --> 00:01:25,280 This week, we have Sravani Saluru, 31 00:01:25,280 --> 00:01:28,760 again, who's here to talk to us about SQL MI and authentication. 32 00:01:28,760 --> 00:01:30,560 Sravani, thank you for joining us this week. 33 00:01:30,560 --> 00:01:33,560 Would you mind taking a moment and just give our listeners 34 00:01:33,560 --> 00:01:35,000 a little bit of a background? 35 00:01:35,000 --> 00:01:37,840 Sure. Thanks, Michael. Hi, everyone. 36 00:01:37,840 --> 00:01:42,840 I'm Sravani. I have been working with Microsoft for the past 10 years, 37 00:01:42,840 --> 00:01:47,920 and initially worked as a support engineer supporting SQL Server on-prem customers, 38 00:01:47,920 --> 00:01:49,440 as well as the cloud customers, 39 00:01:49,440 --> 00:01:53,360 and recently moved to the database platform security team, 40 00:01:53,360 --> 00:01:55,880 which is part of the SQL Server product group. 41 00:01:55,880 --> 00:02:02,640 I'm currently working as a program manager and owning few features in security portfolio. 42 00:02:02,640 --> 00:02:08,760 One of the features that I own is SQL Auditing for Azure Database, MI, and SQL Server. 43 00:02:08,760 --> 00:02:13,280 Also today, there is one more feature which we are going to talk about that is currently in 44 00:02:13,280 --> 00:02:16,840 public preview and I'm the owner of the feature. 45 00:02:16,840 --> 00:02:20,400 So that's Windows authentication for SQL MI. 46 00:02:20,400 --> 00:02:25,160 Also, I have quite experience in SQL Server in terms of performance, 47 00:02:25,160 --> 00:02:27,920 high availability, and security. 48 00:02:27,920 --> 00:02:31,520 I did some work in high availability area as well. 49 00:02:31,520 --> 00:02:33,440 So let's just get stuck right into it. 50 00:02:33,440 --> 00:02:35,840 So SQL managed instance. 51 00:02:35,840 --> 00:02:39,600 There's a whole bunch of different members of the SQL family. 52 00:02:39,600 --> 00:02:46,960 Can you explain briefly where SQL MI fits and what its role is compared to all the other versions of SQL? 53 00:02:46,960 --> 00:02:53,280 So I think SQL itself is very well known in the database world and it's spread across. 54 00:02:53,280 --> 00:02:57,520 You name a platform and SQL exists there. 55 00:02:57,520 --> 00:03:00,720 So the most important of us are SQL Server, 56 00:03:00,720 --> 00:03:03,120 which works on both Windows and Linux. 57 00:03:03,120 --> 00:03:08,160 And this is the native SQL Server, the on-premise version, 58 00:03:08,160 --> 00:03:10,160 which exists for several years now. 59 00:03:10,160 --> 00:03:15,440 And today, SQL 2022 is going to be released soon. 60 00:03:15,440 --> 00:03:21,040 So that is our on-premise story and we have a lot of enterprise customers who are using 61 00:03:21,040 --> 00:03:27,040 SQL 2022 as their top database servers today. 62 00:03:27,040 --> 00:03:34,240 Now coming to the cloud, as we all are aware, in cloud we offer infrastructure as a service 63 00:03:34,240 --> 00:03:35,760 and platform as a service. 64 00:03:35,760 --> 00:03:37,760 So SQL exists in both places. 65 00:03:37,760 --> 00:03:41,680 So in infrastructure as a service, you can have your SQL servers, 66 00:03:41,680 --> 00:03:48,560 the native SQL servers running on IAS, basically either the Windows VMs or even the Linux VMs. 67 00:03:48,560 --> 00:03:51,600 So that is the infrastructure as a service. 68 00:03:51,600 --> 00:03:57,440 And coming to the past, most of you are aware we use SQL, the Azure Database, 69 00:03:57,440 --> 00:04:02,080 which is the platform as a service offering for SQL Database, 70 00:04:02,880 --> 00:04:07,840 and which comes in SQL Database as well as the Elastic Pools, 71 00:04:07,840 --> 00:04:12,160 where you can have a group of databases as part of your SQL Azure Database. 72 00:04:12,160 --> 00:04:14,160 So that is one of the offerings in the past. 73 00:04:14,160 --> 00:04:18,480 The other one, which is basically the one which we are going to talk today, 74 00:04:18,480 --> 00:04:20,400 is the SQL Managed instance. 75 00:04:21,360 --> 00:04:28,400 As the name itself indicates, it's basically a SQL Server which is managed in the cloud. 76 00:04:28,400 --> 00:04:33,040 So most of our customers wanted something which is very, 77 00:04:33,040 --> 00:04:38,960 very close to their on-prem servers because when it comes to the cloud Azure Database, 78 00:04:38,960 --> 00:04:45,680 that is there are certain features which are server-scoped features like, for example, 79 00:04:46,320 --> 00:04:50,720 CLR or Service Procure or machine learning. 80 00:04:50,720 --> 00:04:54,800 The SQL agent, per se, many of our customers run a lot of jobs. 81 00:04:54,800 --> 00:04:58,640 And for Azure Database, we do not have a SQL agent today. 82 00:04:58,640 --> 00:05:03,920 So they still want to manage their SQL Server in cloud, but at the same time, 83 00:05:03,920 --> 00:05:06,960 they want to be as close as their on-prem servers. 84 00:05:06,960 --> 00:05:10,720 So that is where your SQL Managed instance comes into the picture. 85 00:05:10,720 --> 00:05:16,080 So it is very close, compatible with your SQL Server. 86 00:05:16,080 --> 00:05:20,320 It gets you all server-level-scoped features that you want. 87 00:05:20,320 --> 00:05:25,360 At the same time, the maintenance is completely done by Microsoft, 88 00:05:25,360 --> 00:05:31,120 where the high availability point in Tamry store, everything is offered just the way it is needed 89 00:05:31,120 --> 00:05:34,080 and absolutely zero work from the customer side. 90 00:05:34,080 --> 00:05:38,480 And the best part of the SQL Managed instance is you just have an online migration. 91 00:05:38,480 --> 00:05:40,240 You do not have to do anything. 92 00:05:40,240 --> 00:05:47,040 We use log shipping in the back end and you can just do a switchover whenever you are ready to migrate to cloud. 93 00:05:47,040 --> 00:05:50,640 So that is the main beauty of the Managed instance. 94 00:05:50,640 --> 00:05:54,880 You know, it is funny, every once in a while, someone says something where the penny just drops, 95 00:05:54,880 --> 00:05:58,000 where you realize why something is the way it is. 96 00:05:58,000 --> 00:06:02,080 And you said that there are some features that are server-scoped. 97 00:06:02,080 --> 00:06:06,800 And that made so much sense to me as soon as you said that. 98 00:06:06,800 --> 00:06:11,600 Because I was always wondering, why does something not exist in Azure SQL DB? 99 00:06:11,600 --> 00:06:15,280 And the reason is because some of those features are server-scoped. 100 00:06:15,280 --> 00:06:16,480 Server-scoped. That is good. 101 00:06:16,480 --> 00:06:18,400 Right. So for example, the CLR, as you mentioned, right? 102 00:06:18,400 --> 00:06:23,600 The Common Language Runtime Support where you can write back-end code and say C-sharp. 103 00:06:23,600 --> 00:06:26,160 Yeah, that made absolute sense when you said that. 104 00:06:26,160 --> 00:06:31,200 Actually, the other funny thing is the first version of SQL Server I actually used back in the day, 105 00:06:31,200 --> 00:06:37,440 and I am really showing my age here, is SQL Server 4.2 running on LAN man. 106 00:06:37,440 --> 00:06:41,040 That is even before 2007.0, right? 107 00:06:41,040 --> 00:06:43,040 Hey, so here is a question for you then. 108 00:06:43,040 --> 00:06:44,080 Here is a question for you. 109 00:06:44,080 --> 00:06:47,280 Do you remember or do you know what the sample database was? 110 00:06:47,280 --> 00:06:48,960 What the sample database was back then? 111 00:06:48,960 --> 00:06:51,280 No, don't ask me. 112 00:06:51,280 --> 00:06:52,240 It was the authors. 113 00:06:52,240 --> 00:06:54,000 Remember, it was the authors database. 114 00:06:54,720 --> 00:06:56,640 I know. I heard those stories. 115 00:06:56,640 --> 00:06:58,320 Yeah, yeah. Absolutely. 116 00:06:58,320 --> 00:06:59,280 I heard those stories. 117 00:06:59,280 --> 00:07:02,000 Okay, now you are making me feel really, really old. 118 00:07:02,000 --> 00:07:04,800 My grandpa told me this story about the authors database. 119 00:07:04,800 --> 00:07:05,600 Now, I am only kidding. 120 00:07:06,320 --> 00:07:07,200 No, that is fantastic. 121 00:07:07,200 --> 00:07:07,920 That is actually really good. 122 00:07:07,920 --> 00:07:10,400 I mean, and I think you really put things in perspective there. 123 00:07:10,400 --> 00:07:13,120 So I think one of the really nice things about SQL MI, 124 00:07:13,120 --> 00:07:15,360 or perhaps two of the really nice things about SQL MI, 125 00:07:15,360 --> 00:07:20,160 one, it has a really high degree of isolation, right? 126 00:07:20,160 --> 00:07:23,280 I mean, when you have a managed instance, that's your box. 127 00:07:23,280 --> 00:07:24,880 I mean, that is your instance. 128 00:07:24,880 --> 00:07:27,200 That is not shared with any other compute. 129 00:07:27,200 --> 00:07:28,400 Is that correct? 130 00:07:29,280 --> 00:07:30,640 Yes, that's correct. 131 00:07:30,640 --> 00:07:33,920 Yeah. So for some customers who absolutely categorically 132 00:07:33,920 --> 00:07:36,960 and emphatically require a massive degree of isolation, 133 00:07:36,960 --> 00:07:38,720 then this is certainly an option. 134 00:07:38,720 --> 00:07:39,600 And then the other one is, 135 00:07:40,320 --> 00:07:44,960 whether it being a database product that's very, very close to on-prem 136 00:07:44,960 --> 00:07:48,240 without being on-prem, in other words, we do the management. 137 00:07:48,240 --> 00:07:50,080 Remember, because it all shared responsibility, right? 138 00:07:50,800 --> 00:07:53,920 And so what we've got here is a SQL Server instance 139 00:07:53,920 --> 00:07:55,680 that is managed by us. 140 00:07:55,680 --> 00:07:58,960 Like we do it, we take care of patching and all the sort of good stuff. 141 00:07:59,600 --> 00:08:02,000 But you obviously control, you the customer controls, 142 00:08:02,000 --> 00:08:04,880 the databases and user accounts and so on. 143 00:08:04,880 --> 00:08:07,760 But because it's so close to SQL Server, 144 00:08:07,760 --> 00:08:11,840 it's one of the easiest to use for lift and shift scenarios. 145 00:08:11,840 --> 00:08:12,320 Is that correct? 146 00:08:12,960 --> 00:08:13,840 Yes, that's good. 147 00:08:13,840 --> 00:08:15,040 Okay. All right. 148 00:08:15,040 --> 00:08:17,360 So we're mainly here to talk about authentication options. 149 00:08:17,360 --> 00:08:19,680 So before we talk about SQL MI specifically 150 00:08:19,680 --> 00:08:22,240 with the new Windows authentication stuff that's in preview, 151 00:08:22,960 --> 00:08:25,040 why don't we just take a quick whirlwind tour 152 00:08:25,040 --> 00:08:27,600 about the around the basic authentication options 153 00:08:27,600 --> 00:08:29,520 that we have in the SQL products? 154 00:08:30,080 --> 00:08:33,360 Sure. So the native SQL Server supports 155 00:08:34,080 --> 00:08:37,280 Windows authentication and the mixed authentication, right? 156 00:08:37,280 --> 00:08:40,880 So the Windows authentication is basically for the domain users. 157 00:08:40,880 --> 00:08:44,240 And the mixed authentication is you can have either domain users 158 00:08:44,240 --> 00:08:45,760 or a SQL Server lock-ins, 159 00:08:45,760 --> 00:08:48,320 which you can create inside SQL Server 160 00:08:48,320 --> 00:08:51,280 and use them to authenticate the users. 161 00:08:51,280 --> 00:08:54,960 So that is how the SQL Server has been supporting for several years. 162 00:08:54,960 --> 00:08:57,040 And then with SQL 2022, 163 00:08:57,040 --> 00:09:01,040 we also started supporting Azure Active Directory users as well. 164 00:09:01,040 --> 00:09:02,880 So that is with the SQL Server story. 165 00:09:02,880 --> 00:09:06,160 And when it comes to Azure SQL Database, 166 00:09:06,160 --> 00:09:11,520 we offer Azure Active Directory authentication, 167 00:09:12,080 --> 00:09:17,520 where you can have authentication using Password or using MFA, 168 00:09:17,520 --> 00:09:19,360 the multi-factor authentication, 169 00:09:19,360 --> 00:09:24,800 or you can also use Azure Active Directory integrated authentication. 170 00:09:24,800 --> 00:09:27,120 So one of the challenges that we have seen 171 00:09:27,120 --> 00:09:30,480 when customers wants to migrate to Azure SQL Database 172 00:09:30,480 --> 00:09:33,280 is they might have a legacy applications, 173 00:09:33,280 --> 00:09:36,960 which may not be able to use this Federation providers 174 00:09:36,960 --> 00:09:41,920 that they need for this Azure Active Directory integrated authentication to work. 175 00:09:41,920 --> 00:09:46,240 So that could be one of the blocker for many applications to migrate. 176 00:09:46,240 --> 00:09:47,600 If you have a legacy driver, 177 00:09:47,600 --> 00:09:51,280 which may not support this Azure Active Directory authentication. 178 00:09:51,280 --> 00:09:57,360 So that is when we have this Windows authentication for Azure AD users 179 00:09:58,240 --> 00:09:59,840 for SQL Managed Instance. 180 00:09:59,840 --> 00:10:02,960 This feature is in public preview. 181 00:10:02,960 --> 00:10:06,320 Currently, and in few months, it will be GA. 182 00:10:06,320 --> 00:10:10,880 So with this, we are going to unblock all the migrations 183 00:10:10,880 --> 00:10:14,000 which are blocked because they could not migrate 184 00:10:14,000 --> 00:10:16,960 because of the legacy drivers or the legacy applications 185 00:10:16,960 --> 00:10:19,600 and the way the applications are designed. 186 00:10:19,600 --> 00:10:23,920 They cannot use, they can only use Windows authentication, 187 00:10:23,920 --> 00:10:26,000 and that support is not available today. 188 00:10:26,000 --> 00:10:31,680 So that is where this Windows authentication for SQL MI comes into the picture. 189 00:10:31,680 --> 00:10:36,160 And this is going to unblock many of our customers. 190 00:10:36,160 --> 00:10:37,440 This is important, right? 191 00:10:37,440 --> 00:10:42,160 I mean, if we say that we've got these lift and shift scenarios, 192 00:10:42,160 --> 00:10:44,720 authentication is obviously critical. 193 00:10:44,720 --> 00:10:47,280 And you don't want things to start breaking 194 00:10:47,280 --> 00:10:49,680 because you move this thing into the cloud 195 00:10:49,680 --> 00:10:51,680 and all of a sudden your authentication just breaks. 196 00:10:51,680 --> 00:10:52,720 Yeah. 197 00:10:52,720 --> 00:10:59,040 So this is obviously a critically important feature that is coming in SQL MI. 198 00:10:59,040 --> 00:11:02,080 So could you explain just sort of briefly kind of how it works 199 00:11:02,080 --> 00:11:06,080 and what are the machinery is required to make it work successfully? 200 00:11:06,080 --> 00:11:07,520 Absolutely. 201 00:11:07,520 --> 00:11:11,120 So I will just talk about this feature, 202 00:11:11,120 --> 00:11:13,760 like how it design and how it's going to work. 203 00:11:13,760 --> 00:11:18,160 So first of all, by enabling this Windows authentication 204 00:11:18,160 --> 00:11:20,960 for Azure Active Directory principles, 205 00:11:20,960 --> 00:11:24,000 customers can migrate to Azure Managed Instance 206 00:11:24,000 --> 00:11:27,120 without implementing any changes in their application, right? 207 00:11:27,120 --> 00:11:30,240 So they just have to do this initial setup 208 00:11:30,240 --> 00:11:32,400 for the authentication flow to work. 209 00:11:32,400 --> 00:11:34,880 And once that is done from the application side, 210 00:11:34,880 --> 00:11:37,200 they don't have to make any changes. 211 00:11:37,200 --> 00:11:43,440 So how it works basically is we use this Kerberos authentication workflow 212 00:11:43,440 --> 00:11:44,400 in the backend. 213 00:11:44,400 --> 00:11:48,400 The main prerequisite here is customers have to ensure 214 00:11:48,400 --> 00:11:50,240 that their Windows principles, 215 00:11:50,240 --> 00:11:53,840 the Active Directory principles are synchronized 216 00:11:53,840 --> 00:11:56,000 with the Azure Active Directory, 217 00:11:56,000 --> 00:11:58,720 which is in today's world, 218 00:11:58,720 --> 00:12:01,120 we have seen customers are already doing that 219 00:12:01,120 --> 00:12:04,960 because they are, along with modernizing their applications, 220 00:12:04,960 --> 00:12:07,120 they are also modernizing their identity. 221 00:12:07,120 --> 00:12:10,000 And most of our customers already have 222 00:12:10,000 --> 00:12:14,160 like a hybrid Active Directory domains running in their environments. 223 00:12:14,160 --> 00:12:18,000 So this is a prerequisite where they have to ensure 224 00:12:18,000 --> 00:12:22,000 the principles are synchronized between their Active Directory 225 00:12:22,000 --> 00:12:24,720 as well as the Azure Active Directory. 226 00:12:24,720 --> 00:12:29,840 Once that is done, we have to establish this Kerberos authentication flow 227 00:12:29,840 --> 00:12:31,840 with the Azure Active Directory. 228 00:12:31,840 --> 00:12:35,440 For this to work, we support all versions of Windows, 229 00:12:35,440 --> 00:12:40,480 but like there are two ways to establish this Kerberos authentication. 230 00:12:40,480 --> 00:12:44,720 So if you are using, depending on your client mission, 231 00:12:44,720 --> 00:12:49,680 for example, you are using Windows 2012 or other versions 232 00:12:49,680 --> 00:12:53,040 where you do not have a latest OS version 233 00:12:53,040 --> 00:12:57,040 where you can use this modern interactive workflow, 234 00:12:57,040 --> 00:12:59,520 for those servers, we can just go ahead 235 00:12:59,520 --> 00:13:03,040 and use an incoming trust-based authentication. 236 00:13:03,040 --> 00:13:07,040 I think this is something that, at least the SQL server customers 237 00:13:07,040 --> 00:13:08,640 hard for several years, right? 238 00:13:08,640 --> 00:13:11,040 When you have to connect across domains, 239 00:13:11,040 --> 00:13:13,040 you have to establish this incoming trust, 240 00:13:13,040 --> 00:13:17,040 and basically you are going to use Kerberos authentication protocol 241 00:13:17,040 --> 00:13:19,040 in the backend to make this connection 242 00:13:19,040 --> 00:13:21,040 since both the domains are trusted. 243 00:13:21,040 --> 00:13:23,040 So this is not something that is new, 244 00:13:23,040 --> 00:13:27,040 but just that it's now implemented for the Azure Active Directory. 245 00:13:27,040 --> 00:13:33,040 So the customer's domain and the Azure AD are now in trust with each other. 246 00:13:33,040 --> 00:13:37,040 So you can make this Kerberos authentication to work, 247 00:13:37,040 --> 00:13:41,040 and through which the Windows authentication happens. 248 00:13:41,040 --> 00:13:45,040 So this is for the clients which are using Windows 2012. 249 00:13:45,040 --> 00:13:49,040 So all they have to do is establish this incoming trust 250 00:13:49,040 --> 00:13:53,040 between Azure Active Directory and their domain services. 251 00:13:53,040 --> 00:13:57,040 So with the incoming trust-based workflow, 252 00:13:57,040 --> 00:14:01,040 the prerequisite is just your client must be running 253 00:14:01,040 --> 00:14:05,040 on a Windows Server 2012 or higher version. 254 00:14:05,040 --> 00:14:09,040 And second thing is it has to be a domain-joined machine. 255 00:14:09,040 --> 00:14:11,040 It should have access to the Active Directory 256 00:14:11,040 --> 00:14:15,040 because when the Kerberos token has to be issued, 257 00:14:15,040 --> 00:14:19,040 it has to connect to the local directory to check for the experience, 258 00:14:19,040 --> 00:14:21,040 the service principal name, 259 00:14:21,040 --> 00:14:25,040 and then get the Kerberos token and send it to the Azure Active Directory. 260 00:14:25,040 --> 00:14:27,040 So to only two conditions, 261 00:14:27,040 --> 00:14:31,040 the client must be running on Windows 2012 or above. 262 00:14:31,040 --> 00:14:37,040 The second one is it should have access to their local Active Directory domain. 263 00:14:37,040 --> 00:14:41,040 Once this two is done, all you have to do is establish this incoming trust 264 00:14:41,040 --> 00:14:45,040 using the Kerberos KDC server, 265 00:14:45,040 --> 00:14:47,040 that is the Kerberos distribution center. 266 00:14:47,040 --> 00:14:51,040 So for that, we already provide a PowerShell commandlet. 267 00:14:51,040 --> 00:14:55,040 So customers just have to install the PowerShell commandlet. 268 00:14:55,040 --> 00:14:57,040 And once the module is installed, 269 00:14:57,040 --> 00:15:00,040 just to use their domain name past the credentials, 270 00:15:00,040 --> 00:15:05,040 and then create the trust object for the Azure Active Directory domains 271 00:15:05,040 --> 00:15:07,040 in their local Active Directory. 272 00:15:07,040 --> 00:15:10,040 So that is the first workflow which works. 273 00:15:10,040 --> 00:15:13,040 You through incoming trust based. 274 00:15:13,040 --> 00:15:19,040 And there is another workflow for which your machine need not to even join a domain. 275 00:15:19,040 --> 00:15:25,040 In this scenario, if it is a Azure AD joined machine, 276 00:15:25,040 --> 00:15:29,040 so if you have a VM which is a Azure AD joined machine, 277 00:15:29,040 --> 00:15:33,040 then you do not have to even establish any incoming trust. 278 00:15:33,040 --> 00:15:36,040 It works through modern interactive flow. 279 00:15:36,040 --> 00:15:40,040 So for this modern interactive flow to work, 280 00:15:40,040 --> 00:15:46,040 your Windows version must be either see 2022 server 281 00:15:46,040 --> 00:15:50,040 or it's Windows 10, 20 H1 and about. 282 00:15:50,040 --> 00:15:54,040 So if you have a client which is running on this Windows version, 283 00:15:54,040 --> 00:16:00,040 and if it's Azure joined, the Azure AD joined or a hybrid joined machine, 284 00:16:00,040 --> 00:16:02,040 you do not have to do anything. 285 00:16:02,040 --> 00:16:05,040 It's already knows about the modern interactive workflow 286 00:16:05,040 --> 00:16:07,040 since it's already joined to Azure AD. 287 00:16:07,040 --> 00:16:13,040 All you have to do is enable this Kerberos token during the login. 288 00:16:13,040 --> 00:16:18,040 So there is a group policy that you have to configure on the client machine 289 00:16:18,040 --> 00:16:20,040 in your local policies. 290 00:16:20,040 --> 00:16:24,040 So you just have to go to the sec poll and update this local policy 291 00:16:24,040 --> 00:16:28,040 which tells you that use Kerberos token during the login process. 292 00:16:28,040 --> 00:16:33,040 So you just enable this setting and you just have to connect to the managed instance 293 00:16:33,040 --> 00:16:35,040 using the Windows authentication. 294 00:16:35,040 --> 00:16:40,040 So in this scenario, since this is a modern interactive workflow, 295 00:16:40,040 --> 00:16:47,040 it works only for the applications which has the interactive connection method. 296 00:16:47,040 --> 00:16:52,040 So if I need to give examples like maybe a SQL server management studio 297 00:16:52,040 --> 00:16:57,040 or a SQL server profiler or an extended event where you can explicitly specify 298 00:16:57,040 --> 00:17:00,040 and interact while connecting to the service. 299 00:17:00,040 --> 00:17:06,040 And it will also work with applications like which uses impersonation. 300 00:17:06,040 --> 00:17:11,040 However, this may not work for certain applications which will run as a service 301 00:17:11,040 --> 00:17:17,040 because since it's configured as a service, there is no interactive method of login during the login. 302 00:17:17,040 --> 00:17:21,040 So if you have applications which are running as service, 303 00:17:21,040 --> 00:17:25,040 in that scenario, you still have to establish incoming trust 304 00:17:25,040 --> 00:17:28,040 and then use the incoming trust based authentication model. 305 00:17:28,040 --> 00:17:34,040 So with these two in place, either modern interactive flow or with the incoming trust, 306 00:17:34,040 --> 00:17:39,040 we pretty much cover all kinds of clients that are running either joined active directory, 307 00:17:39,040 --> 00:17:45,040 a domain join server or an Azure AD joined machine or just a hybrid machine 308 00:17:45,040 --> 00:17:48,040 or any of these clients, right? 309 00:17:48,040 --> 00:17:51,040 And you can connect to the managed instance. 310 00:17:51,040 --> 00:17:56,040 So the second aspect is to allow managed instance to understand 311 00:17:56,040 --> 00:17:58,040 the Windows authentication. 312 00:17:58,040 --> 00:18:04,040 We have to enable this SPNs, which is quite familiar to all SQL server customers 313 00:18:04,040 --> 00:18:08,040 because the service principle names are pretty known, right? 314 00:18:08,040 --> 00:18:13,040 If the SPN is not correct, you know, you have seen scenarios where the authentication just fails. 315 00:18:13,040 --> 00:18:17,040 So for managed instance to understand this Windows authentication, 316 00:18:17,040 --> 00:18:22,040 we have to enable this service principle which is available in Azure portal itself. 317 00:18:22,040 --> 00:18:24,040 So you do not have to do anything. 318 00:18:24,040 --> 00:18:29,040 Let's say you want to configure Windows authentication for your managed instance. 319 00:18:29,040 --> 00:18:33,040 All you have to do is go to your Azure portal, go to the managed instance, 320 00:18:33,040 --> 00:18:41,040 and in managed instance in the principles, you can just enable the service principle for the managed instance. 321 00:18:41,040 --> 00:18:48,040 Once the service principle is enabled, you also have to provide an admin consent for managed instance 322 00:18:48,040 --> 00:18:52,040 so that it can allow applications to communicate with the managed instance. 323 00:18:52,040 --> 00:19:01,040 So to provide the admin consent, you just have to go to the Azure Active Directory in portal 324 00:19:01,040 --> 00:19:05,040 and then search for your managed instance and provide admin consent. 325 00:19:05,040 --> 00:19:08,040 Again, this can be also done in the portal itself. 326 00:19:08,040 --> 00:19:12,040 It just takes couple of minutes to do both the steps that I described 327 00:19:12,040 --> 00:19:17,040 and they are documented in our public documentation with these screenshots. 328 00:19:17,040 --> 00:19:19,040 So it's easy for you to access. 329 00:19:19,040 --> 00:19:23,040 Again, just to summarize, there are two aspects. 330 00:19:23,040 --> 00:19:26,040 One, the configuration that needs to be done at the SQL MI 331 00:19:26,040 --> 00:19:32,040 where you need to enable the service principle by setting the service principles to on 332 00:19:32,040 --> 00:19:37,040 and provide the admin consent for SQL MI in your Azure Active Directory. 333 00:19:37,040 --> 00:19:40,040 So these are the two steps from the MI side. 334 00:19:40,040 --> 00:19:44,040 Once that is done, on the client side for the Kerberos to work, 335 00:19:44,040 --> 00:19:51,040 based on the client version, if it is Windows 2012 or above, you will do an incoming trust. 336 00:19:51,040 --> 00:19:56,040 For the incoming trust to work, all you need to do is create the Kerberos tokens. 337 00:19:56,040 --> 00:20:02,040 We have a partial module which you can use, provide the domain credentials to connect your domain 338 00:20:02,040 --> 00:20:09,040 and provide your Azure Active Directory domain and it just creates the required Kerberos for you. 339 00:20:09,040 --> 00:20:15,040 And the other way is just to use the modern interactive flow. If you have machines which are running on 340 00:20:15,040 --> 00:20:20,040 Windows 10, 20 H1 or Windows Server 20, 22 and above, 341 00:20:20,040 --> 00:20:25,040 you just have to set the local policy to enable Kerberos token during the lock-in. 342 00:20:25,040 --> 00:20:31,040 Once that is done, if the machines are joined to Azure AD or if they are hybrid joined, 343 00:20:31,040 --> 00:20:34,040 it just establishes the connection. 344 00:20:34,040 --> 00:20:39,040 So once you do both the settings, you can test the connection either using an SSMS 345 00:20:39,040 --> 00:20:43,040 or any client tool that connects to the SQL Server. 346 00:20:43,040 --> 00:20:46,040 So I have one point, one question and a comment. 347 00:20:46,040 --> 00:20:51,040 So the first one is when we are talking Windows authentication, we are just talking Kerberos here, right? 348 00:20:51,040 --> 00:20:52,040 Yes. 349 00:20:52,040 --> 00:20:57,040 Okay, because just for those who are not aware, technically there is no real such thing as Windows authentication. 350 00:20:57,040 --> 00:21:01,040 It is kind of two things. It is either Kerberos or the classic local accounts, the SAM account, 351 00:21:01,040 --> 00:21:04,040 which is like domain slash name. So we are talking Kerberos. 352 00:21:04,040 --> 00:21:09,040 Now, one of the most important things that people should be aware of is with Kerberos, 353 00:21:09,040 --> 00:21:12,040 Kerberos provides mutual authentication, right? 354 00:21:12,040 --> 00:21:14,040 So it is authenticating both ends of the communication. 355 00:21:14,040 --> 00:21:17,040 And the key thing there is I think all the service principal name. 356 00:21:17,040 --> 00:21:22,040 And the service principal name is things like the machine name, 357 00:21:22,040 --> 00:21:27,040 like the IP address or the DNS name, the identity under which it is executing, 358 00:21:27,040 --> 00:21:30,040 and also the protocol import number. 359 00:21:30,040 --> 00:21:35,040 And that is really important so that way Kerberos can actually authenticate the endpoint. 360 00:21:35,040 --> 00:21:38,040 So I just want to make sure everyone is aware when we are throwing the service principal name thing out there, 361 00:21:38,040 --> 00:21:39,040 what we are talking about. 362 00:21:39,040 --> 00:21:43,040 It is just this unique identifier that identifies the endpoint. 363 00:21:43,040 --> 00:21:49,040 And you said something really interesting there, which is that the service principal name must be registered in as your active directory. 364 00:21:49,040 --> 00:21:53,040 And the moment you said that, again, that is when the penny drops, right? 365 00:21:53,040 --> 00:21:54,040 That is the key part. 366 00:21:54,040 --> 00:22:00,040 If you don't have the SPN registered in AAD, there is no way AAD is going to know, 367 00:22:00,040 --> 00:22:02,040 or Kerberos is going to know what the endpoint is. 368 00:22:02,040 --> 00:22:03,040 It is not going to be as authenticating the endpoint. 369 00:22:03,040 --> 00:22:05,040 Yes, that is critically important. 370 00:22:05,040 --> 00:22:11,040 So another question is, does this require things like, let's say ExpressRoute. 371 00:22:11,040 --> 00:22:12,040 Is that required to make this work? 372 00:22:12,040 --> 00:22:16,040 For this feature to work, you do not need any ExpressRoute. 373 00:22:16,040 --> 00:22:19,040 It just works through any connection. 374 00:22:19,040 --> 00:22:20,040 Right. 375 00:22:20,040 --> 00:22:21,040 That is good to know. 376 00:22:21,040 --> 00:22:28,040 I mean, a lot of things, some services that we have that provide this sort of functionality may require things like ExpressRoute. 377 00:22:28,040 --> 00:22:30,040 But that is great to see that we do not need that. 378 00:22:30,040 --> 00:22:34,040 So what are some of the major business benefits of all of this? 379 00:22:34,040 --> 00:22:39,040 I mean, one thing that I see is just unblocking some migrations to Azure, right? 380 00:22:39,040 --> 00:22:42,040 That is one thing I see as a major issue, or a major benefit. 381 00:22:42,040 --> 00:22:43,040 Is that a fair comment? 382 00:22:43,040 --> 00:22:46,040 And what other scenarios do you see as being valid here? 383 00:22:46,040 --> 00:22:55,040 Yeah, so that is a major, I mean, that is a top scenario that we are targeting today to unblock our customers to migrate to Azure. 384 00:22:55,040 --> 00:23:01,040 All the legacy applications which could be running on IIS with AppPools, right? 385 00:23:01,040 --> 00:23:03,040 With identity and run as a service. 386 00:23:03,040 --> 00:23:08,040 So we want to unblock those customers who don't have access to Azure, 387 00:23:08,040 --> 00:23:12,040 for Federation services, or they might be using some legacy drivers, things like that. 388 00:23:12,040 --> 00:23:22,040 So this feature we are targeting to migrate as many as customers as possible to MI. 389 00:23:22,040 --> 00:23:32,040 And the second part is ultimately, like I said, since we have SQL MI pretty much close to your on-prem servers, 390 00:23:32,040 --> 00:23:40,040 you can use all the client tools that you use to use in SQL Server, like SQL Profiler, extended events. 391 00:23:40,040 --> 00:23:45,040 All those client tools are going to just work fine with this Windows authentication. 392 00:23:45,040 --> 00:23:48,040 So that is another benefit that we are seeing today. 393 00:23:48,040 --> 00:23:51,040 Now, this is really cool stuff, I think. 394 00:23:51,040 --> 00:24:00,040 Again, for those who want to move workloads quickly to Azure with minimal downtime, minimal effort, minimal re-engineering, 395 00:24:00,040 --> 00:24:03,040 SQL MI can be a valid option. 396 00:24:03,040 --> 00:24:07,040 I think for Greenfield, I think a lot of people will look at, say, Azure SQL DB, 397 00:24:07,040 --> 00:24:11,040 for migration, and this makes absolute sense. 398 00:24:11,040 --> 00:24:14,040 By the way, something people should be aware of if they are not already aware of this. 399 00:24:14,040 --> 00:24:21,040 When you spin up a version of SQL MI, it can take hours, just so you know. 400 00:24:21,040 --> 00:24:24,040 I mean, we are literally allocating your own hardware for you. 401 00:24:24,040 --> 00:24:28,040 So don't be surprised if you can't just spin one up in 30 seconds and then come back. 402 00:24:28,040 --> 00:24:30,040 It is not going to be there, right? 403 00:24:30,040 --> 00:24:35,040 We are allocating system resources at the back end, so it is a little bit different. 404 00:24:35,040 --> 00:24:39,040 Again, the reason for that is it is literally your own isolated environment, 405 00:24:39,040 --> 00:24:41,040 and that is what we are spinning up at the back end. 406 00:24:41,040 --> 00:24:44,040 All right, I think that is anything else you want to mention? 407 00:24:44,040 --> 00:24:47,040 If not, do you have a final thought you would like to leave our listeners with? 408 00:24:47,040 --> 00:24:56,040 About the final thoughts, I mean, I have been into, I mean, I worked as a SQL administrator for several years in my experience. 409 00:24:56,040 --> 00:24:59,040 Of course, I am not competing with Michael for sure. 410 00:24:59,040 --> 00:25:07,040 I just realized how old Michael is, I mean, he was working from SQL 4.0, so he surely knows better than me. 411 00:25:07,040 --> 00:25:16,040 But I myself worked as an administrator, and I know like at least 70% of the work goes into this administration part, right? 412 00:25:16,040 --> 00:25:23,040 But today, with SQL in-pass, where we have this platform as a service, you have everything that you need. 413 00:25:23,040 --> 00:25:27,040 You have Azure Database, you have Elastic Ports, you have Hyperscale, 414 00:25:27,040 --> 00:25:29,040 and you have this managed instance. 415 00:25:29,040 --> 00:25:39,040 So, if you are still not migrated to Azure, I will just leave it to you to think what is that one point that is blocking you not to migrate to cloud? 416 00:25:39,040 --> 00:25:44,040 Because today, the cloud services in Azure provides you the topmost security, 417 00:25:44,040 --> 00:25:48,040 provides you the topmost availability point in time recovery, what not? 418 00:25:48,040 --> 00:25:51,040 Everything that you need for a database server. 419 00:25:51,040 --> 00:25:59,040 So, if someone comes to me and tells me that, you know, what from tomorrow you don't have to worry about your administrative stuff, 420 00:25:59,040 --> 00:26:05,040 just go and work on your applications, that's going to be like the great thing to me, right? 421 00:26:05,040 --> 00:26:07,040 So, just think about it. 422 00:26:07,040 --> 00:26:12,040 And if you have any blockers, you think that this is the blocker today for us to not to move to the pass, 423 00:26:12,040 --> 00:26:18,040 either to database, Azure Database, or to managed instance, just let us know your thoughts, right? 424 00:26:18,040 --> 00:26:23,040 Just put things in perspective. You know, you've got SQL Server on-prem, you've got SQL Server in VM, 425 00:26:23,040 --> 00:26:25,040 but you've got to manage absolutely everything. 426 00:26:25,040 --> 00:26:34,040 Then you've got SQL MI, where the back end, like the anti-malware, the updates and so on, that's all handled by us. 427 00:26:34,040 --> 00:26:42,040 And then you've got SQL, Azure SQL DB, which is the straight up pass solution, where we manage even more of the back end. 428 00:26:42,040 --> 00:26:48,040 But yeah, you're right. You really want people to move to platform as a service as much as possible, 429 00:26:48,040 --> 00:26:50,040 because more of it is handled by Microsoft, right? 430 00:26:50,040 --> 00:26:55,040 It's handled by the Azure infrastructure and by the people at the back end, which is always beneficial. 431 00:26:55,040 --> 00:26:59,040 Okay, well with that, let's bring this episode to an end. 432 00:26:59,040 --> 00:27:06,040 Give us your feedback as well, if you like this idea of just sort of running relatively short sessions with no news, 433 00:27:06,040 --> 00:27:11,040 just focusing on a specific product or a specific part of a product from a security perspective. 434 00:27:11,040 --> 00:27:14,040 And let us know what topics you'd like to learn about. 435 00:27:14,040 --> 00:27:16,040 So with that, let's bring this episode to an end. 436 00:27:16,040 --> 00:27:18,040 So Varni again, thank you so much for joining us this week. 437 00:27:18,040 --> 00:27:20,040 And to our listeners out there, thank you for listening. 438 00:27:20,040 --> 00:27:49,040 Take care and we'll see you next time.