Accessing SQL Server or External Data from Dynamics 365 via Virtual Entity

Microsoft introduced a new feature called Virtual Entity which will help you access data residing in an external system via OData 4.0 API. You can build your own OData API for your database, plug it in to Dynamics 365 to access your data which would take significant development, maintenance and testing efforts. With Progress Hybrid Data Pipeline, you get an out of the box solution which will help you create an OData 4.0 endpoint, which you can configure as Virtual entity and access your data. In this tutorial, we will walk you through how you can use the virtual entity feature by showing how you can access SQL Server data using OData API generated by Progress Hybrid Data Pipeline.

Microsoft introduced a new feature called Virtual Entity which will help you access data residing in an external system via OData 4.0 API. You can build your own OData API for your database, plug it in to Dynamics 365 to access your data which would take significant development, maintenance and testing efforts. With Progress Hybrid Data Pipeline, you get an out of the box solution which will help you create an OData 4.0 endpoint, which you can configure as Virtual entity and access your data. In this tutorial, we will walk you through how you can use the virtual entity feature by showing how you can access SQL Server data using OData API generated by Progress Hybrid Data Pipeline.

Follow the below tutorials on how to install Progress Hybrid Data Pipeline in Azure or AWS

For AWS

https://www.progress.com/tutorials/cloud-and-hybrid/deploying-progress-datadirect-hybrid-data-pipeline-on-amazon-aws

For Azure

https://www.progress.com/tutorials/cloud-and-hybrid/deploying-hybrid-data-pipeline-on-microsoft-azure

Important: Make sure you redirect traffic on 80 ports on your machine to port 8080 of HDP server using a proxy or firewall rules. This is important, as Dynamics CRM will not work if your endpoint URL has a port in it.

Enabling On-Premise Connectivity (if applicable)

If your database is behind a corporate firewall or in a private network, you can use our On-Premises connector which will enable a secure connection to your database for Hybrid Data Pipeline Server, without having to open any ports in your firewall. To get started, you would need to install this On-Premises Connector on your on-premise infrastructure as the name suggests. Follow the below tutorial on how to install the On-Premises Connector.

If Hybrid Data Pipeline Server is on AWS

https://www.progress.com/tutorials/cloud-and-hybrid/deploying-progress-datadirect-hybrid-data-pipeline-on-amazon-aws

If Hybrid Data Pipeline Server is on Azure

https://www.progress.com/tutorials/odbc/configuring-on-premise-connector-for-hybrid-data-pipeline-on-azure

Follow the below tutorials on how to install Progress Hybrid Data Pipeline in Azure or AWS

For AWS

https://www.progress.com/tutorials/cloud-and-hybrid/deploying-progress-datadirect-hybrid-data-pipeline-on-amazon-aws

For Azure

https://www.progress.com/tutorials/cloud-and-hybrid/deploying-hybrid-data-pipeline-on-microsoft-azure

Important: Make sure you redirect traffic on 80 ports on your machine to port 8080 of HDP server using a proxy or firewall rules. This is important, as Dynamics CRM will not work if your endpoint URL has a port in it.

Enabling On-Premise Connectivity (if applicable)

If your database is behind a corporate firewall or in a private network, you can use our On-Premises connector which will enable a secure connection to your database for Hybrid Data Pipeline Server, without having to open any ports in your firewall. To get started, you would need to install this On-Premises Connector on your on-premise infrastructure as the name suggests. Follow the below tutorial on how to install the On-Premises Connector.

If Hybrid Data Pipeline Server is on AWS

https://www.progress.com/tutorials/cloud-and-hybrid/deploying-progress-datadirect-hybrid-data-pipeline-on-amazon-aws

If Hybrid Data Pipeline Server is on Azure

https://www.progress.com/tutorials/odbc/configuring-on-premise-connector-for-hybrid-data-pipeline-on-azure

1. Open your browser and go to go to http://server_ip_address and you should see a login page as shown below. You should be able to access the UI without any port in the URL after you have redirected traffic on 80 to 8080.




2. Login with the credentials that you have supplied during installation and you should be seeing the dashboard. Click on Data Sources tab on the side bar and you should see all the data stores that are supported.




3. Click on SQL Server or your own database and you should now see a connection configuration page as shown below. Fill it up with your connection information for your database and click on Test Connect button to verify the connection.
   
   
   Note: If you are connecting to a database On-Premise, you should find the On-Premise Connector ID, which you have configured above, in Connector ID drop down.
4. Now go to OData tab and Click on Configure Schema button. You should now see all the schemas in your database, select your schema and now you should see all the tables in the schema as shown below. Select the tables that you want to access through




5. Click on Save and Close to save the configuration. Now go to OData tab and you should find the endpoint URL as shown below. If you don’t have the SSL enabled, fallback to http.
   
   

   The URL might be of following format : https://server_name:8443/api/odata4/SQLServer/ , but you should be able to access it via https://server_name/api/odata4/SQLServer (if SSL enabled) or http://server_name/api/odata4/SQLServer as you have redirected traffic.

