Please wait, loading...

 

Connecting SSMS to use SQL Query in Dynamics 365 CRM Online – Login failed: TDS protocol endpoint is disabled for this organization

May 22, 2020

You can also use SQL Server Management Studio (SSMS) version 18.4 or later with the Common Data Service endpoint SQL connection to connect with Microsoft dynamics 365 CRM online. Examples of using SSMS with the SQL data connection are provided below.

Supported operations and data types when querying data from Dynamics 365 CRM online or CDS

The list of supported SQL operations includes:

  • Batch operations
  • SELECT
  • Aggregation functions (i.e., Count() and Max() functions)
  • UNIONs and JOINs
  • Filtering

STEP 1: : https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/cds-sql-queryThe following document by Microsoft and entering Credentials

https://i0.wp.com/microsoftdynamics.in/wp-content/uploads/2020/05/img_5ec6cd66f006e.png?fit=711%2C406&ssl=1

But Not all org Connect successfully, we need to attend some prerequisites. i.e Download the OrgDBOrgSettingsTool and configure Microsoft.Crm.SE.OrgDBOrgSettingsTool.exe.config file.

Step 2Getting Error when clicking connect Error :

——————————
ADDITIONAL INFORMATION:

Login failed: TDS protocol endpoint is disabled for this organization. For more information, please visit http://aka.ms/EnableSQLForCDS
RequestId: TDS;6d3b31e8-d260-410c-9a23-cca78cbd1795;2
Time: 2020-05-21T07:17:56.0358439Z (Microsoft SQL Server, Error: 18456)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

STEP 3:Configuring Microsoft.Crm.SE.OrgDBOrgSettingsTool.exe.config

Follow instruction from here: https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/view-entity-data-power-bi

  1. Sign into Power Apps, and then select the appropriate environment from the top-right corner.
  2. Verify that your enviroment has version 9.1.0.17437. To do this, select the Settings gear on the toolbar, and then select Advanced Settings. Then, on the new browser tab that opens select the Settings gear on the toolbar, and then select About.
  3. Download the OrgDBOrgSettingsTool. During the download, select CRM2016-Tools-KB4046795-ENU-amd64.exe. After the download is completed, install the tool.
  4. Open the Microsoft.Crm.SE.OrgDBOrgSettingsTool.exe.config file and make the following changes. For example, if your advanced settings environment URL is https://orgbb33fb45f.crm3.dynamics.com/main.aspx the OrgDBOrgSettingsTool_CrmDiscoveryService_CrmDiscoveryService value will be https://disco.crm3.dynamics.com/XRMServices/2011/Discovery.svc and the OrgDBOrgSettingsTool_OrgServiceUri value will be https://orgbb33fb45f.crm3.dynamics.com/XrmServices/2011/Organization.svc.
https://i0.wp.com/microsoftdynamics.in/wp-content/uploads/2020/05/img_5ec6cd66f006e.png?fit=711%2C406&ssl=1

5. Add the following node within the <configuration> node in the Microsoft.Crm.SE.OrgDBOrgSettingsTool.exe.config file:

  <runtime>
      <AppContextSwitchOverrides value="Switch.System.Net.DontEnableSchUseStrongCrypto=false"/>
  </runtime>

6. Open a command window, move to the folder where the OrgDBOrgSettingsTool contents are located, and run the following command:

    • Microsoft.Crm.SE.OrgDBOrgSettingsTool Update /u <org-unique-name> EnableTDSEndpoint true
    • Replacing <org-unique-name> with Org unique name

STEP 4: ONCE ABOVE CONFIGURATION IS DONE, ITS A SUCCESSFUL CONNECTION.

https://i0.wp.com/microsoftdynamics.in/wp-content/uploads/2020/05/img_5ec6d00536c39.png?fit=1315%2C735&ssl=1
https://i0.wp.com/microsoftdynamics.in/wp-content/uploads/2020/04/Microsoftdynamics365.png?fit=640%2C651&ssl=1
Microsoft Dynamics Community Profile

Learn more