Setting up Data Export Service without PowerShell Script

Setting up Data Export Service without PowerShell Script

Setting up Dynamics 365 Data Export Service requires a Azure KeyVault to be set up which is typically done using a PowerShell script which can be found in the Data Export Service setup wizard. However, if you run into issues setting this up, it might be easier to do this directly in Azure by minimizing the steps of the scripts. This was a tip that my friend and Business Solution MVP Scott Durow recommended. He mentions this in his very instructive video, but doesn’t actually show how, so I thought I’d just detail how I made it work.

First some background. The reason why I even started investigating how to do this manually was that when I tried running the PowerShell script supplied by Microsoft in the wizard.

Press the “i” icon to get a window containing the PowerShell Script that Microsoft recommends for setting up the Key Vault.

When running the PowerShell script both as myself (not a global admin) and asking a global admin to do it, it failed in the latter parts. The key vault was created by some of the access policies seemed to be missing and it just didn’t work. My users rights in Azure was Contributor in the Resource Group, and it was a bit interesting cause the global admin and I got different error messages, but when I finally managed to create the key vault manually, I could do it all with my user, so it didn’t seem I was missing any rights to do it.

First step is to make sure you have all your data straight. The power shell script is good for this. Check out Scott clip if you want to know how to find the different strings. He shows it very clearly.

 Just copied from the PS-Script:

$subscriptionId = '<subscription ID>'
$keyvaultName = '
MyVault'
$secretName =
'MySecretName'
$location = '
North Europe'
$connectionString = 'Server=tcp:
<db-name>,
1433;
Initial Catalog=<catalog>;
Persist Security Info=False;
User ID={your_username};
Password={your_password};
MultipleActiveResultSets=False;
Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;'
$organizationIdList = '<DYN365GUID>

$tenantId = ‘<AZURE TENANT ID> 

The highlighted parts have to be replaced by your settings. I will use these variables to have something to reference to further in this article.

Search for Key Vault and add the “Key vault”, the top one in this picture

Then we have to set it up. Not so tricky if you have worked with Azure before. Consider if you want to work in an existing Resourcegroup or if you want to create a new one. Typically you need to have Azure SQL services running as well so it might be good to keep them all together to be able to see the costs and control who has access why a resource group might be a good idea. But that should hence already exist. If not, you can create it. I would recommend keeping Azure SQL and Key vault in the same, not sure if it actually works in different resource groups, probably does, but I haven’t tested.

Creating the key vault – in this case I am creating a new resource group, normally it would already exist

Azure will add you as the default principal with access to the key vault. We will add Data Export Service to this later. For now, just create it.

Now we need to open the Key vault and select the “Secrets” section in the menu on the left hand side and press the button:

“+ Generate/Import” 

Then you have to enter you Secret name ($secretName) and the connection string ($connectionString) into the value.

Creating a secret – $secretname in Name and $connectionstring in Value

Press “Create”.

You should now return to the previous screen and see a row for your secret.
Select it.

It should open the settings panel for the Secret, press the “Tags” part which is located in the middle and add a tag which has $OrgIdList ($organizationIdList) as the key and Tennant ($tenantId) as value. I have blurred them out below as they are rather private.

Adding a tag with OrgIdList and tenantId to a Secret

You then need to go back to the Key Vault and click on the “Access Policies” menu item, you should then see yourself as the principal as this was set when we created the key vault. We now need to add Data Export Service as a valid Principal with read access rights.

So click “Add”, click “Select Principal” and search for “b861dbcc-a7ef-4219-a005-0e4de4ea7dcf” which is the ID for Data Export Service. It should show up like this:

It needs to have “Secret Management Operations – GET” permissions and nothing else.

Now, go back to the Secret and copy the URI to the Secret.

Getting the URI for the Key Vault Secret

Paste it into the Data Export Service Wizard field for Key Vault.

Fill in the other information and press validate. Hopefully it will work out well!

Some issues

Being too cheap with the Azure SQL level
If you don’t go for a Azure SQL P1 and choose a lower tier, you might get this warning:

