MS Dynamics CRM – Akif – akif.techie.ca

Find Reporting Services (SSRS) Reports using Dynamics CRM (DB)

Recently we migrated a CRM 4.0 implementation to Dynamics CRM Online. As part of the migration, we revamped all scripts, integration and reports connecting to CRM.

Once the system was live, it was discovered that the DBA has created some reports that were hosted in an SSRS server that was not connected to CRM. There was no CRM local or shared data-source.

DBA decided to use the data from CRM within joins of some of his reports. So he added CRM as a linked DB to his SQL server and then developed queries within the reports that uses data from CRM as well as from his SQL DBs. These reports were not available in any of the lower environments or in source code repository. The only way to identify the reports consuming data from CRM, as per him, was to export reports from SSRS and open their definitions one-by-one, which was cumbersome, for around 60 reports hosted on the SSRS server.

Fortunately, with the help of some forums, I was able to come up with the following query to list all SQL queries within all reports reading data from CRM DB. This query will list reports and the SQL statements within these reports consuming data from CRM DB:

Declare @Namespace NVARCHAR(500)

Declare @SQL   VARCHAR(max)

SELECT @Namespace= SUBSTRING(

x.CatContent

,x.CIndex

,CHARINDEX(‘”‘,x.CatContent,x.CIndex+7) – x.CIndex

)

FROM

(

SELECT CatContent = CONVERT(NVARCHAR(MAX),CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)))

,CIndex    = CHARINDEX(‘xmlns=”‘,CONVERT(NVARCHAR(MAX),CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content))))

FROM Reportserver.dbo.Catalog C

WHERE C.Content is not null

AND C.Type = 2

) X

SELECT @Namespace = REPLACE(@Namespace,’xmlns=”‘,”) + ” ;

