Tip #343: In Dynamics CRM we trust

Having customer’s commitment issues? Something like this?

Oh, I don’t trust corporations, they are out there to get you and your money. Remember Lehman Brothers?

To start with, unlike subprime mortgages, Microsoft Dynamics CRM is a real product. What’s important though, that Microsoft, unlike payday lenders, recognizes concerns customers and prospects alike might have about entrusting their valuable business data to a cloud provider, Microsoft or not.

Enter Microsoft Dynamics CRM Trust Center. Abridged summary follows, to save you a visit until you have stocked up on your coffee supplies and have a spare afternoon to sieve through the abbreviations and trying to understand how PII is different from SSN.

Privacy

No ads, no mixing the data with other customers, data portability – unless there is a court injunction stopping you from doing so, you can “whack” your data at any time (by proxy, of course, no one will let you anywhere near the datacenter, and that’s a good thing).

Transparency

You know where the datacenters are. You know who has access to your data and under what circumstances.

Security obsession

Decades of experience, five layers of the security, proactive monitoring, restricted server access. ‘Nuff said.

Standards

First major cloud provider to support ISO/IEC 27018. Certified for ISO 27001, support for EU Model Clauses, HIPAA, SSAE annual attestation, FedRAMP, FISMA, U.S.–EU Safe Harbor, CJIS. Enough abbreviations?

What’s equally if not more important, is that Microsoft is absolutely open when CRM is no-worky. Take for example Regulatory Compliance FAQ

Office 365 and Microsoft Dynamics CRM Online do not support the processing, transmitting, or storing of PCI-governed data, such as credit card numbers.

In other words, any solution vendor claiming safe storage of credit card data inside Dynamics CRM, was designing subprime mortgages in their previous corporate lives.

I don’t know about you but I feel safe for my and my customers’ data.

Bonus points to Microsoft for making Australian site prettier than US one and for correctly spelling Centre.

Tip #342: Demystifying pop3/smtp for CRM Online

Having troubles creating server-side synchronization between CRM Online and pop3/smtp system? You are in the right place then.

You may have read in the documentation that GMail and Yahoo are listed as supported pop3/smtp providers for Microsoft Dynamics CRM Online and

Although other POP3/SMTP systems may work with Microsoft Dynamics CRM, those systems were not been tested by Microsoft and are not supported.

Let’s be clear about “not supported“. In this context it means precisely “you will not be able to go past server profile screen as we will reject any pop3/smtp provider that is not GMail or Yahoo.”

Yahoo

Easy one first. Use the following settings:

  • Incoming Server Location: pop.mail.yahoo.com
  • Outgoing Server Location: smtp.mail.yahoo.com
  • Important! In Advanced section change outgoing port to 587

Given the right credentials, you should be all set.

GMail

That was epic. Google tightened up the security belt on gmail to the extent that yours eyes will pop out first before pop3 will start working. Abridged summary (all items are equally important):

  • Incoming Server Location: pop.gmail.com
  • Outgoing Server Location: smtp.gmail.com
  • In Advanced section change outgoing port to 587
  • In the same section set Minimum Polling Interval in Minutes to 15 or more
  • Google, most likely, will reject CRM advances to login. Why? Because. Go to https://security.google.com/settings/security/activity and if you see this:
    Access to gmail from unknown location
    indicate that device is yours.
  • Follow Google advice to allow less secure apps to access your account. Apparently, CRM Online servers are one of those.
  • Read and weep, and then follow the instructions. All of them
  • And the last bit I’d like to reiterate separately because it killed couple hours of my time. Make sure your password strong. I had mine set to all lowercase + some numbers and it just was not enough. Sad part is that neither Google nor CRM Online give you any information to assist with troubleshooting

Now that you have it all working, remember what Google said in one of the documents:

Make sure your mail app isn’t set to check for new email too frequently. If your mail app checks for new messages more than once every 10 minutes, the app’s access to your account could be blocked.

That’s why 15 minutes or more. I hope you saved more by reading this.

Tip #341: Lean and mean UAT

One of the most wildly accepted ways to refresh your test database is to copy/redeploy production database back into the test environment. (If you don’t have a test database, go and receive your Spießrutenlaufen, then come back and create the database; if you can still move, that is).

