Microsoft SQL Connector for Power Apps and Power Automate #PowerApps #PowerAutomate #SQL

hey everyone in this video we will learn about power automate and power apps SQL connector I will talk about what it is how you can use it in your power app or in your power automate flow I'm also going to talk about few very important point that you should definitely know if you are using SQL in your flow or in your app so stay tuned [Music] hey everyone I'm Deepak Shasta welcome back to my channel so today we are talking about uh SQL connector for power apps and power automate so to to show you how

you can create the connector if you log into make. Power apps.com to create a new app we can start creating a blank app and then create a connection or I can start creating from SQL the only difference between start in from blank app or starting from SQL if I am going to select the SQL power app is automatically going to create screens for me so I don't need to create those screen so I'm going to select that it's no difference you can start from the blank or you can start from your any existing app that

you have the the main thing is creating the connection what I need to show you so once I go to uh start from blank I will end up on this screen where you can see all of the different connection those are available aable for me to use or I can create a new connection if you are not using SQL this is the place you end up new connection and search for SQL okay you will end up this this screen if you are creating the connection from the blank screen or on your existing app the screen

is more or less it's going to look similar and I can quickly so if you select from the blank app you need need to of course select the type of app you are creating give a name so you will end up to this screen and then from the left hand data you need to click on add data search for SQL here and then select SQL add a new connection you will end up to the same screen right where you need to select the authentication type very similar okay so it's not really difference it's just the

difference between uh where you are and how you can reach to this screen where you can create the SQL connector now if you notice any other connector uh you need to create your uh connection using the account with s call you have multiple options you can select as your ad integrated you can select SQL Server authentication Windows authentication Windows authentication not Shar what to select when is the key point because whatever selection I made here when I'm going to create this connector and when I'm going to publish my app and share with other users ERS

not only my app is going to share with them but also the connection that I have in my app is also going to share with them and what does that mean when those user are going to create any new app they will see that connection listed here okay so remember this always when you are sharing your power app with other user you are also sharing the connection that you have used in that app okay and those user can use those connection to create some other app it's equel because they have we have different type of

authentication it's going to behave differently when you are going to share that app so for an example if you select the AZ ad integrated that means that the connection that you're going to create for this app you are saying that when someone is going to use that connection it's going to use the Azure active directory authentication so when I'm using that connection it's going to use my credentials if someone else is going to try to use the same connection they need to use their own credential so they cannot use mine okay if I'm going to

select the SQL Server authentication in this case whether I'm connecting to the SQL in Azure or an on Prem SQL I'm providing the SQL server name database name and username and password when I'm going to use this kind of authentication type and create this connector and share with other users they can use the same same connection same username and password for the given SQL server and database and they can create and use this connection to create other app and do all the operation on this SQL database that this username and password is going to have

so if you are using uh cql admin username and password here then whoever is you going to share your app B they will have the complete access to your SQL database if you are trying to use the windows authentication so in this case what I'm saying in Windows authentication I'm selecting SQL server name database and then I'm providing Windows login here it is very much similar to the SQL authentication instead of providing the SLE username and password I'm providing Windows username and password and when I'm going to share this connection or app with other users

they will have full control on this connection and they can use these username and password that I provided to create other apps or read or write data into this particular SQL database if I select Windows authentication not shared that will save me because even if I'm using the username and password here in this app context this is good but if someone is going to try to reuse this particular connection for other app they need to provide the username and password to authenticate them themselves okay so hope you understand these four point the Azure ad integrated

and windows authentication notes not shared is called explicit shared connection and say called server authen authentication in Windows authentication called implicit authentication TP very very important point please make sure when you're creating the app in the production and for your Enterprise keep in mind select the right authentication type to make sure that your data is secure uh you are not by mistake enabling the access to entire database to everyone who have access to the power app okay most of the cases my recommendation use a your ad integrated until unless you have a different requirement and

scenario where you have to use the SQL Server authentication and if you are planning to use CLE server authentication make sure the database and that you're going to connect has the data that you are okay to expose to the user that you are actually sharing your app with okay so I'm going to select the Azure ad integrated click create this is going to create the connection okay so my connection is created then it's going to ask me you the SQL server name and database for this demo I have created one sample database in my Zer

okay okay so I have this sample database that I am using for this purpose I'm going to copy the server name and the database name Okay click connect okay and once you connect it's going to list on all the tables that you have in your database you can select the the table on which you want to create your your app okay so I'm going to select the product say connect now because I have selected the app from SQL the power app is automatically going to create all the screens and and forms for me uh it

doesn't matter if you are not really using starting from SQL you can still go ahead and create a blank app and then create the connection and then you can manually add all of these form that you want or the functional that you want so as you can see here my app is ready if I'm going to play this I can see all the information coming straight from SQL I can see the data I can update the information as I want to 30 click save beautiful right yes so by this simple step you have your fully

functional app that is connected to your SQL I can if I run it I can create a new item to the table I can edit any existing item or I can delete and I can also search for example if I search for fork right so I can search and you can also do a lot of other things like for example here we are using search if you don't want to use search if you want to apply filters you can also use the filter so for an example let me show you quickly I can add another

