Windows NT Authentication Provider - it can work at several levels

One of the diagnostics checks performed by the CRM Environmental Diagnostics Wizard prior to installation is to confirm that the CRM web site has the NTAuthenticationProvider set to 'Negotiate,NTLM'. I've never entirely worked out why this is necessary, especially as I've subsequently changed the Authentication provider after installation with no adverse effects (* though see below), but that's not the point of this post.

The main point is that the NTAuthenticationProvider attribute can be viewed or set at several levels via the adsutil.vbs script. This is reasonably well documented in the Technet article referenced from the EDW help file. However, what is not made clear is that the attribute can be set at one of 3 levels. Each of the following commands does something slightly different:

cscript adsutil.vbs set w3svc/WebSite/root/NTAuthenticationProviders "Negotiate,NTLM"
cscript adsutil.vbs set w3svc/WebSite/NTAuthenticationProviders "Negotiate,NTLM"
cscript adsutil.vbs set w3svc/NTAuthenticationProviders "Negotiate,NTLM"

The third command is noticeably different, in that it sets the attribute at the server level, rather than the web site level. I can't see any particular reason for different behaviour between the first 2 commands, but it does affect the CRM EDW. As far as I can tell, you need to use the syntax in the second command to satisfy the EDW, whereas unfortunately the Technet article uses the syntax in the first command.

* Re what Authentication Provider to use. I've happily run a single server implementation of CRM using NTLM authentication, but I expect Kerberos may be necessary in a multi-server implementation

Reports on CRM Privileges - Update to view by User

I've added 2 reports to the resource on the MSDN Code Gallery. These show cumulative privileges by user across their roles

Hidden CRM Privileges

Not all CRM privileges are visible within the CRM User Interface. I recently spent some time investigating what privileges exist in CRM, and how the privilege information is stored in the MSCRM database.

The results of these investigations have been posted on the Microsoft Dynamics CRM Team Blog. I also created a couple of reporting services reports to display the privilege data by role. These are available on the MSDN Code Gallery

Some undocumented CRM attribute types

I was working on some custom plugin registration tools recently, and came across some undocumented attribute types. Several customisation entities (e.g. PluginAssembly, PluginType, SavedQuery) have an attribute called CustomizationLevel which has a value of 0 for system data, and 1 if it is custom or customised (according to the SDK).

The SDK documentation states this attribute is of type CrmNumber, so when writing a QueryExpression that selects only records with a CustomizationLevel = 1, it would seem reasonable to use something like:

qe.Criteria.AddCondition(new ConditionExpression("customizationlevel", ConditionOperator.Equal, 1));

However, this gives the error “Condition for attribute 'customizationlevel': expected argument(s) of type 'System.Byte' but received 'System.Int32' " (code 0x80040203 - Invalid Argument). Digging deeper I found that the field for the CustomizationLevel is stored in SQL as a tinyint (i.e. a single-byte integer), and that the AttributeTypes table in CRM has a corresponding AttributeType of tinyint.

So, despite the attribute being identified as a CrmNumber, any condition expressions need to pass values as a single-byte integer, not the documented four-byte integer. This is easily done by using the following:

qe.Criteria.AddCondition(new ConditionExpression("customizationlevel", ConditionOperator.Equal, (Byte) 1));

Being somewhat nosey, I thought to see what other attribute types there were. These can be easily found with the following SQL query:

Select * from AttributeTypes

In addition to tinyint, 2 similar types caught my attention – smallint and bigint (SQL Server data type names are not that imaginative). Following on from this, the following SQL query lists attributes of these types, which may cause similar problems to those above:

select e.name as Entity, a.name as Attribute, at.description as [Type]
from attribute a join entity e on a.entityid = e.entityid
join attributetypes at on a.attributetypeid = at.attributetypeid
where at.description in ('tinyint', 'smallint', 'bigint')
order by at.description, e.name, a.name

This yields the following results. The bigint attributes aren’t a concern, as these 2 attributes aren’t available via the CRM platform, but you could encounter some of the smallint types on UserSettings, in which case I expect you’d have to cast values to Int16.

Entity	Attribute	Type
AsyncOperation	sequence	bigint
Subscription	completedsyncversionnumber	bigint
Organization	tokenexpiry	smallint
UserSettings	advancedfindstartupmode	smallint
UserSettings	timezonecode	smallint
UserSettings	timezonedaylightday	smallint
UserSettings	timezonedaylightdayofweek	smallint
UserSettings	timezonedaylighthour	smallint
UserSettings	timezonedaylightminute	smallint
UserSettings	timezonedaylightmonth	smallint
UserSettings	timezonedaylightsecond	smallint
UserSettings	timezonedaylightyear	smallint
UserSettings	timezonestandardday	smallint
UserSettings	timezonestandarddayofweek	smallint
UserSettings	timezonestandardhour	smallint
UserSettings	timezonestandardminute	smallint
UserSettings	timezonestandardmonth	smallint
UserSettings	timezonestandardsecond	smallint
UserSettings	timezonestandardyear	smallint
Organization	fiscalyeardisplaycode	tinyint
Organization	tagmaxaggressivecycles	tinyint
Organization	trackingtokeniddigits	tinyint
OrganizationUI	customizationlevel	tinyint
PluginAssembly	customizationlevel	tinyint
PluginType	customizationlevel	tinyint
SavedQuery	customizationlevel	tinyint
SdkMessage	customizationlevel	tinyint
SdkMessageFilter	customizationlevel	tinyint
SdkMessagePair	customizationlevel	tinyint
SdkMessageProcessingStep	customizationlevel	tinyint
SdkMessageProcessingStepImage	customizationlevel	tinyint
SdkMessageProcessingStepSecureConfig	customizationlevel	tinyint
SdkMessageRequest	customizationlevel	tinyint
SdkMessageRequestField	customizationlevel	tinyint
SdkMessageRequestInput	customizationlevel	tinyint
SdkMessageResponse	customizationlevel	tinyint
SdkMessageResponseField	customizationlevel	tinyint