1. Open your browser and go to go to http://server_ip_address and you should see a login page as shown below. You should be able to access the UI without any port in the URL after you have redirected traffic on 80 to 8080.




2. Login with the credentials that you have supplied during installation and you should be seeing the dashboard. Click on Data Sources tab on the side bar and you should see all the data stores that are supported.




3. Click on SQL Server or your own database and you should now see a connection configuration page as shown below. Fill it up with your connection information for your database and click on Test Connect button to verify the connection.
   
   
   Note: If you are connecting to a database On-Premise, you should find the On-Premise Connector ID, which you have configured above, in Connector ID drop down.
4. Now go to OData tab and Click on Configure Schema button. You should now see all the schemas in your database, select your schema and now you should see all the tables in the schema as shown below. Select the tables that you want to access through




5. Click on Save and Close to save the configuration. Now go to OData tab and you should find the endpoint URL as shown below. If you don’t have the SSL enabled, fallback to http.
   
   

   The URL might be of following format : https://server_name:8443/api/odata4/SQLServer/ , but you should be able to access it via https://server_name/api/odata4/SQLServer (if SSL enabled) or http://server_name/api/odata4/SQLServer as you have redirected traffic.

1. In Dynamics CRM, Go to Settings -> Administration and Click on Virtual Entity Data Sources
2. Now create a New OData4 datasource, by clicking on New. Fill up the form as shown below. For Authentication, Hybrid Data Pipeline Server uses a basic auth, so you need to calculate the basic Auth String and set a header with ParameterName Authorization and value as Basic base64encoded(username:password)




3. Click on Save and Close.

1. In Dynamics CRM, Go to Settings -> Administration and Click on Virtual Entity Data Sources
2. Now create a New OData4 datasource, by clicking on New. Fill up the form as shown below. For Authentication, Hybrid Data Pipeline Server uses a basic auth, so you need to calculate the basic Auth String and set a header with ParameterName Authorization and value as Basic base64encoded(username:password)




3. Click on Save and Close.

1. Before going further down, let’s look at the entity that I am trying to access via Virtual Entity.


               <?xml version="1.0" encoding="UTF-8"?>
<edmx:Edmx xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx" Version="4.0">
<edmx:DataServices>
<Schema xmlns="http://docs.oasis-open.org/odata/ns/edm" Namespace="SQLServer">
<EntityType Name="Customer">
<Key>
<PropertyRef Name="CustomerId"/>
</Key>
<Property Name="CustomerId" Nullable="false" Type="Edm.Guid"/>
<Property Name="FirstName" Nullable="false" Type="Edm.String" MaxLength="40"/>
<Property Name="LastName" Nullable="false" Type="Edm.String" MaxLength="20"/>
<Property Name="Company" Type="Edm.String" MaxLength="80"/>
<Property Name="Address" Type="Edm.String" MaxLength="70"/>
<Property Name="City" Type="Edm.String" MaxLength="40"/>
<Property Name="State" Type="Edm.String" MaxLength="40"/>
<Property Name="Country" Type="Edm.String" MaxLength="40"/>
<Property Name="PostalCode" Type="Edm.String" MaxLength="10"/>
<Property Name="Phone" Type="Edm.String" MaxLength="24"/>
<Property Name="Fax" Type="Edm.String" MaxLength="24"/>
<Property Name="Email" Nullable="false" Type="Edm.String" MaxLength="60"/>
</EntityType>
<EntityContainer Name="dbo">
<EntitySet Name="Customers" EntityType="SQLServer.Customer"/>
</EntityContainer>
</Schema>
</edmx:DataServices>
</edmx:Edmx>



2. As you can see, I have an entity named Customer with a primary key CustomerId of data type Edm.Guid. Your entity’s data type should be Edm.Guid, else you can’t access your data via Dynamics CRM.
3. Some Important things you need to keep in mind when configuring Fields in Entity:
   • The Column Names are Case Sensitive, you need to set it to same name in the External Name of the field as in your OData 4.0 metadata.
   • If the Column has Nullable=false, you need to set Field Requirement to Business Required.
   • If the Column has Nullable=true, you need to set Field Requirement to Optional.
   • If your column has MaxLength property in OData 4.0 metadata, then set Maximum length to same value in your field configuration.
