SSMS on VM unable to locate Machine Data Sources

Question

I'm trying to add a Linked Server in SSMS to a SQL Managed Instance on a VM in Azure using an OLE DB Provided for ODBC. I have created both 32 and 64 bit System and User DSNs but none are located when I try to create the Linked Server in SSMS.

I have done this before on server machines in AWS and on-premise but this is the first time with an Azure VM.

I do have a Integration Runtime ODBC connected to the VM's Machine Data Sources and able to pull data into Data Factory. But Ideally I'd like to be able to add the Linked Server within SSMS.

Thank you,
Dan

Answer 1

In your original post you said: "I 'm trying to add a Linked Server in SSMS to a SQL Managed Instance". An SQL Managed instance runs SQL Server which is why I suggested that you should use an OLE DB provider for SQL Server.

Now you are saying that you want to connect to something completely different. In that case, you would need an OLE DB provider for that data source, and if there isn't any but there is an you would use MSDASQL + ODBC.

I am getting confused over what your setup is. Are you running an Azure SQL Managed instance, or you are you running SQL Server in an Azure VM? In the latter case, you should be able to set up the linked server with help sp_addlinkedserver. If you are running a Managed Instance, you cannot create a linked server to NetSuite, as Microsoft will not permit you install the ODBC driver.

Answer 2

Please configure your Azure VM to connect to the Azure Managed Instance by following the steps provided in this tutorial.

You can also create a VM and add it to an existing VNET where the Azure Managed Instance was created. Please see this ARM template. You can easily implement the template from here.

Answer 3

@Alberto Morillo thank you for sharing the tutorial!

I had set up the MI in that way originally but the VM created from the template was publicly open and my IT team asked me to use an existing VM that is more secure.

We created a subnet for the managed instance on the Virtual Network that the VM runs in and I am able to access the MI from SSMS on the VM.

Is there a way use the template but set the networking to more secure?