Azure SQL DB connecting to local SQL

0
votes

I'm trying to connect from my azure SQL DB to link to the local SQL DB and create some linked tables that i can run some reports on within the Azure environment.

I've tested that i can follow the steps and successfully connect to another SQL Db within my Azure environment and this is working fine, so the steps i am following are correct.

However when i try to run the same steps connecting against my local DB i receive the following error:

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.)

Steps i'm following are

  • Create Database scoped creds (using my same details that can log on via Management Studio)
  • create external data source
  • Create external table using the details created above.

This creates a external table within my azure database but when i try to select from this table i get the following message

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.)

So two questions really as i've googled for some time and a lot of the posts I've found seemed to be old.

  • Does Azure support connecting to a local DB?
  • And if so any ideas what this message means?

I've read up about this and the only comments i could see mentioned TrustServerCertificate=True to false. However i'm not sure where this is set and where the change is required? to me this looks more like a change within a connectionstring for coding rather than a db to db?

1
sql-serverdatabaseazureazure-sql-database
The error message says nothing about credentials. It complains about the certificate used for connection encryption and says that it's not trusted. You have to tell the database to trust it. That's explained in all connection tutorials simply because the test certificates used in the tutorials are untrusted. The relevant articles may be old because this isn't a new feature – Panagiotis Kanavos
Check for example Enable Encrypted Connections to the Database Engine. Now, if the question is Can I make Azure SQL trust an untrusted test certificate I created on my machine the answer is probably no, for obvious security reasons – Panagiotis Kanavos

1 Answers

2
votes

Elastic queries allow a query to span multiple Azure SQL Databases but on-premises SQL Server instances can not be part of elastic queries.

My suggestion is to perform replication of on-premises databases to Azure SQL Database or use SQL Data Sync so you can have an always updated copy of on-premises databases on Azure SQL Database and you can then query them as local tables or with elastic queries on Azure SQL Database.