search

MicrosoftDocs / azure-docs

Open source documentation of Microsoft Azure
https://docs.microsoft.com/azure
Creative Commons Attribution 4.0 International
10.22k stars 21.37k forks source link

Linked service and private endpoint from Synapse workspace to Azure SQL managed instance #75593

Closed bh3r1th closed 4 months ago

bh3r1th commented 3 years ago

I created a Synapse workspace with managed virtual network enabled and "Yes" for "Allow outbound data traffic only to approved targets".

I got this error message when I tried to create a Linked Service for Azure SQL managed instance from my synapse workspace.

Cannot connect to SQL Database: 'instancename.public.0581668b77c7.database.windows.net,3342', Database: 'ServiceCentralData', User: 'userID'. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access.
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.), SqlErrorNumber=10060,Class=20,State=0,
A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond

I also noticed that I can create private link from my Synapse workspace to Azure SQL single database but not to Azure SQL managed instance.

Apparently, I can't access Azure SQL managed instance from Synapse workspace when Data exfiltration protection is enabled for the workspace. Is that correct? If not, how to create Linked Service and private endpoint to Azure SQL managed instance from workspace that has Data exfiltration protection enabled?

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

SamaraSoucy-MSFT commented 3 years ago

Thanks for the question! We will review and get back to you shortly.

bh3r1th commented 3 years ago

BTW, I created new Synapse workspace in managed virtual network but without data exfiltration protection. In that new workspace, I'm able to create linked service for SQL managed instance.

SamaraSoucy-MSFT commented 3 years ago

One of the downsides of managed instances is that they loose some of the integration options between services. In this way it behaves closer to SQL Server running on a VM than it does Azure SQL.

The instructions for creating a private link from the Data Factory docs should also work for Synapse: https://docs.microsoft.com/en-us/azure/data-factory/tutorial-managed-virtual-network-sql-managed-instance

It does take a couple extra resources to create the connection. You need to put a load balancer in from of your SQL instance, and then a generic Private Link resource in front of that- you should then be able to connect to a Managed Instance through that link.

bh3r1th commented 3 years ago

@SamaraSoucy-MSFT Thanks for the response.

I'll test the instructions in the link you provided, and get back to you if I face any problems.

SamaraSoucy-MSFT commented 3 years ago

@nanditavalsan Can we please look at improving this doc to make it clear that Azure SQL and managed instances aren't treated the same when exfiltration protection is on?

nanditavalsan commented 3 years ago

reassign:@RonyMSFT

bh3r1th commented 3 years ago

@SamaraSoucy-MSFT It would be definitely helpful to get the documentation updated. I appreciate you for taking lead on that.

nanditavalsan commented 3 years ago

reassign: @RonyMSFT

bandersmsft commented 4 months ago

Thanks for your dedication to our documentation. Unfortunately, at this time we have been unable to review your issue in a timely manner and we sincerely apologize for the delayed response. We are closing this issue for now, but if you feel that it's still a concern, please respond and let us know. If you determine another possible update to our documentation, please don't hesitate to reach out again. #please-close