4. Now Go to -> Settings -> Customizations -> Customize the System and Create a New Entity. Configure it as shown below.
   • Display Name: Customer
   • Plural Name: Customers
   • External Name: Customer (should be same as the EntityType Name="Customer” in your OData 4.0 config)
   • External Collection Name: Customers (should be same as the EntitySet Name="Customer” in your OData 4.0 config)
   • Check the Virtual Entity and now you should see new field called Data Source, and in the drop down you should find the Virtual Entity data source you have configured above.
   • Click Save





5. Once you have saved entity, it’s time to create Fields. Click on Fields tab on the side bar. You should see two default columns already there.





6. Click on column new_customerid, which is a primary key and you should see a Field Configuration as shown below. Set External Name: CustomerId, it should be same as the Primary key column name in your OData 4.0 metadata. Note, the column name is case sensitive.





7. Click on column new_name and configure it as shown below.
   • External Name: FirstName (same as my OData 4 metadata)
   • Field Requirement: Business Required (as my metadata has Nullable=false)
   • Maximum Length: 40 (As FirstName in my Metadata has a maximum length 40)





8. Similarly, you need to create all the fields in your metadata, here are my configurations for the remaining fields.








































9. After saving all the fields, Publish all Customizations
10. Go to Views, All Customers - Add Columns and add all columns to the view, and Save the view.
11. Go to Advanced View and run a query on Customers and you should see your data as below.

We hope this tutorial helped you in accessing your external data in SQL Server or on-premises database from Dynamics CRM. Feel free to try Progress Hybrid Data Pipeline and let us know if you have questions or issues.

1. Before going further down, let’s look at the entity that I am trying to access via Virtual Entity.


               <?xml version="1.0" encoding="UTF-8"?>
<edmx:Edmx xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx" Version="4.0">
<edmx:DataServices>
<Schema xmlns="http://docs.oasis-open.org/odata/ns/edm" Namespace="SQLServer">
<EntityType Name="Customer">
<Key>
<PropertyRef Name="CustomerId"/>
</Key>
<Property Name="CustomerId" Nullable="false" Type="Edm.Guid"/>
<Property Name="FirstName" Nullable="false" Type="Edm.String" MaxLength="40"/>
<Property Name="LastName" Nullable="false" Type="Edm.String" MaxLength="20"/>
<Property Name="Company" Type="Edm.String" MaxLength="80"/>
<Property Name="Address" Type="Edm.String" MaxLength="70"/>
<Property Name="City" Type="Edm.String" MaxLength="40"/>
<Property Name="State" Type="Edm.String" MaxLength="40"/>
<Property Name="Country" Type="Edm.String" MaxLength="40"/>
<Property Name="PostalCode" Type="Edm.String" MaxLength="10"/>
<Property Name="Phone" Type="Edm.String" MaxLength="24"/>
<Property Name="Fax" Type="Edm.String" MaxLength="24"/>
<Property Name="Email" Nullable="false" Type="Edm.String" MaxLength="60"/>
</EntityType>
<EntityContainer Name="dbo">
<EntitySet Name="Customers" EntityType="SQLServer.Customer"/>
</EntityContainer>
</Schema>
</edmx:DataServices>
</edmx:Edmx>



2. As you can see, I have an entity named Customer with a primary key CustomerId of data type Edm.Guid. Your entity’s data type should be Edm.Guid, else you can’t access your data via Dynamics CRM.
3. Some Important things you need to keep in mind when configuring Fields in Entity:
   • The Column Names are Case Sensitive, you need to set it to same name in the External Name of the field as in your OData 4.0 metadata.
   • If the Column has Nullable=false, you need to set Field Requirement to Business Required.
   • If the Column has Nullable=true, you need to set Field Requirement to Optional.
   • If your column has MaxLength property in OData 4.0 metadata, then set Maximum length to same value in your field configuration.
4. Now Go to -> Settings -> Customizations -> Customize the System and Create a New Entity. Configure it as shown below.
   • Display Name: Customer
   • Plural Name: Customers
   • External Name: Customer (should be same as the EntityType Name="Customer” in your OData 4.0 config)
   • External Collection Name: Customers (should be same as the EntitySet Name="Customer” in your OData 4.0 config)
   • Check the Virtual Entity and now you should see new field called Data Source, and in the drop down you should find the Virtual Entity data source you have configured above.
   • Click Save





5. Once you have saved entity, it’s time to create Fields. Click on Fields tab on the side bar. You should see two default columns already there.





6. Click on column new_customerid, which is a primary key and you should see a Field Configuration as shown below. Set External Name: CustomerId, it should be same as the Primary key column name in your OData 4.0 metadata. Note, the column name is case sensitive.





7. Click on column new_name and configure it as shown below.
   • External Name: FirstName (same as my OData 4 metadata)
   • Field Requirement: Business Required (as my metadata has Nullable=false)
   • Maximum Length: 40 (As FirstName in my Metadata has a maximum length 40)





8. Similarly, you need to create all the fields in your metadata, here are my configurations for the remaining fields.








































9. After saving all the fields, Publish all Customizations
10. Go to Views, All Customers - Add Columns and add all columns to the view, and Save the view.
11. Go to Advanced View and run a query on Customers and you should see your data as below.

We hope this tutorial helped you in accessing your external data in SQL Server or on-premises database from Dynamics CRM. Feel free to try Progress Hybrid Data Pipeline and let us know if you have questions or issues.