Connecting SQL Server Management Studio to Dynamics 365 via ODBC Driver
In order to avoid incorrect integration with MS SSMS, the working environment must meet the following conditions:
You can use the Microsoft SQL Server Management Studio to connect your Dynamics data to an SQL Server instance. Linked Server is a tool of MS SQL Server that allows to execute distributed queries to refer tables stored on non-SQL Server datbase in a single query. With linked servers, you can execute commands against different data sources such as Dynamics and merge them with your SQL Server database. You can create a linked server with one of these methods: by using the options in the Object Explorer or by executing stored procedures.
Below are major advantages of using SQL Server Linked Servers to connect to Dynamics:
You can follow the steps to create a linked server for Dynamics in SQL Server Management Studio by using Object Explorer:
The linked server will appear under the Linked Servers in the Object Explorer Pane. You can now issue distributed queries and access Dynamics databases through SQL Server.
Disable the Allow inprocess option of MSDASQL OLE DB Provider for ODBC Drivers. For this, find the MSDASQL provider in the list of Linked Servers and double-click on it
In the appeared Provider Options window, clear the Allow inprocess checkbox:
Create a new Linked Server
Make sure to select Microsoft OLE DB Provider for ODBC Drivers and specify the following parameters:
The Dynamics tables are already available to be fetched. To query the linked server, click New Query in the toolbar:
Enter your SQL query in the editor window and click Execute to run the query:
As a result, you can see the contents of the selected table retrieved directly from the Dynamics account you are connected to.
If the Linked Server was created with the Allow inprocess option enabled, then you should delete this Linked Server and create it again with the Allow inprocess option disabled.