Common issue with this approach, however, is the size of the production database. Blind copy will take unnecessary (and expensive in case of CRM Online) space that is simply wasted. Unless there is a specific requirement to test some obscure functionality, there is no reason to cling to those email attachments and notes files.

Find the offending entities

The following SQL script will display database tables in the descending order by space occupied.

declare  @tmpTableSizes as table 
(
    tableName varchar(100),
    numberofRows varchar(100),
    reservedSize varchar(50),
    dataSize varchar(50),
    indexSize varchar(50),
    unusedSize varchar(50)
)
insert @tmpTableSizes
EXEC sp_MSforeachtable 
   @command1="EXEC sp_spaceused '?'"

select  * from @tmpTableSizes order by 
   cast(LEFT(reservedSize, LEN(reservedSize) - 4) 
      as int) desc

It’ll produce something like this:
SQL tables & space occupied

Delete unwanted records

Now that you identified the offending entities, take approach documented in the administrator’s guide and use it as a baseline. For example, you’d want to expand definitions of the emails with attachments (method 4) to cover not only Completed status reason but also Sent and Received. And don’t hesitate to specify different time conditions or maximum attachment size to reduce database size even further.

This approach will work equally well on-premises as well as in CRM Online. To get the space occupied by your data per table/entity basis, simply raise a support request – and you’ll have this information for your CRM organization in no time.

Tip #340: Sky is the limit for the workflows

From time to time I come across posts referring to CRM Online limits and citing 200 (active) workflows as a limit to be aware of.

Let me use 1000-word equivalent:
Unlimited workflows in CRM Online
What you see here is the user interface lagging behind the product capabilities. As far as I know, limit for the number of custom entities still stands however it can be increased by placing a support request (and having a good reason handy).

Tip #339: N:N relationships and Advanced Find

So you create a many-to-many relationship and add data to it, then you want to run an Advanced Find filtered on that relationship. But when you run the advanced find, you don’t get any results. What’s up?

The reason you don’t get anything back is because you have the relationship set to not display in the navigation area. Update your relationship to display on both sides of the relationship, and you will be able to filter advanced find off of the relationship and return results.

Tip #338: Rename that organization

So, you followed our earlier tip and ended up with
https://ihateyoumarcus.crm.dynamics.com.

Two days later, he finally agreed that, yes dear, we do need monogrammed napkins for each one of our 276 guests, but on the condition that this CRM name goes away.

CRM Online

  • Login into https://portal.office.com
  • Click Admin > CRM
  • If your company is rich enough, select organization, otherwise it’s preselected. Click Edit
  • Change URL to your liking. Find that someone has taken “grumpycat”, opt for “cardpinis2590”

CRM On-premises

On premises you can change the name using CRM Deployment Manager:

  • Disable organization
  • Edit? No, editing will only allow you to change the display name
  • Delete organization (Yes, delete. Chicken?)
  • Import organization back
  • At that point you’ll be given an opportunity to change the name
  • Consult Banned Words They Don’t Want You To Use. Based on your chequered history, stay clear of “marcus” anywhere in the organization name
  • Inhale/exhale – that’s how long it usually takes

Partner-hosted CRM

Call your partner’s support line and ask them to change it for you. After all, it’s them who provisioned this name in the first place.

Now, what did he say about the plastic glasses being just fine?

Tip #337: Using stored procedures with CRM reports

One trick veteran report writers like to use when writing SSRS reports for Dynamics CRM is to use a stored procedure for the report query. There are several appealing reasons to do this:

  • Performance: Stored procedures are cached, which can give them a performance advantage when compared to a query in the report. This advantage is diminished in modern versions of SQL, but I have seen complex, long running reports where the query timed out when embedded in the report, but completed successfully when using a stored procedure.
  • Separation of code: by separating the query in a stored procedure, it can sometimes be easier to troubleshoot/manage the query when it is separated from the RDL. We’ve all seen weird behavior when editing an existing CRM report–you upload it and the old version is cached, so you have to remove and republish the entire report. This can get very complicated if you have multiple subreports. By separating the query into a stored procedure, I can update the query and filters without having to republish the report.
  • Reuse of code: Sometimes you need to use the same query for multiple reports. A stored procedure lets me have a single copy of the query used by multiple reports.