SELECT @SQL = ‘WITH XMLNAMESPACES ( DEFAULT ”’ + @Namespace + ”’, ”http://schemas.microsoft.com/SQLServer/reporting/reportdesigner” AS rd )

SELECT ReportName        = name

,DataSetName        = x.value(”(@Name)[1]”, ”VARCHAR(250)”)

,DataSourceName    = x.value(”(Query/DataSourceName)[1]”,”VARCHAR(250)”)

,CommandText        = x.value(”(Query/CommandText)[1]”,”VARCHAR(500)”)

,Fields            = df.value(”(@Name)[1]”,”VARCHAR(250)”)

,DataField        = df.value(”(DataField)[1]”,”VARCHAR(250)”)

,DataType        = df.value(”(rd:TypeName)[1]”,”VARCHAR(250)”)

FROM ( SELECT C.Name,CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML

FROM ReportServer.dbo.Catalog C

WHERE C.Content is not null

AND C.Type = 2

) a

CROSS APPLY reportXML.nodes(”/Report/DataSets/DataSet”) r ( x )

CROSS APPLY x.nodes(”Fields/Field”) f(df)

Where x.value(”(Query/CommandText)[1]”,”VARCHAR(500)”) LIKE ”%_MSCRM%”

ORDER BY name ‘

EXEC(@SQL)

Dynamics CRM Online – Azure Service Bus – Bad Gateway Error

After a gap of 4 years, I have finally convinced myself to start blogging again. There is so much to blog. I will start with a simple problem that I faced last year while integrating Dynamics CRM Online with on-premises microservices.

If you are wondering, how a cloud based Dynamics CRM can send message to on-premises API, then have a look at this MSDN article.

Once the Azure service bus was configured, service bus listener was implemented and functional, custom workflow activity was registered within CRM Online, and service endpoint was configured, data started flowing to the back-end services. Great! At this time, our developer got stuck with a “50200: Bad Gateway” error within the custom workflow activity which was supposed to send data to on-premises service via Azure Service Bus.

When I started troubleshooting, I noticed that the data flow is fine for all except Account entity. Account entity, in our case, has a number of additional attributes. This entity was the largest in our case. All other custom and out-of-the-box entities were fine.

While googling I came to this article, which proposed to check system proxy that makes sense. But in my case, there was no system proxy on dev box, so it was not the proxy issue. I felt it has something to do with the message size as account entity has around 180 attributes with most of them filled.

Interestingly this guess proved to be correct despite a confusing and misleading error message. It turned out that the message size for the account entity from the CRM was bigger than the default WCF message size. Increasing the received message size for the Service Bus listener by adding maxReceivedMessageSize in the config file of WCF with a higher value than the default, fixed the issue:

<bindings>
  <ws2007HttpRelayBinding>
    <binding name="default" maxReceivedMessageSize="2147483647">
    </binding>
  </ws2007HttpRelayBinding>
</bindings>

Hoping this will save time for the others and will be helpful.

MS CRM 2011 Key Improvements

CRM, for me it is the most clean and flexible MS product. Yes including BizTalk and SharePoint. Why? because at least it fulfils its core functionality with minimum efforts and secondly you have to write least amount of code for any change and thirdly it has the most clean architecture as compared to other products. Just like SharePoint 2010, CRM 2011 have some breakthrough enhancements and new feature. For me, following are the most exciting ones:

Flexible Form Layout – We now have much more flexibility in how forms are laid out, for example, we can position sections side-by-side, as well as field labels on top, left or right of each field. Best of all, we can now configure “In-Line Sub-Grids” for child records, so a combination of IFrames & JScript is no longer required to make this work.
Filtered Lookups – One of the most requested features has finally made it into the product. Whilst customizing the form, you can choose a pre-defined view or better still you can filter by a related lookup on the same form.
Form Headers & Footers – Now that all tabs, sections and fields appear on a single, scrolling form, it is quite possible the form will get become quite long and you will end up scrolling up and down more often to find the information you require. In order to make the most commonly required visible at all times, you can now place these fields in a header or footer so that they will always be displayed regardless of the scrolling.
Solution Management and Sanboxing – Just like SharePoint now you have solution management and sanboxing in CRM as well, means you can create different customization-sets as solutions and then deploy them in CRM. Same customization-set Solution can then be ported to different environment for deployment. Sandboxing will help administrators in managing trust-level for customization and untrusted code can then be deployed in Sandbox role.
Plug-In Transaction Support – In CRM 4.0 you could register a plug-in to run either before (pre-event) or after (post-event) the CRM platform operation. However, you were not able to run as part of the transaction itself, so you had to right your own compensation logic in the event the CRM platform operation failed.
Workflow Dialogs – In CRM 5, they have taken the concept to workflow to a newer level and now we have 2 types of workflows which product team calls processes: background workflows which are called workflows, and user-experienced based workflows which are called dialogs. Basically, dialogs addresses a limitation of the workflow model in CRM 4, which is that a workflow can’t have a user experience component to it. Workflows are great and they can do a lot…but CRM 4 workflows cannot prompt a user for input. You cannot have workflow-task form there but now we have dialogs.
Role-Based Forms and Views, and Field Level Security – When setting up CRM, it seemed that we have always been asked, “can I set it up so that each person sees a different view of opportunities [or some other list type] when they open CRM?” Until CRM 2011, this used to require some custom development or add-ons. Now users can be presented not only with whatever view they choose as their favorite, but forms can also be customized by user role. This includes not only the fields on a form, but even the related lists in the left-hand part of the form. So, for example, if you have a campaign of type “event,” you might see related lists of: speakers, sponsors, venues. But a campaign of type “new store opening” might not display any of those lists. It seems small – but it goes a long way towards making the application easier to use for everyone. Also related to this is field level security – the ability to control visibility and read/write permissions on specific fields based on the role of a user.
SharePoint 2010 Integration with CRM 2011 – Unlike SharePoint 2007 and CRM 4, CRM 2011 has out-of-box integration with SharePoint 2010. It was strange and at the same time waste-of money to have WSS managing your documents separately than your CRM. You cannot take advantage of DMS capabilities of your SharePoint implementation without having complex integration but now this is available out-of-box.

Now CRM 2011 beta is available for evaluation at http://www.microsoft.com/downloads/en/details.aspx?FamilyID=0c7dcc45-9d41-4e2e-8126-895517b4274c&displayLang=en

List Web Part for Microsoft Dynamics CRM 4.0

The List Web Part for Microsoft Dynamics CRM 4.0 provides a way to view and update Microsoft Dynamics CRM records using a Windows SharePoint Services 3.0 SP1 or Microsoft Office SharePoint Server 2007 SP1 Web site. Microsoft Dynamics CRM users can create shared or personal List Web Parts of Microsoft Dynamics CRM records from a SharePoint Web site, open records in Microsoft Dynamics CRM 4.0 from the List Web Part, and create connected List Web Parts. For details, click here

How to find all shares in MS CRM 4

While implementing CRM at one of our clients, I was asked to provide them a report of all shares by one user. I was sure it must have to do something while displaying all items views in CRM. So I used SQL Profiler to find exact query used by CRM to fetch all items on which a user has access and from them extract the part which returns all shares for a user. This is what I got and it is really simple to find shares 🙂

select

POA.* from PrincipalObjectAccess POA join SystemUserPrincipals sup on POA.PrincipalId = sup.PrincipalId

where

sup.SystemUserId = ‘6130B308-2692-DE11-A9EF-000C29F4EE9D’ and POA.ObjectTypeCode = 4200 and

((

POA.AccessRightsMask|POA.InheritedAccessRightsMask) & 1) = 1

A handy and free of cost Call Center Solution by Microsoft using CRM and OCS

The Agent Communications Panel for Microsoft Dynamics CRM 4.0 is an application add-in to Microsoft Dynamics CRM 4.0. It works with Microsoft Dynamics CRM and Microsoft Office Communications Server 2007 R2, and enables agents to manage their communications (make calls, receive calls, conference, and chat) from their Microsoft Dynamics CRM system.

The Agent Communications Panel is an XAML browser application (XBAP). It is published to a Web server and opened from a Web browser.

It can be downloaded from Here

Reparenting Cascade behavior in MS CRM

Issue:

Lets say I have Campaign A created by user User1. Now when I login as CRM Admin and create a planning task and assign it to User2, User1 being owner of the Campaign gets complete access to this task although role of User1 has neither read nor write access to other people’s tasks. When I check DB, inheritedaccessrightsmask for the object is set to non-zero value which should be for CRM Admin not for User2. In other words, although CRM Admin has reassigned this task to User2, its inheritedaccessrightsmask is not reset. If I login as User1 and create a task for User2, I immediately get access denied and task is created for User2 on which User1 has neither read nor write access.

Since workflows usually run as CRM Admin, it happens with all tasks and Campaign owners can play with approval tasks which is not good.

Resolution:

What we need to do is change the Cascading relationships on Campaigns / Accounts / Contacts / Custom Entities etc. The issue is with the Reparent Cascade behavior. The Reparent is actually what takes care of granting rights when a new child record is created, like a Task. If the Reparent is set to Cascade All, when you create a new Task/Activity under a parent record (Account, Contact, Campaign etc), the Reparent rule grants the Owner of the parent record access to the child. So change it from Cascade All to Cascade User-Owned.

How to enable NTLM authentication for reports in CRM 4.

By default, MS Dynamics CRM uses Kerbaros for reports authentication against SSRS. Now everyone will agree with me that configuring Kerbaros is not an easy option and secondly it requires involvement of infrastructure guys with many "WHY"s. So I was wondering is there any way to avoid all this hassle. Finally with the help of google and reading some forums, I was able to find a nail that can be used to avoid all this.

Open your registry and browse to HKLM->Software->Microsoft->MSCRM key
Add a DWord NTLMForSQLRSServer
Set its value to 1.
Reset IIS.

And enjoy NTLM for accessing your reports rather than Kerbaros

Installing CRM 4 over SQL 2008 and Windows 2008

Yesterday, I decided to install CRM 4 over SQL 2008 and Windows 2008. I have already installed CRM over Windows 2008 but it wasn’t problemtic as I faced issues only with Windows firewall, but this time it was really challenging.

I will try to be specific and will discuss point by point issues that I faced and how I resolved them:

As part of my security policy, I disconencted all my internal servers from internet to ensure restricted deployment scenario. After installing SQL 2008 on a separate box, when I ran CRM setup, it gave me options to either download updated installation files or use existing ones. I decided to use existing ones as I didn’t have internet connection on my server. After passing though all configuration steps, when I reached to last step which was for system diagnostics, I got many errors, out of which I would like to mention 2 ones: the application complained about missing msftesql and cisvc services.

For cisvc.exe, you need to start Windows indexing service on CRM box.
Now for msftesql, it is renamed in SQL 2008, so you cannot find this service anywhere on SQL box. For this, hotfixes are available on Microsoft site but if you will download these hotfixes and try to use them in patch file as mentioned in Microsoft site, it will not work for you and will give you same error. So now what to do. I am stuck. Fortunately it is also solvable as if you will use the "Update installation files" option of setup to update your setup files, it works like a charm but if you will use patch files as part of your installation, it will not work. So after trying few times, I realized that I have to open my connection to Internet on CRM box and let it download updated files.
Now i started getting another error after getting verified for above issues which was "The SQL Server ‘{0}’ is unavailable". Hmm, new challenge. I tried to google and found my resolutions but neither of them worked for me. Finally I realized, it has something to do with Windows firewall of Windows server 2008. So I disabled it and it worked for me. For details of ports which need to be manually opened on SQL box using Windows firewall, consult here.
Hmm, now I am done with issues on verification screen, so can start deployment. Here I will like to mention one more thing that I was using domain account for CRM service not Network Services. So when this time, I started my installation after getting my environment verified, I got an error which was "Action Microsoft.Crm.Setup.Server.ConfigureAspNetAccountAction failed". Again fortunately, it is a known issue and a workaround for it is document on Microsoft support site which is to use Network Service account rather than domain account :S.
Till this time, I was quite frustrated with stupid things and decided to have a break before again continuing. After break, I rerun the setup, went through all above pains and when I reached previous point, using Network Service Account, I was able to move forward, thanks to GOD, but wait a minute, I got another error. This time, it was ""The specified path is not a metabase path." Platform Error: System.Exception: Action Microsoft.Crm.Setup.Server.RSConfigAction failed. —> System.ArgumentException: The specified path is not a metabase path." What the hell is this. Again a know issue, again workaround exist on Microsoft Support Center.
After following this, finally i was able to deploy CRM 4 on SQL 2008 and Windows 2008. It was painfull but possible, so we were able to deploy CRM within same day