- After the driver is installed, it can be configured. For this, in the Control Panel open Administrative Tools and find the Data Sources (ODBC)<\/strong> tool:
![\"Data_Sources_tool\"](\"https:\/\/www.devart.com\/odbc\/dynamics\/docs\/data_sources_tool.png\")
<\/li>- Now you have to add the ODBC Driver for Dynamics CRM to the list of System Data Sources<\/strong> or User Data Sources<\/strong> or File Data Sources<\/strong> using the ODBC Data Source Administrator<\/strong> dialog.
![\"added_DSN\"](\"https:\/\/www.devart.com\/odbc\/dynamics\/docs\/added_dsn.png\")
<\/li>- For this, click the Add<\/strong> button – the Create New Data Source<\/strong> dialog will appear with a list of available drivers.Select ODBC Driver for Dynamics CRM and click Finish.
![\"create_DSN\"](\"https:\/\/www.devart.com\/odbc\/dynamics\/docs\/create_dynamics_dsn.png\")
Then you will be suggested to configure the driver.<\/li>- To activate the driver via ODBC Administrator, refer to the Product Activation<\/a> article for more details.<\/li>
- In the appeared connection dialog, you can set the connection options.
![\"connection_settings_dn\"](\"https:\/\/www.devart.com\/odbc\/dynamics\/docs\/connection_settings_dn.png\")
<\/li>- Then you can click OK and finish configuration or switch to the Advanced settings <\/strong>tab to specify more detailed Connection String<\/li><\/ol>\n\n\n\n
Connecting to Dynamics from SQL Server Management Studio using ODBC Driver for Dynamics<\/h3>\n\n\n\n
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.<\/p>\n\n\n\n
Below are major advantages of using SQL Server Linked Servers to connect to Dynamics:<\/p>\n\n\n\n
- The ability to connect other database instances on the same or remote server.<\/li>
- The ability to run distributed queries on heterogeneous data sources across the organization.<\/li>
- The ability to work with diverse data sources in the same way.<\/li><\/ol>\n\n\n\n
How to configure a SQL Server Linked Server to connect to Dynamics<\/h3>\n\n\n\n
You can follow the steps to create a linked server for Dynamics in SQL Server Management Studio by using Object Explorer:<\/p>\n\n\n\n
- Start your Management Studio and choose your SQL Server instance.<\/li>
- In the Object Explorer pane<\/strong>, expand the Server Objects<\/strong>, right-click on Linked Servers<\/strong> and then click on New Linked Server<\/strong>.<\/li>
- Configure your linked server in the dialog box:
- Give a name for your server in the Linked server<\/strong> field.<\/li>
- Under Server type<\/strong>, select Other data source<\/strong> .<\/li>
- Choose Microsoft OLE DB Provider for ODBC Drivers<\/strong> in the Provider<\/strong> drop-down list.<\/li>
- In the Data source<\/strong> field, enter the name of your DSN, e.g. Devart ODBC Driver for Dynamics . Alternatively, you can input the ODBC Driver connection string in the Provider<\/strong> field.<\/li><\/ul><\/li><\/ol>\n\n\n\n
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.<\/p>\n\n\n\n
Retrieving Data From Dynamics<\/h3>\n\n\n\n
Disable the Allow inprocess option<\/strong> of MSDASQL OLE DB Provider for ODBC Drivers. For this, find the MSDASQL<\/strong> provider in the list of Linked Servers and double-click on it<\/p>\n\n\n\n
![\"msdasql\"\/](\"https:\/\/www.devart.com\/odbc\/dynamics\/docs\/msdasql.png\")
<\/figure>\n\n\n\nIn the appeared Provider Options<\/strong> window, clear the Allow inprocess<\/strong> checkbox:<\/p>\n\n\n\n
![\"allowinprocess\"\/](\"https:\/\/www.devart.com\/odbc\/dynamics\/docs\/allowinprocess.png\")
<\/figure>\n\n\n\nCreate a new Linked Server<\/strong><\/p>\n\n\n\n
![\"newlinkedserver\"\/](\"https:\/\/www.devart.com\/odbc\/dynamics\/docs\/newlinkedserver.png\")
<\/figure>\n\n\n\nMake sure to select Microsoft OLE DB Provider for ODBC Drivers<\/strong> and specify the following parameters:<\/p>\n\n\n\n
![\"provideroptions\"\/](\"https:\/\/www.devart.com\/odbc\/dynamics\/docs\/provideroptions.png\")
<\/figure>\n\n\n\nThe Dynamics tables are already available to be fetched. To query the linked server, click New Query<\/strong> in the toolbar:<\/p>\n\n\n\n
![\"newquery\"\/](\"https:\/\/www.devart.com\/odbc\/dynamics\/docs\/newquery.png\")
<\/figure>\n\n\n\nEnter your SQL query in the editor window and click Execute<\/strong> to run the query:<\/p>\n\n\n\n
![\"accounttable\"\/](\"https:\/\/www.devart.com\/odbc\/dynamics\/docs\/SSMS-SQL.png\")
<\/figure>\n\n\n\nAs a result, you can see the contents of the selected table retrieved directly from the Dynamics account you are connected to.<\/p>\n\n\n\n
Attention<\/h3>\n\n\n\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
Requirements In order to avoid incorrect integration with MS SSMS, the working environment must meet the following conditions: an 3rd party ODBC Driver, like Devart ODBC for Dynamic 365, click below link for more info. https:\/\/www.devart.com\/odbc\/dynamics\/ The driver, studio, and SQL Server must be of the same bitness. For example, if you are using 64-bit Read More<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"footnotes":""},"categories":[627,497],"tags":[643],"class_list":["post-3131","post","type-post","status-publish","format-standard","hentry","category-bi-and-reporting","category-solutions","tag-connecting-sql-server-to-microsoft-dynamics"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\t
- Under Server type<\/strong>, select Other data source<\/strong> .<\/li>
- Configure your linked server in the dialog box:
- Now you have to add the ODBC Driver for Dynamics CRM to the list of System Data Sources<\/strong> or User Data Sources<\/strong> or File Data Sources<\/strong> using the ODBC Data Source Administrator<\/strong> dialog.