torsdag 16 mars 2017

Install Reporting Services on another machine than the SQL Server

We have faced the question of using an SQL hotel for CRM purposes a couple of times. One problem you could be facing is that the SQL server is hosting several different CRM environments so you needed to install the CRM on a named instance, which of course is possible even with a single installation. Since the SQL server might be hosting several CRMs you needed to install the SQL Server Reporting Services on a different machine.

The Reporting Services doesn't need to be installed on the same machine that is hosting the SQL server and this is a feature that you might be interested in when you are hosting several CRM deployments on the same SQL server. It is possible to host different CRM servers on a single SQL server, although on separate instances of the SQL server, and you can also have separate instances of Reporting Services on that SQL.

The issue is that for the CRM installation you have a component called the Reporting Services Connector that you need to install on the server running the Reporting Services of the CRM system. This software can only handle one CRM server and can only be installed once on a server which means that you can only host one CRM server with a Reporting Services installation so even though you can host multiple CRM servers on one SQL server, you still need one Reporting Services installation per CRM server.
You can install the Reporting Services server on one of the servers used for CRM if you need to minimize the number of servers involved, if you do this you need to think about how your reporting is used because if you do a lot of reporting that server might be loaded and might make the system less responsive for the users.

One thing that's good to know is is that you can't run the reporting sevices service with an account that is used for the CRM installation, i.e. the same account as you're running Asynchronous service with. It is a good thing to have separate accounts for every part of the CRM system, but in this case you are not allowed to use the same account because things won't work.

To install the Reporting Services server on a different machine could also be interesting if you're doing a lot of reporting and you find that the SQL server isn't coping with the load, or you'd like to split them because you'd like to have a lot of power on your SQL server for different reasons but you also want that power to be available for database handling rather than reporting.

Rickard Norström
Developer at CRM-Konsulterna 
I’m currently building an integration plugin for a customer that uses an online ERP system that has a REST interface to communicate with the system. To use the REST endpoint in an easier way the company has provided me with a couple of connection DLLs.

This is the first time I’m using external DLLs in a plugin for an online deployment, I’ve previously only done that on premise, and it’s a couple of years ago, so this meant that I’m facing some new challenges.
The first issue is that I can’t just place stuff in the assembly folder and look happy, since it’s online everything needs to be located in the database, and well you can't just put some dlls there so ILMerge is needed to create one dll. At first I was a but curious how the sandbox in Dynamics 365 would react to the external calls that I was going to make but I really didn’t think it would be an issue, and that was a correct assumption, no problems making the calls from the sandbox, one issue out.

The next thing I thought was my salvation is that I’m able to do some testing on an on-premise Dynamics 365 and just setting the plugin to run in the sandbox. This was one issue I didn’t expect. When I ran my code in the sandbox on our server, it was working just fine but when I registered the assembly in Dynamics 365 online I got in trouble, and it’s not really amusing to troubleshoot plugins online. This one was even worse since it was five or so dlls that I had merged using ILMerge, which also was a source of concern. This proved not to be an issue, the dll that was the output from ILMerge was working as planned.

I got a bit of help by George Doubinski  and the issue was that the code I was using implicit typed variables. The reason for this is that I got code examples from the ERP provider. Don’t get me wrong here, I’m not blaming them for that, it just that I’m coming from hardware where everything is explicitly typed so my code is generally explicit typed even though the best practice for C# is to use implicit typed variables. This time however I let it be as the example code was written and since that worked in a sandboxed on-prem I didn’t really think about it.

I’m not sure what it is that makes the dynamic and var problematic here but it was very clear that in this case it did not work online.

The moral of this is that even though you have code that’s working in a sandboxed on-premise installation it might not work in an online deployment. If that happens it might be worth it to start looking at what variable types you’re using. To merge dlls together with ILmerge is working very good though, which is good.
Rickard Norström
Developer at CRM-Konsulterna 

