Introduction: Why Connect Dataverse to SSMS?
Power Platform’s Dataverse enables organizations to store business-critical data securely and at scale. However, accessing that data with advanced queries can be challenging within built-in interfaces. By connecting Dataverse to SQL Server Management Studio (SSMS), users gain the ability to run powerful T-SQL queries, join tables, and perform deeper analytics using a familiar SQL environment—without cumbersome exports or third-party connectors. This integration streamlines reporting, auditing, and troubleshooting, making it invaluable for admins, developers, and analysts.
Prerequisites
Before you start, ensure you have:
- Dataverse Environment: Access to an environment (via Power Apps or Dynamics 365) with necessary permissions (Environment Admin or System Customizer).
- SQL Server Management Studio (SSMS): Version 18.4 or later installed.
- TDS Endpoint Enabled: The Tabular Data Stream (TDS) endpoint must be active in your Dataverse environment.
- Appropriate Security Role: “System Administrator” or equivalent role in Dataverse. Click here for more information on setting up security roles: Security concepts in Microsoft Dataverse – Power Platform | Microsoft Learn
- Environment URL: The Dataverse instance URL (e.g., yourorg.crm.dynamics.com).
- Azure AD Credentials: Your organizational account, preferably with single sign-on and multi-factor authentication.
Step-by-Step: How to Connect Using TDS Protocol
- Enable TDS Endpoint
- Log in to Power Platform Admin Center.
- Select your environment, go to Settings > Product > Features.
- Ensure TDS endpoint is enabled (toggle On if necessary).
- Install/Update SSMS
- Download SSMS 18.4 or later from Microsoft.
- Install and launch SSMS. Get the link here: Install SQL Server Management Studio | Microsoft Learn
- Obtain Connection Details
- Copy your environment’s URL (found in Power Platform Admin Center).
- Note: TDS endpoint uses port 1433 or 5558.
- Configure the Connection in SSMS
- Open SSMS, select Database Engine as server type.
- Enter: yourorg.crm.dynamics.com,1433 or 5558 in Server name.
- Choose Azure Active Directory – Universal with MFA support for authentication.
- Enter your Azure AD credentials and complete any authentication prompts.
- Click Connect to access Dataverse tables as read-only SQL tables.
- Explore and Query Data
- Expand your environment database in SSMS.
- Write T-SQL queries (e.g., SELECT TOP 100 * FROM account;).
- Note: The Dataverse TDS endpoint is read-only; data modification is not supported.
Troubleshooting Tips
- Authentication Issues: Use Azure AD credentials with the required Dataverse role. If login fails, try “Universal with MFA support.”
- TDS Endpoint Not Available: Double-check that the endpoint is enabled in admin center. Some organizations restrict TDS for security—consult your admin if needed.
- Port 1433 or 5558 Blocked: Ensure your network allows outbound traffic on port 1433 or 5558.
- Missing Tables: Only tables exposed via TDS appear in SSMS. Review your security permissions if expected tables are absent.
- Read-Only Limitation: The connection is for analytics; inserts, updates, or deletes are not supported.
Conclusion: The Benefits of Integration
By connecting Dataverse to SSMS, organizations unlock real-time, read-only data access using efficient SQL queries. This boosts productivity for reporting, analysis, and governance while reducing reliance on exports and external connectors. With the right setup and a few troubleshooting strategies, Dataverse-SSMS integration is a straightforward way to elevate your Power Platform experience.
