Connect to on-prem sql server using Azure Relay

Santhi Dhanuskodi 145

Hi,

I would like to connect to a on-prem sql server and execute T-SQL commands to listen and receive SQL broker messages from Azure cloud hosted .net application.

How can i connect to on-prem sql server and execute these sql commands and receive the responses? Looking for solutions and sample code.

I have connection string specified in my .net app...how should I change my connection string or any other code?

My current code in.net (without any relay/hybrid connection)is in below picture. Should I modify this code if I use hybrid connection/Azure Relay? or it is only a configuration required at azure rely and hybrid connections?

User's image

ajkuma 22,411 Reputation points Microsoft Employee

2024-04-01T11:23:16.6466667+00:00

To better assist you on this, just to clarify, are you leveraging Azure Relay of Azure Service Bus platform or Azure Fluid Relay ? Could you please more details about your requirement?
Santhi Dhanuskodi 145 Reputation points

2024-04-02T07:56:15.6766667+00:00

it is Azure Relay of Azure Service Bus platform.
Santhi Dhanuskodi 145 Reputation points

2024-04-02T10:51:26.71+00:00

@ajkuma, cold you please reply
ajkuma 22,411 Reputation points Microsoft Employee

2024-04-02T13:22:00.9633333+00:00

Thanks for the follow-up and update. I'm checking on this and will get back to you shortly.

I have also added additional tags to receive insights from the targetted audience/SMEs
ajkuma 22,411 Reputation points Microsoft Employee

2024-04-04T14:11:36.2533333+00:00

Santhi, Just checking in to see if you had got a chance to see the previous response by Bruce. If the answer helped (pointed, you in the right direction) > please click Accept Answer Or please share the requested/more info to help you better.
Santhi Dhanuskodi 145 Reputation points

2024-04-09T16:50:15.72+00:00
@ajkum, did you get any answers? I am still trying to find out...

2 answers

Bruce (SqlWork.com) 56,531 Reputation points

2024-04-01T15:41:21.96+00:00

only the connection string is modified to the relay endpoint.
Please sign in to rate this answer.
Santhi Dhanuskodi 145 Reputation points

2024-04-02T08:48:22.1633333+00:00

Thanks! If we have to use API management in the middle between .net solution and Azure Relay, how do we achieve that? We want to standardize by using API in API management service, instead of directly connecting to Azure Relay. API management service will push all requests to Azure relay. any suggestions?

Bruce (SqlWork.com) 56,531 Reputation points

2024-04-02T15:30:35.6333333+00:00

a sql connection uses a private protocol (TDS), so your API management would need to support being a stream TCP proxy rather than http/https api proxy.

also the azure relay adds two network proxies (one on azure, and one on-prem) to the connection, not sure you want to another.

Santhi Dhanuskodi 145 Reputation points

2024-04-09T12:55:00.2566667+00:00

@Bruce, We are trying to send only the sql command and connection string from .net app to API as json text, API will inturn post the request to Azure relay endpoint. Is this possible? Azure Relay has the ability to process this request? We dont want the client app to have the code to establish the connection and run the query. As relay has the ability to do this, we want to leverage with that. But not sure how to use that.

To check this, We are trying to test with simple http request to azure relay endpoint.

Below is the sample http request i am trying, will it work?

POST <Azure Relay Endpoint>

Authorization:<Auth-Header>

Content-Type: application/json

{

"ConnectionString": "Server=<servername>:1433;Database=<dbname>;User Id=<userid>;Password=<pwd>;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;",

"Query": "SELECT * FROM <table>"

}

Bruce (SqlWork.com) 56,531 Reputation points

2024-04-09T15:15:08.5733333+00:00

SqlServer uses TDS not http. you would need to write an onprem web api that supported that request, and change the azure code to use HttpClient instead of SqlCommand. Then point the azure relay to your api endpoint.

Santhi Dhanuskodi 145 Reputation points

2024-04-09T15:19:31.2266667+00:00

Ok. We dont need azure relay or hybrid connection in that case.

We assumed that relay and hybrid connection can establish connection to on-prem sql server, it can process sql commands as well. Looks like that is not correct.

The whole point of writing this .net app and host in AKS(Kubernetes) is to avoid using any on-prem API or application. As on-prem application requires support and maintenance is difficult, we just want a simple .net application to be hosted in Azure which can be used to connect to on-prem sql server and execute sql commands and get the results.

How do I achieve this?

Bruce (SqlWork.com) 56,531 Reputation points

2024-04-09T16:42:34.6566667+00:00

the azure relay supports SqlServer. it works like a point to point vpn (virtual lan) to the SqlServer. it does not change the protocol (TDS) used to access SqlServer.

your azure code can use azure relay to access an on-prem SqlServer via the sql client library. only the connect string is modified.

if you want to use an API management tool, it needs to support stream socket connections. it will not be able to inspect the stream, only the connection requests.

Santhi Dhanuskodi 145 Reputation points

2024-04-10T06:32:48.3233333+00:00

Thanks Bruce. If I choose an option to use .net app with existing code(sql client library) and modify the connection string to relay endpoint... how the connection string would look like? The usual string? Only the server is changed to relay endpoint? database name and authentication remains the same?
Sign in to comment
Bruce (SqlWork.com) 56,531 Reputation points

2024-04-09T19:54:02.98+00:00
for an azure app to connect to you on-prem sqlserver, you need a network connection between them. you have several options:

expose your sqlserver to directly to the internet so azure can connect to it. not recommend.

install a vpn between azure and your internal network. this requires special on-prem hardware. see: https://learn.microsoft.com/en-us/azure/vpn-gateway/tutorial-site-to-site-portal

azure express route - an expensive direct connection from your data center to azure. see: https://learn.microsoft.com/en-us/azure/expressroute/expressroute-erdirect-about

azure relay. create a point to point "vlan" with no custom hardware. config the azure relay, and install and configure the on-prem software (windows only). the on-prem software only needs outgoing internet access. see:
https://learn.microsoft.com/en-us/azure/azure-relay/relay-what-is-it

note: none these solutions magically turn your sqlserver in a http api server.
Please sign in to rate this answer.
Santhi Dhanuskodi 145 Reputation points

2024-04-10T09:36:23.0666667+00:00

Thanks a lot Bruce!

I am trying to visualize the connection and passing of data between client and on-prem sql server over Azure Relay. If I want to test Azure relay manually for the SQL connection and commands execution, what is the best way? How data being transmitted to on-prem sql from client using relay? what protocols used?

How SQLclient library makes connection to on-prem server over relay?

Bruce (SqlWork.com) 56,531 Reputation points

2024-04-10T17:52:42.3066667+00:00

as I said, the hybrid relay acts like a virtual lan. the main feature is the on-premise relay component you install opens the connection to the azure relay component, so only an on-premise out-going connection is required.

the relay acts like any tcp proxy, it just passes the packets. the azure client connects to the azure endpoint (hostname) instead of the sqlserver hostname. I typically use the same name in azure. see docs:

https://learn.microsoft.com/en-us/azure/app-service/app-service-hybrid-connections

note: your azure sqlclient does not know it using the relay, just as it does not know about network switches and routers.
Sign in to comment