torsdag 2 februari 2017

Reporting Services, reports, reporting extension Visual Studio and headache

I was involved in a discussion about report creation in the Microsoft Dynamics CRM group on Facebook the other day where started digging in how to use Visual Studio 2015 for reporting since Jonas Rapp asked about that. He thought it was silly that his devolpment team had to have both VS 2015 and 2012 to be able to work, 2015 for general coding and 2012 for reports.

Now, I have always been using the Data Tools, which has been knows as BIDS (Business Intelligence Development Studio) when I've been doing the reports since most of my report building has been on premise and I need the database to be able to work anyway so I've been doing the job on the SQL server where the BIDS has been installed, or at least after I request it to be installed if it's not our environment.
Since I've been fiddling a bit with SSIS I know it's possible to download the Data Tools for VS 2015, that's the same tool you're using for SSIS and reports so I had a quick look at the Data Tools download which was all fine and dandy. It worked very good to open a reporting solution and move stuff around. All good.

Now, what I didn't think about was that you might like to do online reports which require the Reporting Extensions. As I said earlier, I haven't done much reporting for online since the customers we've been working with that wants reports have been on premise. Anyway, reporting extension it is and that's available here for Dynamics 365 and on that very page the pre reqs state that it should work with VS 2013 and 2015 however the comments at the bottom pretty clearly says that it doesn't work.

