{"id":195,"date":"2022-01-06T11:54:19","date_gmt":"2022-01-06T11:54:19","guid":{"rendered":"https:\/\/blog.citrus-lime.com\/crmc\/?p=195"},"modified":"2022-01-06T11:54:19","modified_gmt":"2022-01-06T11:54:19","slug":"using-sql-management-studio-to-connect-to-the-dynamics-db","status":"publish","type":"post","link":"https:\/\/blog.citrus-lime.com\/crmc\/using-sql-management-studio-to-connect-to-the-dynamics-db\/","title":{"rendered":"Using SQL Management Studio to connect to the Dynamics DB"},"content":{"rendered":"\n
\"sqlserver\"<\/figure><\/div>\n\n\n\n

This is a feature that Microsoft began rolling out last year and is extremely useful for understanding Dynamics and Database Structures \u2013 but the challenge with features in the Cloud is keeping up with new functionality or options that are sent to us \u2018down the pipe,’ so it is good to get this knowledge shared more widely.<\/p>\n\n\n\n

To that end, this article will demonstrate how to connect SQL Management Studio to a Dynamics or Power Apps cloud instance.<\/strong><\/p>\n\n\n\n

1. First of all, we need SQL Management Studio and we need it to be v18.4<\/strong> \u2013 we can download this here: https:\/\/docs.microsoft.com\/en-us\/sql\/ssms\/download-sql-server-management-studio-ssms?view=sql-server-ver15<\/a><\/p>\n\n\n\n

2. Open SQL Management Studio and select our way of connecting to the Cloud, in this case: Azure Active Directory – Password<\/strong><\/p>\n\n\n\n

3. Then supply the URL of Dynamics (minus the https:\/\/<\/a>), our Username and our Password:<\/p>\n\n\n\n

\"clip_image002[4]\"<\/figure>\n\n\n\n

4. All being well, this will then open a view of the Dynamics Database in the Cloud in a familiar SQL format.<\/p>\n\n\n\n

A few words of warning..<\/strong><\/p>\n\n\n\n

Its a lot!  <\/strong>The SQL view is great but you will see *all<\/strong>* the Tables behind Dynamics which can be overwhelming!  Its best to dip in and get used to the structure in small doses, similar to using the Advanced Find.<\/p>\n\n\n\n

Its Read-Only!  <\/strong>Any data we want to Create or Update must be done through the Dynamics API and so this SQL view is Read-Only.<\/p>\n\n\n\n

Security<\/strong> \u2013 if you have sufficient rights to view the SQL view, you have \u2018grandmaster\u2019 rights and so avoid the usual security restrictions on the data.
<\/p>\n\n\n\n

\"clip_image002[6]\"<\/figure>\n\n\n\n



5. The massive advantage this gives us is the ability to run various SQL SELECT and JOIN statements past the core Dynamics Database.<\/p>\n\n\n\n

Typically Dynamics and the Power Platform use their own Query language called FetchXml to run Select style statements \u2013 and we can see this in action in the CRM Advanced Find, and download our own FetchXml queries.<\/p>\n\n\n\n

But FetchXml is nowhere near as well known as SQL, and so the ability to run commands on the Dynamics Database using standard SQL Notation is often a hugely useful feature.<\/p>\n\n\n\n

6. For our client, this allowed us to dip into the different Database Tables and interrogate the Data or the Database Names of the Fields involved:<\/p>\n\n\n\n

\"Capture1\"<\/figure>\n\n\n\n


7. Will return:<\/p>\n\n\n\n

\"clip_image002[9]\"<\/figure>\n\n\n\n

To give us the history of Product Family Changes since the beginning of today.<\/p>\n\n\n\n

This is an Entity that we\u2019ve added into our clients CRM and gives a good peek behind the scenes for both System (built into Dynamics) or Custom (added by Apps or Service Providers) Tables added to the system.
<\/p>\n\n\n\n

\n\n\n\n

Further Reading:<\/strong><\/p>\n\n\n\n

The following is the Microsoft Guide on the SQL Connector for Dynamics \/ Dataverse: https:\/\/docs.microsoft.com\/en-us\/powerapps\/developer\/data-platform\/dataverse-sql-query<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

This is a feature that Microsoft began rolling out last year and is extremely useful for understanding Dynamics and Database Structures \u2013 but the challenge with features in the Cloud is keeping up with new functionality or options that are sent to us \u2018down the pipe,’ so it is good to get this knowledge shared<\/p>\n","protected":false},"author":43,"featured_media":35,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_price":"","_stock":"","_tribe_ticket_header":"","_tribe_default_ticket_provider":"","_tribe_ticket_capacity":"0","_ticket_start_date":"","_ticket_end_date":"","_tribe_ticket_show_description":"","_tribe_ticket_show_not_going":false,"_tribe_ticket_use_global_stock":"","_tribe_ticket_global_stock_level":"","_global_stock_mode":"","_global_stock_cap":"","_tribe_rsvp_for_event":"","_tribe_ticket_going_count":"","_tribe_ticket_not_going_count":"","_tribe_tickets_list":"[]","_tribe_ticket_has_attendee_info_fields":false,"footnotes":""},"categories":[3],"tags":[],"class_list":{"0":"post-195","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-crm"},"featured_image_src":"https:\/\/blog.citrus-lime.com\/crmc\/wp-content\/uploads\/sites\/30\/2021\/11\/Dynamics-365-Consultancy.jpg","author_info":{"display_name":"jadesmith","author_link":"https:\/\/blog.citrus-lime.com\/crmc\/author\/jadesmith\/"},"_links":{"self":[{"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/posts\/195","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/users\/43"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/comments?post=195"}],"version-history":[{"count":1,"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/posts\/195\/revisions"}],"predecessor-version":[{"id":196,"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/posts\/195\/revisions\/196"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/media\/35"}],"wp:attachment":[{"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/media?parent=195"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/categories?post=195"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/tags?post=195"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}