We tried an S0 for our Dev environment and tried to sync a couple of million records and that just didn’t work, we got tons of errors. We upgraded the ASQL to S2 and then at least we didn’t get any errors. We are planning for P1s in UAT and production.

Might have to set activation date on secret
Seems that you might have to set an activation date on the secret. Not sure why this is, the PS-script doesn’t seem to do this. But not very hard.

Added activation date on the Secret from June 4.th

Using Database schema that is not created
The default database schema is “dbo” in the Data Export Service Wizard. If you change this to something else like “crm” and you haven’t created this in the database, you will get an error. It is simple to fix, you just have to go into the database and create the schema. To create the schema “crm” open a query and run:
CREATE SCHEMA crm

For more information on how to create schemas, check this site: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-schema-transact-sql?view=sql-server-2017

Once the schema has been created, there should be no problem using it, as long as the user has permissions using it.

I hope this works for you. If you have any questions, don’t hesitate to leave a comment.

Gustaf Westerlund
MVP, Founder and Principal Consultant at CRM-konsulterna AB
www.crmkonsulterna.se

Troublesome report troubleshooting

Troublesome report troubleshooting

Creating reports in SSRS and uploading to CRM can be a pain sometimes. The report works just fine in SSRS (well, the CRM-Autofilters don’t of course, but still), but when trying to upload it you get this fine and informative error message:

