Using Teams & Flow in the Government Cloud: Talk to a SQL Database in Azure Gov from a Teams chat

Bot integration in Teams isn't available yet in the Government Cloud [as of this writing], but we can still 'talk' to other systems (like a SQL database) using Flow. Let's consider the scenario where my friend Tony Stark and I are both a part of the Department of Transportation, and we're tracking the number of potholes on streets throughout the State. Tony decides to track and store the pothole data in a cloud-based SQL database that he has created in our Azure Government tenant. I'm a project manager who wants to query this data from my Teams environment, without special tools or having to understand database queries. Let's look at one approach.

First, let's see how Tony has created the database. Using Azure, he creates a small and simple SQL Database table called potholes1:

This database stores street names and the number of potholes on that street...

Now that the database is created and being populated with data, let's see how Flow can help me see that data in Teams. We've created a Channel in Teams to be used specifically for 'talking' to the data - in this scenario, whenever someone posts the name of a known street name in the Channel, Flow will respond with an automated post showing the number of potholes on that street by querying the SQL database. Our end result will look something like this:

To do this, Flow will look for any post in this Channel, and use the content of the post as an input in a SQL query. The entire Flow looks like this:

If we break it down, we'll see that we need to use the Compose action to get output (like the Msg ID and the result of our SQL query) that we'll need at the end of our Flow. But the key action is our SQL query action, which contains our query:

However, this only works with the right connection information. Flow is able to create a connection to our SQL Database using its Connections capability:

Finally, we post our automated Teams response message by using the original Msg ID (so we know which conversation to Reply to) and the [unformatted] response from our SQL query, both of which were exposed for us by the Compose action:

The result is a Channel that anyone in our Team can use to "ask the database" about the current number of potholes for a given street:

With this functionality now in place, our admin Tony Stark can spend less time fielding email requests re: pothole data, and more time... fighting bad guys? :)

For more articles in this series, follow #TeamsFlowInGCC in Linked or Twitter, or view this series in the Public Sector TechCommunity Blog: