Friday 13 November 2009

Attachment Download code in UR 7 - an update

I've updated my original post here to reflect a suggested fix posted by Microsoft on the MSDN Forums here.

Despite my major reservations with how the problem arose, credit is due to Microsoft for responding as quickly as I think they possibly could (even if it doesn't always seem that way).

My reservations relate to disagreements on the need for the change made in UR7 (I don't think it increases security at all), and also to how MS allowed a change that broke supported extensions. It'll be interesting to see what happens with UR 8...

Socket Exhaustion when accessing CRM web services

When submitting a large number requests to the CrmService web service, you may occasionally get the error 'Only one usage of each socket address (protocol/network address/port) is normally permitted'. The reason for this is 'socket exhaustion', which is excellently described here. You are at risk of this happening when over 4000 web requests are submitted within 4 minutes - I find this most commonly on data imports, but have recently met it during heavy user activity on a live CRM system.

There are 2 solutions:
  1. As suggested in the link above, add registry values to give a much wider range of socket addresses. Note that you need to restart the server for these registry changes to take effect
  2. Set the UnsafeAuthenticatedConnectionSharing and PreAuthenticate properties of your CrmService (or MetadateService) proxy to true. This will allow your web request to reuse the same socket

Thursday 12 November 2009

Field Level Security in CRM 4.0 - MS White Paper

The Microsoft Dynamics CRM Engineering for Enterprise (MS CRM E2) team have released a detailed white paper describing the supported options for implementing field-level security in CRM 4.0. It is available for download at http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=471f8670-47b3-4525-b25d-c11a6774615c .

As I was one of the document's technical reviewers, I may be a bit biased in my praise of the document, however one option is missing from it. As a Microsoft document, it concentrates on the supported approaches, but there are also unsupported approaches, such as that taken by c360, which uses an HttpModule to modify the html sent to the client. Personally I dislike this approach, as there is a potentially heavy performance overhead, and it is fragile with respect to future changes in hotfix rollup. However, it is another option that could be considered, and is unfortunately not mentioned in the white paper.

Friday 6 November 2009

UR 7 breaks Attachment Download code, and how to fix it

* 2009-11-13. I've updated this post to reflect some proposed SDK changes published by Microsoft to the MSDN Forums here. I'll probably update this post again for reasons I'll come to later, but I think it's worth making an initial update now *

It was going to happen at some point; a CRM hotfix rollup that broke existing supported customisation code. Update Rollup 7 adds some security changes that apply to the Attachment/download.aspx page. As a result the example code documented here in the SDK no longer works.

If you try the code, the user gets the error message "Access Denied, Invalid Operation". If you enable tracing, you see a message like the following:
"Message: INVALID_WRPC_TOKEN: Validate WRPC Token: WRPCTokenState=Invalid, TOKEN_EXPIRY=4320, IGNORE_TOKEN=False, TOKEN_KEY=b1Nd0byfEd6+gwAZuez7cauYiRnfHuMLAquFY1Ks22dHgxZiW5IrxSobkv9aVfbC, ErrorCode: -2140991221"

The fundamental problem is that CRM now expects additional parameters on the query string that contain a Token, and there is no way for us to generate that token. There's a significant question about why this change was made, but I'll leave that for another time.

The recommended workaround is the use the web services directly as described at http://social.microsoft.com/Forums/en-US/crmdevelopment/thread/2f46a3c4-1123-49dd-804e-2787ef367986, which I expect to make it into the SDK soon. This is close to a like-for-like replacement for the original SDK code in that it is the type of code you might write for a console application.

However, a popular use of download.aspx was to provide any easy client-side link to download an attachment, rather than doing it programmatically with .Net code. To get equivalent behaviour you'll need a custom .aspx page that gets the uses similar code to that above, but writes it the response stream using Response.BinaryWrite. I'm going to have to write this code soon, and when I do I'll post it on the MSDN Code Gallery, and link to it here; in the meantime if anyone has to write it themselves the other thing you should do is pass information on the Response-Header to specify the filename. From memory this involves something like:

Response.AddHeader("content-disposition","attachment; filename=" + outputFile);

Thursday 5 November 2009

SSIS Packages and Excel Data on 64 bit machines

We do a fair amount of integration work between CRM and Excel using SSIS, and it's all got a bit harder with 64 bit machines. The main issues are:

There is no 64 bit OLEDB provider for Excel
So the packages cannot be executed by 64 bit code. This meant that the programmatic route for running packages with the classes in the Microsoft.SqlServer.Dts.Runtime namespace no longer works. The error you get is typically unhelpful - 'AcquireConnection method call to the connection manager "EXCEL: Source" failed with error code 0xC00F9304'. You can also get error code 0xC020801C.

The workaround is to run a package using the 32 bit version of dtexec.exe, which should be installed in Program Files (x86)\Microsoft SQL Server\100\DTS\Binn. This is also the fix if scheduling packages with the SQL Agent; your job steps need to be of type CmdExec instead of SSIS Package, and you have to learn the command line syntax of dtexec.

Overall, something of a pain, but I've not yet found anything I can't do with dtexec.exe and its command line parameters.

The Registry settings for the 32 bit OLEDB provider for Excel have moved
There are a several registry settings that you may need to make the OLEDB provider for Excel work, as described here and here. When running a package in 32 bit mode on a 64 bit server, you find that it uses registry keys in a different location.

Values that were in HKLM\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel are now in HKLM\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel.

Note also that the paths to the providers have changed, so for example "Win32"="C:\Windows\system32\msexcl40.dll" becomes "Win32"="C:\Windows\syswow64\msexcl40.dll"

Although my emphasis here has been on Excel providers, and SSIS, the issues and resolutions can apply to other OLEDB providers and environments, as there are several OLEDB providers that are not expected to have 64 bit releases (the Exchange OLEDB provider, and the FoxPro one are 2 that I'm aware of)