Reading RDL Definitions directly from a ReportServer database

Just a quick post: I recently had to extract the report definitions (RDL) directly from a ReportServer database, as the ReprtServer installation was broken. This should be straightforward, but requires some work to convert the data to a readable format. This can be done with the following SQL query:

select convert(varchar(max), convert(varbinary(max), content))
from catalog
where content is not null

MS Dynamics Top 100 Influential People

Sometimes it's good to come last. I was honoured and flattered to find my name at no. 100 (that's me, last on the list) in “The Microsoft Dynamics Top 100 Influential People 2009" at DynamicsWorld.


It's good to see several other CRM MVPs ahead of me on the list - you can find out who they are here

CRM 4 Update Rollup 5

As several others have announced, CRM 4 Update Rollup 5 has been released. There are a couple of notable points to make:

1. As with update rollups 3 and 4, the majority of the fixes have not been previously released as an individual hotfix. This is different from rollups 1 and 2, which were mostly a collection of previously released fixes. In general, I think that having fewer individually released fixes is a positive thing, but that has to be traded off against the frequency of rollups (currently about every 6 to 8 weeks), which is difficult to keep up with. Across our customers, we have deployments covering 5 different rollup levels, plus some customers on CRM 3, which needs a fair bit of management, especially with regard to schema compatibility
2. So, schema compatibility. Apparently, UR5 schemas cannot be imported into versions prior to UR2, but also, pre-UR2 schemas cannot be imported into UR5. I've got some investigation to do on this, but the most important factor for me is being able to use one development environment that can be used to export (and import if necessary) schemas to all rollup versions of CRM 4. Up to now I've rarely applied rollups to development environments, but it looks like from now on, UR2 will be a minimum requirement for a development environment. Either way, it makes life harder than it should be.

Report Server location and the error: The specified path is not a metabase path

One of the ‘known’ issues with using CRM 4.0 and ESRV0008 is that CRM does not realise that SQL 2008 Reporting Services hosts the ReportServer application outside of IIS. Hence, when you try and edit the ReportServer URL in CRM Deployment Manager, or install CRM, you may get the error "The specified path is not a metabase path". As I said, this is a known issue, and a workaround is described at http://support.microsoft.com/kb/957053 (issue no. 6 in the article).

The problem though is that this workaround doesn’t always, well, work. I tried this, with no success whatsoever. Eventually, I went back to some other issues CRM had had with identifying IIS metadata in the past with CRM 3.0 (e.g. http://support.microsoft.com/kb/916164 ). What I had to do was temporarily change the IIS bindings so that the default web-site (i.e. web-site with ID=1) was on port 80. As long as this is the case when you edit the Report Server URL, then everything works. Fortunately, all I was doing was editing an organisation in an existing deployment, and could arrange downtime to temporarily change web-site bindings; if you’re installing a new CRM deployment it may be harder to play with web-site bindings. For completeness, the steps to make this work were:

1. In IIS Manager, create a new web-site with bindings that match those of the ReportServer URL, as per issue 6 in KB 957053 (link above)
2. In IIS Manager, change the current web-site bindings so that the web-site with ID=1 is on port 80 with no host-headers (if necessary turn off your phone and don’t check email while people try and complain they can’t access their web-sites)
3. In CRM Deployment Manager, disable the organisation, edit the organisation an put in the ReportServer URL you want and continue till the organisation has been updated successfully. Enable the organisation
4. Back in IIS Manager, change the web-site bindings back to how they were, turn your phone back on, and find someone/something else to blame for the temporary outage

Why Microsoft code things assuming the default web-site is on port 80 is beyond me. Oh well.

During my investigations I found out a few more things:

• McAfee anti-virus software hooks into some popular alternate ports (in this case 8081), and it’s not easy to diagnose conflicts when you put ReportServer and McAfee on the same port
• Configuring multiple bindings for the ReportServer URL works fine
• 'All' the above does is make sure that CRM populates the ReportServer with the correct folders, reports, data sources and permissions. It is possible to set all this up manually and get it to work, but it’s very tedious and you may need to change the Guids that identify the reports within either CRM or Report Server

CRM Design Patterns - and back on the MSCRM Team Blog

I seem to be using my invitations to post on the MSCRM Team blog as a prompt to publish posts - I've posted a starter post on Design Patterns.

I'm intending this to be the first in a series of posts on Design Patterns for MS CRM. I'm using the term 'Design Pattern' deliberately to echo the same term used in object-oriented design, though I'm not intending the posts to be particularly formal or prescriptive. If you're interested in the more formal ideas of design patterns, wikipedia as good a place as any to start.

My reasons for investing time in this are two-fold:

1. I don't like having to do thinkgs more than once. When designing MS CRM solutions for a customer probably about 90% of the design decisions I make now are wholly or partly based on a previous, similar design for another customer. A lot of this information is in my head; the intention of building design patterns is to create a structure to be able to communicate this information to others
2. MS CRM solutions are a mix of out-of-the-box functionality, and specific customisations and extensions. This gives a lot of options, and a lot of additional considerations beyond traditional ground-up development, or out-of-box deployment, and I think there's a lot of new and interesting design work that can be done in this environment

Using the IIS Logs to get CRM Usage information

I've been posting on the Microsoft CRM Team blog again.

The article describes how to make use of the IIS logs to get useful information about who is accessing CRM and when.

This technique uses some SQL objects (tables, view and functions), and a reporting services report. The source code for these is available on the MSDN Code Gallery