question

CarlosEduardoVasquez-4444 avatar image
0 Votes"
CarlosEduardoVasquez-4444 asked AlbertoMorillo commented

Error trying to connect an azure sql database with an sql instance installed on an azure virtual machine

I am trying to connect from my azure SQL database to link to the SQL database installed on an azure virtual machine
If I try to connect to another azure database, the connection is successful
However, when I try to run the same steps to connect to the virtual machine database, I get 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.).

The steps I'm doing to create the connection are as follows:
In the sql installed in the virtual machine:
• Create a login and a user in sql with permissions on the database
In the sql azure:
• The encryption key is created
CREATE MASTER KEY ENCRYPTION BY PASSWORD='<password>'

• Credential is created
CREATE DATABASE SCOPED CREDENTIAL credencialdemootro
WITH IDENTITY = '<loginusuariomaquinavirtual>',
SECRET = '<password>'

• The external datasource is created
CREATE EXTERNAL DATA SOURCE [deexternal] WITH (TYPE = RDBMS, LOCATION = N'servidormaquinavirtual', CREDENTIAL = [credencialdemootro], DATABASE_NAME = N'basededatos')

When trying to make a query from the sql azure database connecting to the one installed in the virtual machine, the error is generated:

EXEC sp_execute_remote N'deexternal', N'select * from table'

Error retrieving data from shard [DataSource=<server> Database=<database>]. 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.)'.

azure-sql-databaseazure-sql-virtual-machines
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

AlbertoMorillo avatar image
1 Vote"
AlbertoMorillo answered AlbertoMorillo commented

You can only create elastic queries to query across databases that are Azure SQL Database (PaaS) only. Elastic queries do not support connections to SQL Server VMs on Azure (IaaS) or SQL Server instances on-premises.



· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@AlbertoMorillo Thanks for your contribution.
@CarlosEduardoVasquez-4444 Did the answer provided helpful. If yes, please mark as accepted answer. Otherwise let us know how we can better assist.

Regards,
Oury

0 Votes 0 ·

@CarlosEduardoVasquez-4444 Did the answer provided helpful. If yes, please mark as accepted answer. Otherwise let us know how we can better assist.

Regards,
Oury

0 Votes 0 ·

Thank you @OuryBa-MSFT for asking the OP about his satisfaction with the answers provided.

0 Votes 0 ·