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.