Azure SQL Database - Elastic Query to Azure VM SQL instance - SSL error

Question

I need to connect from an Azure SQL Database to an Azure VM that has a SQL Server instance installed. Since linked servers are not supported by Azure SQL Databases, I went with the elastic query approach. I managed setting up the EXTERNAL DATA SOURCE and allowed connections on port 1433 but when executing the most simple query I get the following error message: "Error retrieving data from one or more shards. The underlying error message received was: 'A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)'."

Is this a supported scenario? In the end I want to pull data out of the VM into the Azure SQL Database.

Also: how can I be sure that the network traffic will stay within the Azure data center when using the public IP of the VM so that I won't be charged for external traffic between the 2 SQL servers, or is this irrelevant as both are hosted in Azure?

Answer 1

Re the exception on your query: Based on the error message, this looks like the SQL Server on your VM does not have a CA signed certificate installed. Elastic query enforces encryption on its connections and it sets Trust Server Certificate to false. Installing a CA signed certificate should fix that issue from what I can tell by looking at the error.

Re network traffic: Both the VM and the Azure SQLDB database are created in a particular region. If you choose to create them in the same region, then the network traffic will be internal to the data center. If you create them in different regions, you will incur cost for cross-data center traffic.

Hope this helps.

Thanks, Torsten