Azure SQL Managed Instance Private Endpoint - Importing Data Via SSMS with OLE DB Driver

Question

Azure SQL Managed Instance Private Endpoint - Importing Data Via SSMS with OLE DB Driver

Carmelo LoPresti 20

I have an Azure SQL Managed Instance that is using a Private Endpoint which is in Preview.

The Local vNet endpoint and the Private Endpoint are on the same vNet, but different Subnets. Ultimate goal is to disable Public access all together.

I've reviewed the documentation on creating a Private Endpoint on a SQL Managed Instance, and I am able to log into SSMS with the private endpoint link, but only if I select "Trust Server Certificate." That works fine.

The issue is when I try to use the "Data Import" feature on a Database hosted on the MI using OLE DB Driver option, and input the same connection string (private endpoint, trust server certificate, encryption disabled), I get the error below. Connecting via the public endpoint works fine.

I believe it has to do with the certificate mismatch in the SQL name, however, why does it allow me to log in via SSMS, but will not allow a data import with the OLE DB driver feature? Is this a supported configuration?

[Microsoft OLE DB Driver for SQL Server]: Client Unable to establish Connection

[Microsoft OLE DB Driver for SQL Server]: SSL Provider: The target principal name is incorrect.

Oury Ba-MSFT 21,126 Reputation points Microsoft Employee Moderator

2023-05-08T17:36:06.2333333+00:00

Carmelo LoPresti Thnak you for reaching out.

I am trying to reproduce this issue from my end. I will get back shortky.

Regards,

Oury
Carmelo LoPresti 20 Reputation points

2023-05-10T17:38:16.0666667+00:00

Would like to point out one item - Using SQL Authentication works for the SQL data import using the private endpoint - when I use Azure AD with MFA, it does not work with the private endpoint.
Oury Ba-MSFT 21,126 Reputation points Microsoft Employee Moderator

2023-05-10T23:35:21.72+00:00

Carmelo LoPresti

This will often happen when the hostname part of the FQDN as sent in the connection string is not an exact match to the name of the instance.

You're getting through the private endpoint, but then SQL MI's gateways reject the connection. Can you please double-check that the OLEDB connection string does in fact contain the exact instance name before the .plink.<dns-zone>.database.windows.net bit?

Regards,

Oury
Oury Ba-MSFT 21,126 Reputation points Microsoft Employee Moderator

2023-05-12T20:43:17.42+00:00

Carmelo LoPresti

Checking if you had the chance to review my above comment.

Regards,

Oury
Carmelo LoPresti 20 Reputation points

2023-05-13T01:29:31.0733333+00:00

Yes it does contain the full instance name. However, for this instance I am not using the vNet private endpoint. I am using the Private Endpoint currently in preview. It is on the same vnet as the SQL MI.
Carmelo LoPresti 20 Reputation points

2023-05-18T18:14:07.2333333+00:00

Thank you very much for your response and confirming the workaround.

Is there a reason that Azure AD Auth works with the plink endpoint when FIRST logging into the database via SSMS? As long as the Trust Server Certificate is selected, in connection properties tab, it works.

It only fails with the SSL error when trying to use the Import Data feature in SSMS using the SQL OLEDB Driver. Is this issue specific to using the OLEDB driver or SQL Import feature?

Answer accepted by question author

0 additional answers

Your answer

Oury Ba-MSFT 21,126 Reputation points Microsoft Employee Moderator

2023-05-08T17:36:06.2333333+00:00

Carmelo LoPresti Thnak you for reaching out.

I am trying to reproduce this issue from my end. I will get back shortky.

Regards,

Oury
Carmelo LoPresti 20 Reputation points

2023-05-10T17:38:16.0666667+00:00

Would like to point out one item - Using SQL Authentication works for the SQL data import using the private endpoint - when I use Azure AD with MFA, it does not work with the private endpoint.
Oury Ba-MSFT 21,126 Reputation points Microsoft Employee Moderator

2023-05-10T23:35:21.72+00:00

Carmelo LoPresti

This will often happen when the hostname part of the FQDN as sent in the connection string is not an exact match to the name of the instance.

You're getting through the private endpoint, but then SQL MI's gateways reject the connection. Can you please double-check that the OLEDB connection string does in fact contain the exact instance name before the .plink.<dns-zone>.database.windows.net bit?

Regards,

Oury
Oury Ba-MSFT 21,126 Reputation points Microsoft Employee Moderator

2023-05-12T20:43:17.42+00:00

Carmelo LoPresti

Checking if you had the chance to review my above comment.

Regards,

Oury
Carmelo LoPresti 20 Reputation points

2023-05-13T01:29:31.0733333+00:00

Yes it does contain the full instance name. However, for this instance I am not using the vNet private endpoint. I am using the Private Endpoint currently in preview. It is on the same vnet as the SQL MI.
Carmelo LoPresti 20 Reputation points

2023-05-18T18:14:07.2333333+00:00

Thank you very much for your response and confirming the workaround.

Is there a reason that Azure AD Auth works with the plink endpoint when FIRST logging into the database via SSMS? As long as the Trust Server Certificate is selected, in connection properties tab, it works.

It only fails with the SSL error when trying to use the Import Data feature in SSMS using the SQL OLEDB Driver. Is this issue specific to using the OLEDB driver or SQL Import feature?

Answer 1

Oury Ba-MSFT 21,126 Microsoft Employee Moderator

Carmelo LoPresti

Sorry for the delay in response to your question. I was able to check internally and seems like the issue is with Azure Active Directory authentication and server certificates. To work around this, either move the private endpoint(s) to a different VNet giving them the correct DNS zone, or peer this VNet with a new VNet in which, again, the correct DNS zone will exist.

Our Product Group is aware of this and will be fixing in due time.

Regards,

Oury

Share via

Azure SQL Managed Instance Private Endpoint - Importing Data Via SSMS with OLE DB Driver

0 additional answers

Your answer