I use an on-prem SQL server. I have registered a gateway and then created a new connection to use the gateway with SQL authentication. But when creating a flow, if I add a "Get rows (V2)" action, under server name drop down, the new connection cannot be selected. If I create a new flow from a template and create a SQL connection that way (which gives you Windows authentication or basic authentication so I picked basic), then the connection can be selected in Get rows (V2). Is this a normal behavior? How come there are different authentication options between the 2 ways to create a SQL connection? What is the proper way to do this?
Apologize for a basic question.
Thank you very much in advance.
I have registered the data gateway for the on-prem SQL server in my O365 account so it was showing up under Data > Gateways.
I then started creating a new flow (scheduled - from blank). I added a "Get rows (V2)" step but the server name drop down didn't show my new gateway. I realized that I need to configure a new SQL connection so I went to Data > Connections > New connection and selected "SQL Server". It provides 3 authentication types (Azure, SQL, Windows) so I selected SQL and entered the SQL login name and password. The new connection was added to the list and it said "Connected". I went back to "Get rows (V2)" but the server name drop down didn't show the new connection so I couldn't create a new flow to use the SQL server.
I then went to New > "Create from template" and selected the template "Notify about rows in a SQL DB". The template says "This flow will connect to" and lists connectors. I clicked "Create" next to SQL server, checked "Connect via on-premises data gateway" and added all the information. Unlike the case when creating a new SQL connection from scratch, this method within a template provides Windows or Basic authentication types only. I selected basic and created the connection. I cancelled the creation of the flow since the connection was already created and saved. At this point, I was able to select the connection from "Get rows (V2)" step and proceed with the creation of the flow.
Check out the announcement of Power Platform content at Microsoft Ignite!
Join us for an in-depth look at the new Power Automate features and capabilities at the free Microsoft Business Applications Launch Event.
Features releasing from October 2020 through March 2021