Next thing. As I had installed the Data Tools for VS 2015 I started using them, big mistake. I altered a report for a CRM 2016 update 1 (might be called spring release, I don't know, but it is version 8.1 on premise) and uploaded it to the CRM server. That did not end well and there was a ton of errors saying very odd things which I just didn't care about at the moment, might come back to that later. Thank god for version control, go back to previous version, make the same changes in the SQL Server Data Tool (some VS 2013 shell methink) and upload it again, this time with better results.

The moral is the following, don't use bleeding edge Visual Studio for reporting, unfortunately, specially since it should be working according to the MS pages.

Hope someone gets less headache from this.

Rickard Norström
Developer at CRM-Konsulterna 

tisdag 24 januari 2017

Dynamics 365 On Premise (also known as CRM 8.2) licensing

I just had a bit of an eye opener regarding the winter update for CRM 2016 which made it  Dynamics 365. This is a new license model and a new license which is needed to run the server and for CALs for all the users.

That may not be that much of an issue if it wasn't for the upgrade engine. Previously when the new major versions were released there was a new server installation, or an in-place upgrade using some sort of install media. This is not true for the winter update, so if you haven't got it already be careful.
By, "haven't got it already" I mean that since it is distributed as an update, previously known as an update rollup, you will get it installed automatically if you have the "install updates automatically" setting set and then you will have a software which you may not be licensed with.

This is rather late for this but say you have a server which is more or less connected to the internet you may not already have got the update, or if you are manually installing upgrades using Windows Update, untick the CRM upgrade if you don't have a license.

The licensing guide can be found here and the KB for Dynamics 365 update is found here

Rickard Norström
Developer at CRM-Konsulterna 

söndag 27 november 2016

Data Encryption Keys during upgrade from CRM 2013

I've been doing some upgrades from CRM 2013 the past couple of month and one thing I've run into is the encryption keys.

The funny thing is that an encryption key that is valid in CRM 2013 isn't valid in CRM 2015 and later. It seems that Microsoft has changed the requirement of the encryption key sometime after the lauch of CRM 2013. So what are the requirements of the encryption key. I started looking for it and it isn't all that clear, however  this blog: say the following:  "The value you enter should be between 10 and 100 characters in length, have at least one numeral, at least one letter and one symbol or special character."

That sounds like a sound idea and the question is if the "at least one symbol or special character" is what have changed. When I use an encryption key which follows this rule set it works and the key that was used on the CRM 2013 which didn't work in later versions didn't have a special character, it did however contain an upper case letter, a lower case letter, a digit and was more than 10 characters long. 
After upgrade you need to re-enter the encryption key from the previous version again and if that key doesn't match you can't save and if the key doesn't match the above criteria you can't save either so you need to go back to the CRM 2013, change the key to something that will be accepted in the more modern version and then to the entire upgrade process again.

Hope this helps someone.

Rickard Norström
Developer at CRM-Konsulterna 

SQL Error when imorting an organization

I had a very weird moment the other day when I was trying to rename an organization in CRM. All of a sudden I got an error saying SQL server is unavailable.

The funny thing is that I opened the deployment manager and disabled and deleted the organization without any issues whatsoever. When I was going to re-import it it all seemed smooth as always, I got to choose the database server and which organization I wanted to import, then I got to name it, which was what I wanted to change, and the reporting server URL plus user mapping passed after that.

Here's where things went south. The always dreaded checks and this time it broke down on me on the SQL server check saying that the SQL server was unavailable, no path could be found.

I obviously found this very annoying and also sort of strange since I set up the server a couple of days ago and all of a sudden it couldn't talk to the SQL, which it obviously had been talking to a couple of seconds earlier since I'm HOPING that the deployment manager doesn't just guess about things like orgnaization names and stuff like that.

On the SQL server there were no error messages so it didn't look like an SQL error, all the services was up and running, but I restarted a couple of them just to be sure. When I tried to import the organization again I ran a "netstat -n" on the CRM server and it tried to do something over port 445, which is part of the ports that are needed for AD authentication according to the IG, it is sort of weird that it got blocked or if it has always been blocked weitd that it has been working.

The solution was to open port 445 on the SQL server and then the checks went through fine and I could import the organization with a new name.

Rickard Norström
Developer at CRM-Konsulterna 

onsdag 23 november 2016

Using Powershell to set up a new organisation

Being the primary techie goblin in our company it often is my job to set up new organisations for development or testing purposes so I thought it might be a neat thing to do this by powershell, which it really should be...

It would actually seem that Microsoft agrees with me since it exists a cmdlet in Microsoft.Crm.Powershell that's called New-Crm-Organization, which you can  read about here.
The issue I was faced with is that it seems that the cmdlet in powershell is run by the service account of the deployment web service.
On the first server I tried this, which is a very test-no-production-server that account was Network Service, and the new org-cmdlet failed with a whole bunch of errors looking like this:

Error Items:
    ActiveDirectoryRightsCheck raising error : The current user does not have required permissions (read/write) for the following Active Directory group: CN=ReportingGro
up {a189b908-ee74-4532-b70d-373aea8fb39f},OU=CRM2016DEV,OU=CRMS,DC=effa,DC=local
    SysAdminCheck raising error : You do not have sufficient permission to perform this operation on the specified organization database
    ExistingRSCheck raising error : Setup failed to validate specified Reporting Services Report Server http://crm2016dev/reportserver. Error: Error occurred while findi
ng an item on the report server.
System.Web.Services.Protocols.SoapException: The permissions granted to user 'NT AUTHORITY\NETWORK SERVICE' are insufficient for performing this operation. ---> Microsof
t.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The permissions granted to user 'NT AUTHORITY\NETWORK SERVICE' are insufficient for performing this oper

This made me a bit sad since I wanted to use Powershell, the strange thing is that it works to create organisations with the deployment management tool, it didn't matter if I used the -Credential-flag either, it still used Network Service.
Oh well, I thought, maybe I'm having better luck on another server where I have an AD account set up as the deployment web service account, but I ran into the same issue there.
I've found sites on the web where they say that you can give the appopriate rights in the OU, which probably means that you need to give access to the reporting services too and so on and so on. I will be continuing to search for solutions of this issue since it would be oh so neat to handle this kind of operations with Powershell. If anyone has a good solution, give me a holler.

Rickard Norström
Developer at CRM-Konsulterna