Track middle tier applications with SQL Audit | Data Exposed

Key Takeaways

learn how we're making it easier for you to track middle to your applications with SQL audit in Azure SQL database this week on data [Music] exposed hi I'm Anna Hoffman and welcome to this episode of data exposed today I'm joined by Sani a product manager on the SQL security team s Ronnie thanks so much for joining us today uh you've been on the show before so it's great to have you back today we're going to be talking about some new things Landing in SQL auditing that I actually don't know anything about so I'd love to just pass it over to you to understand a little bit more about like what is this OBO thing how does it work with SQL audit and and maybe even take a look thanks Anna thanks for having me again yeah so we recently released an enhancement in auditing to support on behalf of access requests for your SQL database so probably I can give a quick introduction about what is on behalf of flow right so Microsoft entra supports on behalf of flow which means a web application uh can be authenticated using an identity which is not its own identity and uh with this on behalf of access uh now it's even supported for aure SQL database and SQL managed instance uh any middle te applications in Microsoft intra identity platform can connect to SQL database and SQL managed instance using a signed in user uh apart from the application identity right so uh this is an enhancement that exists for many months now and it is it is generally available however inside SQL there is there is no direct way to track this middle T application details which is coming through on behalf of access uh to SQL database so this on behalf of flow has certain uh advantages like it eliminates connection secrets to the backend Services just like managed identity approach it also gives backend databases more control over who and how much uh access you can grant to the data and its functionality and it also let the app uh taor uh its data presentation to the signed in user uh but like I said uh there is no way to track this uh connections which are coming from on behalf of access inside SQL database uh so in auditing what we have done is now we pull this middle T application details uh into the audit locks uh from the Azure Ed token that we receive and we write that information into the audit locks here so if you see we have added a new column in the audit logs which tells you obio middle T application ID which gives you the application ID and with this customers now can track their all on behalf of access requests along with the uh the signed in user that is trying to access the database so this is very important ask from many customers for SQL and SQL managed instance because uh this is needed uh to meet their uh security and compliance so that they can track all their on behalf of uh access requests that are coming to Azure SQL database So currently we support this for uh Azure SQL database and soon we will be supporting this for even SQL managed instance uh we can quickly go through the demo to show you how this works cool awesome it's great to see this land I'm sure a lot of customers are happy that they're now going to be able to track this um from those middle tier apps but yeah let's take a look yeah so uh let me quickly take you to my Azure portal so here is a resource Group that I have uh with certain applications deployed so here you can see that there I have a demo web application which is created and I also have a SQL database and SQL server and I created a storage account where I can store the audit logs so in this scenario what I'm going to do is the application that I have here uh will be connecting using the signed in user which is coming from Microsoft entra so this is this works with the Microsoft entra Authentication uh which used to be called as azzure ad authentication uh so the signed in user who has this web application access I can now access this application and connect to the SQL database and authenticated by the signed in user so for that um let me quickly show you my SQL database uh server here so this is my server and if you see I have uh my managed identities are configured here there is a managed identity that is added which can be my primary identity on my SQL Server uh and then this is my web application and when when user access this web application so if you go here which is deployed in aure uh and then I can just try to access this web application page it takes me so it authenticates the signed in user in the database and takes me to the uh web application page the landing page right so uh while we do this here I have my uh SQL server for which I just showed you how the identity is configured let's also check that the auditing is enabled uh so this is my SQL Server I think I just uh have that okay I think this is okay yeah so if you see here this is my uh SQL DV audit logs container to which I enable the auditing and here my XL files are sitting uh so now that we have accessed the application from uh the web app let's quickly go through the extended event logs uh so you can uh I just open the XL files on my management studio and here you can see that there is a server principal Sid and server principal name along with that obio middle application ID right so this is my application ID which is now coming from Microsoft entra and connecting to SQL database so now you can relate so which application has which uh uh which user uh as on behalf of user uh which application granted permissions as on behalf of user to which account and you can also try to retrive this application details by using uh uh command uh Powell command which is uh which I have uh explained in my blog as well so you can simply use this get aure ready application and give the application ID that you get in the audit logs and you can retrive the application details like you can find out which application from my uh Microsoft identity platform is is trying to connect uh to the data waye using an on behalf of access so with this now there is a way uh for customers to track these requests all on behalf of access requests and that can help them to meet their uh security requirements as well as compliance requirements awesome cool suran this was a great demo that kind of takes us through the different steps so just so I understand uh what's happening now like what we've added in those ex event log files you showed the the I think the service principal ID that was accessing it and that new column this OBO middle to your app ID this is what we've added and because beforehand you just have the service principle but you wouldn't know which app was connecting is that am I understanding that right that is that is right so we do have the client application which which could be the which could be the end client but however there is no way to track the middle application because this OBO access is actually coming from a middle application which SQL doesn't even understand what is that right so uh there is no identity created for the middle application inside SQL Server SQL all it knows is the signed in user so to track this uh we have added this column and we retrive this details from the aure ad token that we get uh in the connection and from that we we are populating this information to the audit logs awesome cool and one final question like there's nothing is there anything special that folks need to do to start getting this or will this just start appearing in their xcl files this just yeah this is enabled worldwide it is just available in their XL files and it just gets populated for all the OBO requests but like I said currently it is available only for aure SQL database and soon it will be available for SQL managed instance and currently obio is applicable only for SQL database as and SQL Mii got it okay makes a lot of sense Sani hey thanks so much for coming on the show I learned a lot I'm sure our viewers have learned a lot uh to our viewers if you want to learn more we'll put some links in the description including a link to sani's blog so you can go learn more about it um leave us a comment and let us know what you think like this episode if you like this episode and we hope to see you next time on data [Music] exposed