screen okay and then in insert a new Gallery in the data source I'm going to select the same data source that we have just selected for our app right it's the same data and what we're going to do we're going to apply filter so I can use the filter function and I'll say filter this data where let's say okay so I just filter this entire thing where list price is greater than 100 and if I change this to the list price so you can see everything is more than 100 I can change this to let's

say 1,000 right so if I change this to 1,000 then you can see the different result now the question why I'm showing you these filters and other function that I can use because you cannot directly write a SQL query in power app but instead I can use these filter and another function that we have short search look up okay in in some cases where you absolutely need to use your custom query then you have couple of options that I'm going to talk about number one you can call a power automate from the Power app and

then use the power automate to run the query else you can also use the view so if you know the SQL view that you can create uh pre-create those View and have them ready you can connect your app to those view directly and let me show you that quickly so if I go to my database in my database I have created this this view right so this is the simple View that I've created what I can do in the power app I can create these different view in my SQL and then let power app connect

to that particular View and let me show you how you can do that so if I go to the data again I have to create a new connection because I need to select that view so I can go here and I'm going to use my existing connection here okay so this is the connection that I'm already using because I need to reselect the table and if I look at this table in this table list you can see those view you can see that small V that is the The View that we have so I have

to get all categories product and description in those views so what you can do from Power app you can connect to the table that is pretty standard but you can also connect to a particular view I'm going to say connect okay and then let's do that let me change this and now I have my view data listed here this one because it doesn't has the list price so maybe product category name awesome right what else you cannot do also you cannot trigger a stored procedure if you have in my case I have some store procedure

that I created uh you can't really directly trigger those store procedure from the app for that you need to call the flow now because we are done with the power app what it can do with the SQL now let's jump onto the power automate and see what we can do with SQL connector in power automate so I'm going to log into my flow. microsoft.com and I'm going to create a new flow so click on create okay I'm selecting manually trigger flow uh just to demonstrate but it can be automated flow also and I will show

you what all trigger that we have for SQL new stab and then I'm going to search for SQL it's going to give me the SQL connector and here I can see all the different actions that I can do in power automate for SQL let's talk about the trigger triggers are very standard it's when item is created in a table or when an item is modified so you can create your flow based on the trigger that's happening in your table okay and then from the action you can do all of this so you can delete you

can you can insert you can update and you can also get all the items so let's try with get rows okay when you do that you will end up on this screen where you need to provide the SQL server name database name and table but before you you even do that click on these three dots and make sure the connection that you have exist and working and if it's not you can click on add new connection now remember remember also one thing if you have already created a connection either in power app or in PL

before you can reuse that if you don't have it click on add new connection it's going to ask you the same question that we have seen in the power app you need to select the Authentication type in flow you are much safe because what going to happen if you are creating an automated flow right that's going to trigger based on some action you can select either ad integrator or SQL Server authentication because you are not sharing the flow connection with other users so they can reuse it you can still select the SQL Server authentication here

in the flow until unless you are planning to add lots of people as a owner for this flow and sharing your connection with them if you're not explicitly sharing your connection you are good to select one of these option that make more sense in your case uh to create the flow that is connected to the SQL okay okay so you select the authentication type pretty straightforward click sign in and then it's going to create the connection that you can use and you should be able to see no error check mark here okay then you need

to select provide the SQL Server name okay so I'm going to copy my SQL server name click on addit custom paste it here it should show me my database name automatically so that's my database name and then when I click on the table name I can see all of these table including the view right so as I as I talk about the power app that you can connect to the view you can also connect to a particular view in power automate also so here I'm going to select the customer and if I click on show

Advance option these are the different thing that you can apply on top of it uh because right now we are using an action called get rows so I can order by I can skip I can provide my filter like we doing in power app uh where I can say okay if this particular column equal to this then only bring the data click save and run it okay my flow got completed successfully and if I look at the data here uh it should be entire thing that I have in my SQL is already listed here what

other things you can do in automate that you couldn't do in power app if I search for SQL again I can execute uh SQL query okay database name and then you need to provide your query here so this simple query select off 1,000 from this table okay and we going to run it okay so this completed and you can see the result so it's very simple to just run a query SQL query from Power automate and one more thing that you can do if you have the stored procedure that you have written if you search

SLE you can also execute a stored procedure so in my SQL if I go back here so I have the simple store procedures and if I run it it's going to select uh entire thing from this particular table what I can do I can fire this query from the power automat so if I go here I need to provide the server name and then need to select the database where you want to run and then in this drop down you you should be able to see all of the store procedure that you already already have

in your SQL and the one that we are trying to run is this flow demo again I'm going to click test run okay so that also completed successfully and if I click on this you can see there's lots of data that that we got so as you can see here this is pretty straightforward simple to use SQL connector and once you have it you can you can do all of these different operations or the actions that you have in the SQL connector so this is all for this video this video is covering more on how

you can create connection and how you can use this for basic operation that we have discussed if you guys wanted to see more complex scenario where you want to see how you can have those filter query and more complex example leave a comment and let me know I'll create another video talking about more complex scenario thank you thanks for watching keep learning keep watching thank you