Using SQL Management Studio to connect to the Dynamics DB

   Words by CRM Consultancy

   on 18/05/2021 11:34:00

sqlserver

This is a feature that Microsoft began rolling out last year and is extremely useful for understanding Dynamics and Database Structures – but the challenge with features in the Cloud is keeping up with new functionality or options that are sent to us ‘down the pipe,' so it is good to get this knowledge shared more widely.

To that end, this article will demonstrate how to connect SQL Management Studio to a Dynamics or Power Apps cloud instance.

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

2. Open SQL Management Studio and select our way of connecting to the Cloud, in this case: Azure Active Directory - Password

3. Then supply the URL of Dynamics (minus the https://), our Username and our Password:

clip_image002[4]

4. All being well, this will then open a view of the Dynamics Database in the Cloud in a familiar SQL format.

A few words of warning..

Its a lot!  The SQL view is great but you will see *all* 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.

Its Read-Only!  Any data we want to Create or Update must be done through the Dynamics API and so this SQL view is Read-Only.

Security – if you have sufficient rights to view the SQL view, you have ‘grandmaster’ rights and so avoid the usual security restrictions on the data.

clip_image002[6]



5. The massive advantage this gives us is the ability to run various SQL SELECT and JOIN statements past the core Dynamics Database.

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

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.

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:

Capture1



7. Will return:

clip_image002[9]



To give us the history of Product Family Changes since the beginning of today.

This is an Entity that we’ve 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.



Further Reading:

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

Share this Article

Search Articles

Filter Articles

CRM Tech DocMan

Recent Articles

HOW TO: Manage Your Dynamics 365 Database Size (Video Included) Dynamics 365 Marketing vs ClickDimensions It’s time to pause, reflect and acknowledge a new era of inclusivity and collaboration. Part 2 - How to get the most from a Technology Expert – Asset Management Hub Property & Asset Management Hub Part 1 – Balancing CRM and Asset Management Scopes - Asset Management Hub Creating a Multi-Lingual PowerApps Portal How to Set Up a Microsoft Teams Site Using DocDrive365 Microsoft Teams - Adding a Microsoft Teams URL to a Dynamics Appointment Dynamics 365 Marketing – Customer Voice Survey Not Appearing In Emails? Using SQL Management Studio to connect to the Dynamics DB Calling a Power Platform AI Builder Model via oData How to use DocDrive365 to integrate permissions between Business Units in Dynamics with Sites in SharePoint Getting started with the Power Platform AI Builder. Power Apps Portal Information Hub DocDrive365 Security: Day One - Getting Started with Dynamics to SharePoint Permissions Part 5 - Power Apps Portals: How To Connect Azure B2C With Linked-In Part 4 – Power Apps Portals: Styling Azure B2C for Power Apps Portals The 3 Phases for Using Multi-Select Option Sets in Flow with Microsoft Forms Part 3 – PowerApps Portals: Azure B2C and Power Apps Portals – User Flow for Signup and Signin Part 2 - Power Apps Portals: New Application Registration in Azure B2C for our Power Apps Portal Part 1 – Power Apps Portals: Creating a New Azure AD B2C Tenant The Automation Bot: Launching Contextual Flow from Teams Creating a New Bot for Teams Debugging your Teams Bot using Ngrok
Contact Us

Want expert advice or a demo?

Get in touch now and see how we can help your business grow.

  • Name
  • Email Address
  • Phone Number
 
Close

Understanding Your Challenges

Our strong understanding of CRM and emerging technologies within the Microsoft environment means we deliver the right solutions for you.

Proven Real-World Solutions

As a leader in the field of Dynamics solutions, our pedigree developing and delivering real-world solutions is unsurpassed.

Long Term Support

We provide support beyond our design, implementation and 'go-live' delivery using Sprints and continual updates to our AppSource apps.

CRMCS | Design by Thinktank Marketing | Citrus-Lime Limited

To improve your experience today and in the future, this site uses cookies. Read our full Privacy Policy & Cookie information here I Understand