iot edge sql

Question

iot edge sql

Newbie 41

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

AshokPeddakotla-MSFT 10,976 Reputation points

2020-12-24T11:23:42.217+00:00

@Newbie
Can you elaborate your question bit more to understand better? What exactly you mean by "azure sql database hosted in cloud" Are you referring to Azure SQL Database or Azure SQL Edge or SQL Server module running in Azure IoT Edge?

Please see Tutorial: Store data at the edge with SQL Server databases and let us know if you are looking for the same?
Newbie 41 Reputation points

2020-12-24T13:26:36.837+00:00

@AshokPeddakotla-MSFT Im refering to Azure SQL Database. I want to querry Azure SQL Database within iot edge module running on raspberry with pyodbc

AshokPeddakotla-MSFT 10,976 Reputation points

2020-12-24T11:23:42.217+00:00

@Newbie
Can you elaborate your question bit more to understand better? What exactly you mean by "azure sql database hosted in cloud" Are you referring to Azure SQL Database or Azure SQL Edge or SQL Server module running in Azure IoT Edge?

Please see Tutorial: Store data at the edge with SQL Server databases and let us know if you are looking for the same?
Newbie 41 Reputation points

2020-12-24T13:26:36.837+00:00

@AshokPeddakotla-MSFT Im refering to Azure SQL Database. I want to querry Azure SQL Database within iot edge module running on raspberry with pyodbc

Answer 1

Sander van de Velde 16,126 MVP

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.

Newbie 41 Reputation points

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

thanks @Sander van de Velde .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 16,126 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 16,126 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 , thank you for your answer
Newbie 41 Reputation points

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

@Sander van de Velde 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 16,126 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

iot edge sql

0 additional answers

Activity