There are also some potential downsides to using stored procedures for CRM reports:

  • It requires the report writer to have direct access to the CRM SQL database. When using embedded queries in reports, the report writer can author reports from SQL data tools without having direct access to the SQL database. Creating and editing of stored procedures requires a higher level of SQL database privileges than embedded queries.
  • It is borderline unsupported. This falls into the same gray area that creating custom indexes does. It is not documented in the implementation guide or the SDK, so it is not officially supported. It also requires elevation of the default database permissions. However, it can be a safe thing to do, as long as your stored procedures work in a supported way: Don’t write directly to the database, don’t query base tables, use filtered views. If you do this, you can remain close to supported with minimal risk.

Enabling stored procedures for reports

So you weigh the pros and cons and decide that you are comfortable with using stored procedures in CRM reports. How do you make it work?

To enable stored procedures for reports, you need to understand how reporting in CRM works. This applies to CRM 2011-2015. When I run a report in CRM, the report is not actually being run by me. Via the SRS data connector, CRM runs the report under the context of the service account that SSRS is running as on the CRM SSRS server.

  1. Log on to the SSRS server used by your CRM deployment. Pull up services and find the SQL Server Reporting Services windows service. Go to the properties of the service and look at the “Log on” tab. Look for the service account that the SSRS service runs as.
  2. Log on to the SQL server used by your CRM deployment. Go to SQL Management Studio. Add the service account found in step 1 to SQL as a user.
  3. Grant the service account “execute” permission to each stored procedure used by your SSRS report.

Tip #336: Choose your name wisely

A common mistake when setting up your on-premises CRM organization is choosing the wrong name. Name not display name.
Organization name vs display name
Frequently when seeing the field to enter the organization name, the novice CRM implementer will think they should enter the name of their company/organization. This is generally a bad idea.

  • The organization name will form part of your URL if you use IFD. This will result in some very long/hard to remember URL’s. https://acmeindustriesofnorthamericalimited.acme.com anyone?
  • The URL may also become redundant. Acme.acme.com is bad, and may be already reserved for other purposes.

So what should you use? Something easy to remember and descriptive of the purpose of the organization. Typically something with “CRM” in it, or whatever you refer to CRM internally as. I like to include “dev,” “test,” “UAT,” etc in non production organizations, then use a combination of the company abbreviation and CRM for production.

Here are some suggested organization names (case is irrelevant, of course, but may help readability especially if you’re trying to convey a secret message):

  • Awesomecrm
  • Doubinski (See if anyone can spell it correctly)
  • Salesforcestinks
  • acme-1969 (numbers and hyphens are allowed)
  • 1 (or just numbers, including those that can be mistaken for ‘l’)
  • 1l1l1O0l1lOO001l1 (with the names like this who needs security)
  • j––ll–––––––m (It’s amazing to see what a user’s mind can substitute mentally for a hyphen)
  • TheWorkingVersionofCRM
  • OurCompanysHighlyConfidentialInformation
  • EdwardSnowdenUsedtoWorkHere
  • Password
  • KatieFromReceptionCallMeOn800-555-0199

Tip #335: CRM upgrades and dead reports

Evan “Doc” Watson sends a tip to remember to clear out old/unused reports prior to upgrading to CRM 2013/2015. The dependency check in the upgrade process will kill your upgrade if any entities referenced in a report no longer exist in the environment.

If you have any reports that reference an entity that has been removed, you will see an error in the upgrade log that looks something like this:


Exception occured during Microsoft.Crm.Tools.Admin.OrganizationUpgrader: Error.ActionFailed Microsoft.Crm.Tools.Admin.PopulateDependenciesAction

InnerException: Microsoft.Crm.Metadata.EntityMetadataNotFoundException: The entity with a name = 'new_shipmentsummary' was not found in the MetadataCache.

Should this happen to you, you will need to delete the report (if you can), or if it won’t let you delete the report, recreate the missing entity.

Tip #334: Organization refresh and upgrade date

So your CRM Online test environment is scheduled to be upgraded to the Fall 2014 (CRM 2015) release next week. To get ready for it, you log in to the Office 365 console and refresh the organization to ensure that a current copy of your production environment gets upgraded to get a realistic test of your production upgrade. Great! Smart move.

One thing to look out for is that if you have already confirmed your upgrade date for this environment, refreshing the organization will cause your upgrade date to get changed to the next default date (which may be 3 months from now).

After the organization refresh is complete, check the scheduled upgrade date. if it is moved, you can move it back.