Advertisements

Archive

Posts Tagged ‘On-premise’

[Step by Step] Canvas App using on-premise SQL server as Data Source

In this post, lets see how to build a Canvas App with data from on-premise SQL Server.

Using Gateways ,data can be transferred quickly and securely between a canvas app and an on-premises data source, such as an on-premises SQL Server database or an on-premises SharePoint site.

Lets get started to configure a Gateway and read the on-premises SQL Data which acts as a Canvas App’s ‘Data Source’.

Prerequisites:

  • SQL Server with a Database and a simple table.

Gateway_13

  • Dynamics 365 subscription. Subscribe to 30 days trail here.

Configure a ‘Gateway’ to on-premise SQL Server:

We need to install ‘On-Premises Data Gateway’ service on the machine with ‘SQL Server’.

  • Connect to PowerApps maker portal using your Dynamics 365 credentials.
  • Go to Data -> Gateways and click on ‘+New gateway’ button.

Gateway_15.PNG

  • You will be redirected to downloads page and click on ‘Download’ under ‘On-Premises Data Gateway’ section.

Gateway_1

  • Download and click on the ‘GatewayInstall’ application.

Gateway_2

  • Provide your Dynamics 365 email (i.e., User ID) and click ‘Sign in’ to connect to Power platform.

Gateway_3

  • Provide your desired gateway name,recovery key and click ‘Configure’

Gateway_4

  • You will end up with below success screen.

Gateway_5

  • We are done with Gateway configuration.
  • If you go to the ‘Services’ on your machine, you should see ‘On-premises data gateway service’ up and running.

Gateway_14

  • Go back to PowerApps portal and refresh the ‘Gateways’ and you should see the configure gateway.

Gateway_6

Create a Canvas App:

As we completed ‘Gateway’ configuration, the next step to create a Canvas app and connect to on-premises SQL server using the ‘Gateway’.

  • Click on ‘+Create’ and choose ‘Canvas app from blank’

Gateway_7

  • Provide the ‘App name’ and click on ‘Create’

Gateway_8

  • Now we need to establish the connectivity between Canvas App and SQL Server.
  • Click on ‘Connect to data’ and select ‘SQL Server’ from the list.

Gateway_9

  • Choose ‘Connect using on-premises data gateway’ option and provide the SQL server, Gateway details and click ‘Create’.

Gateway_10

  • Select the table (i.e.,Contact) and click ‘Connect’ which creates a new ‘Data source’.

Gateway_11

  • Now add a Gallery component and map the ‘Data source’.

Gateway_12

Additional Details:

  • To install the ‘Gateway’, machine should have minimum configuration mentioned here.
  • Gateway installs as ‘On-premises data gateway service’ on the server. Make sure this service is running all the time.
  • Along with service, a desktop application by name ‘On-premises data gateway’ gets installed. You can enable logging and manage settings using this application.

Gateway_16

🙂

Advertisements

(401) Unauthorized Error – CRM integration with SharePoint

December 12, 2017 1 comment

We encountered below exception when we were integrating CRM with SharePoint.

Exception: System.Net.WebException: The remote server returned an error: (401) Unauthorized.

Environment details:

  • CRM 2016 on-premise with Claims authentication enabled.
  • SharePoint Server On-premise with Claims authentication enabled.
  • User Account using to set up is Admin on both the CRM and Sharepoint servers.

Reason & Fix:

Fix suggested in this MSDN Forum worked for us.

It seems there is a known issue/bug with CRM 2016 that doesn’t allow this to work if you try using https in the MetadataEndPoint.

To get this working, you will have to temporarily do the following items before running the SharePoint Power Shell command.

Note : Make sure you note all the existing settings before executing below steps which helps you to roll back, if something goes wrong.

Steps:

  1. Open the CRM Deployment Manager tool
    • Disable CRM IFD
    • Disable CRM Claims Configuration
  2. Open the IIS and remove the https binding on the CRM Web site and add back the http binding for the CRM Web site.
  3. Run CRM Deployment Manager and change the properties of the deployment for the Web Address to use http instead of https.
  4. Run an IISReset on the CRM Server to ensure this is now accessible via http.
  5. Run the SharePoint PowerShell commands (you should be able to access the MetadataEndpoint using http vs. https now if you put it in the browser (and it should prompt to download a .json file).
  6. Once the SharePoint commands are finished running, you need to reverse the changes above in CRM to re-enable IFD.
  7. Change the CRM Web Address to use https in the CRM Deployment Properties.
  8. Remove the http binding on the CRM Web site and add back the https binding, selecting the correct SSL Certificate.
  9. Run the Configure CRM Claims in the CRM Deployment Manager.  (keep the existing settings)
  10. Run the Configure IFD in the CRM Deployment Manager (keep existing settings).
  11. Run an IISReset on the CRM Server.

Refer MSDN article  which details integrating SharePoint on-premise with CRM on-premise.

🙂