Hi everybody, Device such as raspberry can run sql server locally using azure sql edge. However, is it possible to connect to azure sql database hosted in cloud using pyodbc inside modules. Thanks

Hello @Newbie , To access Sql Azure in the azure cloud, you need to provide public access from the internet. Did you look at these firewall settings ? Keep in mind that if you are able to access the database, the whole world can access it. Only eg. the credentials and IP filtering keeps your database secure. Three alternative ways are: Hiding your database behind a Rest API (eg. an Azure Function with HTPPTrigger (secured over HTTPS)) A more elaborate roundtrip where your device sends a request in a telemetry message and the response is sent eg in a direct method Sql Azure support for a virtual network The second solution could be challenging but you make use of the single secure channel which is already setup by the IoT edge.

iot edge sql - Microsoft Q&A

Accepted answer

Sander van de Velde | MVP 31,211 Reputation points MVP

2020-12-24T13:59:17.397+00:00
Hello @Newbie ,

To access Sql Azure in the azure cloud, you need to provide public access from the internet.

Did you look at these firewall settings?

Keep in mind that if you are able to access the database, the whole world can access it. Only eg. the credentials and IP filtering keeps your database secure.

Three alternative ways are:

Hiding your database behind a Rest API (eg. an Azure Function with HTPPTrigger (secured over HTTPS))

A more elaborate roundtrip where your device sends a request in a telemetry message and the response is sent eg in a direct method

Sql Azure support for a virtual network

The second solution could be challenging but you make use of the single secure channel which is already setup by the IoT edge.
Please sign in to rate this answer.
Newbie 41 Reputation points

2020-12-24T16:38:55.803+00:00

thanks @Sander van de Velde | MVP .Incase i have set up any ip would be able to connect to my azure sql database, i just need to expose, bind port 1433 and i could query within modules right?

Sander van de Velde | MVP 31,211 Reputation points MVP

2020-12-26T23:29:10.14+00:00

Hello @Newbie
Using VS2019 and a C# app.

I create Sql Azure database/server:

I gave it no public access at first:

other default settings:

Once created, I added a table using the Query Editor in the Azure portal:

I inserted a row:

In the query editor, I tested the select:

I accessed the table in VS / C# code:

I failed, so I opened the firewall

It worked:

Sander van de Velde | MVP 31,211 Reputation points MVP

2020-12-26T23:33:02.377+00:00

Use the SQL Server connection string:

Still, keep in mind to use one of the alternatives.

You are not that much in control using a public endpoint like this. Really consider hiding the database behind an Azure function or a WebApp!

Newbie 41 Reputation points

2020-12-27T06:00:24.087+00:00

@Sander van de Velde | MVP , thank you for your answer

Newbie 41 Reputation points

2020-12-27T06:09:41.367+00:00

@Sander van de Velde | MVP before asking the question, i had successfully query azure sql database using python code in vscode. As i want to make my raspberry pi an iot edge device, i want to achieve same result. So i want to know, am i able to do such thing from within the modules consider i have already set up firewall setting and bind port 1433 to host?

Sander van de Velde | MVP 31,211 Reputation points MVP

2020-12-27T14:21:32.77+00:00

From within a container, you should be able to make outbound calls to the internet (if the firewall of the host device allows this too).

Just try it :-)

If an answer is helpful, please "Accept answer" or "Up-Vote" for the same which might be beneficial to other community members reading this thread.

Newbie 41 Reputation points

2020-12-27T15:04:11.577+00:00

thanks @Sander van de Velde | MVP
Sign in to comment

Share via

iot edge sql

0 additional answers