Not very helpful, so maybe the log file is:
Unhandled Exception: System.ServiceModel.FaultException`1[[Microsoft.Xrm.Sdk.OrganizationServiceFault, Microsoft.Xrm.Sdk, Version=6.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35]]: An error occurred while trying to add the report to Microsoft Dynamics CRM. Try adding the report again. If this problem persists, contact your system administrator.Detail: 
<OrganizationServiceFault xmlns:i=”http://www.w3.org/2001/XMLSchema-instance” xmlns=”http://schemas.microsoft.com/xrm/2011/Contracts”>
  <ErrorCode>-2147188072</ErrorCode>
  <ErrorDetails xmlns:d2p1=”http://schemas.datacontract.org/2004/07/System.Collections.Generic” />
  <Message>An error occurred while trying to add the report to Microsoft Dynamics CRM. Try adding the report again. If this problem persists, contact your system administrator.</Message>
  <Timestamp>2015-06-29T14:16:35.6330269Z</Timestamp>
  <InnerFault i:nil=”true” />
  <TraceText i:nil=”true” />
</OrganizationServiceFault>

If you are not very used to reading this, the important part is:
An error occurred while trying to add the report to Microsoft Dynamics CRM. Try adding the report again. If this problem persists, contact your system administrator.

Not really sure who that guy is. Anybody know him or her please leave a comment.

Ok. So, maybe the SSRS tracelogs have something. Let.s go to there, in my case it was in this path:
C:Program FilesMicrosoft SQL ServerMSRS11.MSSQLSERVERReporting ServicesLogFiles

Yours is probably something similar.

The last entries in the log were:
library!ReportServer_0-2!1870!06/29/2015-16:28:36:: i INFO: Call to GetItemTypeAction(/Contoso_MSCRM/CustomReports/{f27fb971-691e-e511-93fe-00155d01ac02}).
library!ReportServer_0-2!1870!06/29/2015-16:28:36:: i INFO: Call to CreateReportAction({f27fb971-691e-e511-93fe-00155d01ac02}, /Contoso_MSCRM/CustomReports, False).
processing!ReportServer_0-2!1870!06/29/2015-16:28:36:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportPublishingException: , Microsoft.ReportingServices.ReportProcessing.ReportPublishingException: Exception of type ‘Microsoft.ReportingServices.ReportProcessing.ReportPublishingException’ was thrown.;
library!ReportServer_0-2!1870!06/29/2015-16:28:36:: i INFO: Call to CreateReportAction({f27fb971-691e-e511-93fe-00155d01ac02}, /Contoso_MSCRM/CustomReports, False).
processing!ReportServer_0-2!1870!06/29/2015-16:28:36:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportPublishingException: , Microsoft.ReportingServices.ReportProcessing.ReportPublishingException: Exception of type ‘Microsoft.ReportingServices.ReportProcessing.ReportPublishingException’ was thrown.;
library!ReportServer_0-2!1870!06/29/2015-16:28:36:: i INFO: Call to CreateReportAction({f27fb971-691e-e511-93fe-00155d01ac02}, /Contoso_MSCRM/CustomReports, False).
processing!ReportServer_0-2!1870!06/29/2015-16:28:37:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportPublishingException: , Microsoft.ReportingServices.ReportProcessing.ReportPublishingException: Exception of type ‘Microsoft.ReportingServices.ReportProcessing.ReportPublishingException’ was thrown.;

Again we are all filled with joy and happiness as it is all too clear what is wrong… Well, if you can see it, please tell me, because I can’t. The logg seems to be a dead end as well.

And I am all out of logs to look for, I don’t really think the IIS-log will help.

However, all hope is not lost and we must trust the force, and the force says that the problem is probably that we have some UI component that is weird or that there is some reference that is bad. So I went back to my report, and just for the hell of it, not that I use it that much anyway, pressed the “Preview” tab, and behold, I had replaced a datasets query with a new SQL statement and missed that a tablix had a reference to a field.

So, these are some ways to troubleshoot a report. Maybe I should have done it the other way round. 🙂

Gustaf Westerlund
MVP, Founder and CTO at CRM-konsulterna AB
www.crmkonsulterna.se

Collation and MVP Renewal

Collation and MVP Renewal

When working with CRM systems in non-English countries you often need to take certain aspects into consideration that might have very dire consequences if set incorrectly, one of these is the database collation.

The collation of the database is simplified how it orders characters. For those of you not used to working with multi-language installation with odd characters this might not be something you have considered. As I am Swedish let me give you an example. The Swedish alphabet looks as follows:

abcdefghijklmnopqrstuvwxyzåäö

However using the collation that is default when using English as language in an on premise installation, which is “Latin1_General_CI_AI” (CI= Case Insensitive, AI = Accent Insensitive) the Swedish alphabet would be sorted as follows:

aäåbcdefghijklmnoöpqrstuvwxyz

This is why when installing an organization that is to be used in Sweden, despite the fact that you might have chose to have English as base language, that you need to select the collation that sorts the Swedish characters correctly which for CRM is “Finnish_Swedish_CI_AS”.

On-premise setup of CRM – ability to select Finnish_Swedish_CI_AS collation
despite using English as base language

When setting up an organization in CRM online, you cannot chose the collation explicitly which is regrettable and I hope there will be some advanced setting in the future which will allow for this when provisioning new organizations. Check out what happened when I provisioned a new organization in English indicating that I was in Sweden.

The first pick list is for country – I selected “Sweden”, the last is for language and I changed from “Swedish” to “English”, which were the two only options.

After going through all the three steps you are shown a last instance configuration step.

In the instance configuration screen you can select the base language but there is no selector for collation. I selected “Engelska”, which if you have any imagination, means “English”.

After, a few seconds, yes, the new installation process is great and really fast, my org is up and running and I jump in to contacts and create to contact and check out the sorting. Any bets?

As you can see selecting English language will also implicitly select Latin1_General _CI_AI as collation which is incorrect from a Swedish perspective, I would have liked to see Östen Svensson at the end of the list, not sorted as “O”. 

Collations are integral to the configuration of the SQL database and there are many features of the database like ORDER BY and indexes that depend on the collation why changing collation of a database is very very tricky. It is actually so tricky that it is not supported if you manage to do it with an on-premise, which I have heard rumours of that some people have managed to do. On an online you cannot request it. You will be recommended to migrate the data from the old org to the new.

So let’s hope Microsoft will enhance the provisioning experience for CRM Online so that it includes a selector for collation. In the meantime, if you need a special setup, like the one I tried to set up above, English with Finnish_Swedish_CI_AS collation on CRM online I would suggest contacting Microsoft Online support to get their assistance in setting up the instance correctly. Hopefully they can assist in this.

And on another note, I was awarded the MVP award for the third year! A great honour and this very article is dedicated to the very award as two of my fellow MVP:s Shan McArthur and Niel Benson provided with background information to this article. I extend to you a humble thanks!

Gustaf Westerlund
MVP, CEO and owner at CRM-konsulterna AB
www.crmkonsulterna.se

CRM 2011 Online and datawarehousing

I attended a half day presentation by Microsoft yesterday on the subject of Dynamics CRM 2011 and integrations. It seems that many parterners are a bit hesistant to integrating Dynamics CRM 2011 Online and are promoting the on-premise versions when the issues of integration are important.

Microsoft did a good job in showing that they do have sufficient technology to handle integrations to Dynamics CRM 2011 Online including the Azure based technology ACS – Access Control System that together with Active Directory Federation Services 2.0 can be used to allow Dynamics CRM 2011 Online to use a local AD as authenticator. It is probably not as easy as setting up a normal on-premise solution but at least it can be done and there hopefully are some whitepapers or Youtube-clips on how it is done.

One of the issues that were discussed was the issue of integrations in reagards to Business Intelligence and data warehousing. This is usually done using SQL Integration Service (SSIS) with direct SQL communication. Their suggestion on how to address this issue was to use the new OData interface that exists in Dynamics CRM 2011. I am no SQL expert but I do believe that this being a standardized protocol, integrations will be possible. However, there will still be issues with performance as the amounts of data that need to be transfered are quite large and the OData protocol is still a strict pull-protocol which does not allow for trigger-based updates.

Another issue that I asked about was the licensing issue in regards to Dynamics CRM 2011 Online. Take the following example: A large company of about 40 000 employees has about 500 people activly working in with Dynamics CRM 2011 Online for SalesForceAutomation. A data warehouse is created and data is integrated from many different systems, ERP, production systems, quality control systems and CRM. CRM being the customer data master. SharePoint is used as the global Intranet platform and some of the data from the data warehouse is published on the SharePoint portal to all employees. For instance our currently 10 most important customers. So, the 39 500 employees are only viewing a minute part of the customer data, and it indirecty, via the data warehouse, originates from the CRM system. What licenses in CRM are required for these users?

In the case of an on-premise installation, the best licensing option for Dynamics CRM is the Application Platform Agreement (APA) that is sort of a “free-for-all pass” which is negotiated with Microsoft. However, in the Online environment there is no correlating licensing agreement to the APA. I explicitly asked Micrsoft how this was to be licensed and their answer is that 40 000 separate users licenses are required for Dynamics CRM 2011 Online, making it a rather impossible option, in other words forcing the company to either an on-premise solution, removing some of the data from the data warehouse/Intranet or moving to another CRM supplier. Neither of which is in line with showing off the power of Microsofts cloud services.

I hope this is just a temporary flaw since it does limit Microsofts business opportunities with larger companies and I would think it is probable that some similar agreement form that matches the on-premise APA will be introduced.

On the other hand, the External Connector license is not required at all for CRM Online (or SPLA) making it even more interesting for smaller CRM customers as customer/event/portal integrations are more and more common.

Gustaf Westerlund
CEO, Chief Architect and co-Founder at CRM-konsulterna AB

www.crmkonsulterna.se

SQL Server 2008

Microsoft are planning to release a new version of SQL Server next year that will be called Microsoft SQL Server 2008.

It hosts lots of new features, for instance the ability to work directly with entities in the database instead of tables and views. An interesting feature, I wonder how good it will be.

SQL Reports will also be better and the integration with MS Office system (=SharePoint?) has be enhanced.

Have a look at Microsofts website for SQL Server 2008 for more information and some videos.

http://www.microsoft.com/sql/prodinfo/futureversion/default.mspx

Gustaf Westerlund
CRM and SharePoint Consultant

Humandata AB
www.humandata.se