Request a topic or
contact an Arke consultant
Arke Systems Blog | Useful technical and business information straight from Arke.

Arke Systems Blog

Useful technical and business information straight from Arke.

About the author

Author Name is someone.
E-mail me Send mail

Recent comments




The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

© Copyright 2022

Microsoft Dynamics CRM 4 and systemuserid

Sometimes you need to know how User ID works “under the hood.”

We see ‘systemuserid’ all over the database identifying a user … but how does it decide which systemuserid to use?

CRM appears to figure out which user is logging in by querying the MSCRM_CONFIG database systemuserauthentication table looking for a value in the authinfo column that matches the objectsid for the user from activedirectory (SID, *not* GUID).

You can get the user SID (and GUID) from active directory by registering a dll from the windows server 2003 resource toolkit ( ) on a 32-bit OS (I don't know how to make it work on 64 bit; docs say 64 bit is not supported) - install toolkit, copy acctinfo.dll to c:\windows\system32, "regsvr32 acctinfo.dll" , then browse to user in AD ( start/run/dsa.msc ) and look at the 'additional account info' tab (doesn't show up when user is found using find, unfortunately).

Then you can dig up the entry for the sid in systemuserauthentication, which gives you a userid, which you can find in systemuserorganization, which gives you a crmuserid, which you can go find as systemuser in the [orgname]_mscrm database (which is NOT the same as systemuser in the mscrm_config database).

If you have a problem with a mismapping between the two databases (for example, somebody mistakenly doing a backup of one but not the other), there are two easy ways to fix the problem:

The first thing to try is to edit the user the in the normal CRM interface and change the AD login name to a not-yet-used-by-CRM value, save the entity, then change it back to the value you want to be used.  This often cleans up a broken mapping.

The next thing to try is to use the crm deployment manager to export and import the organization; during the import there is a user mapping step where you can have CRM set up these relationships properly for you.

Finally, filtered views have their own method of identifying the user – they use [fn_FindUserGuid] which checks for a GUID on context_info(), and if not found, they query SystemUserBase where DomainName = SUSER_SNAME() .

I recommend against trying to operate on this data at the raw sql level, system user id is simply referenced in too many places to reliably get everything.  You would end up with orphaned records at best, for example due to all the ‘createdby’ and ‘modifiedby’ references on every entity.  So ‘select’ is fine to learn things, but ‘delete’ or ‘update’ at your own risk and preferably not even then.  Still, if you have to, the AD SID is where to start.

Posted by David Eison on Monday, June 7, 2010 5:45 PM
Permalink | Comments (0) | Post RSSRSS comment feed

Sitecore: A Simple Site Map

Similar to creating a menu – creating a site map is even easier, there are modules that can do this but with a little preparation it can be very easy to create yourself.

To start we are assuming every template used for the pages includes some ‘base template’ for common aspects for all pages.

To do this, create (or select if already created) the Base Template with the information you want to have and to that base template, add:

InSiteMap: Checkbox

Then create or modify the Page templates with the other information you want. From those templates, click to the Content tab, and select the Base template to be included:



In the Standard values for the base template, you may want to have InSiteMap checked so that it will be on by default for all the pages.

For the sitemap itself, we’re going to create a rendering and then we can place it wherever we would like.

The rendering creates a basic nested list of all the items that have InSiteMap checked.

<xsl:template match="*" mode="main">
        <sc:link select="$home">
        <xsl:variable  name="hasSubItems" select="$home/item[sc:fld('InSiteMap',.) = '1']"/>
        <xsl:if test="$hasSubItems">
          <xsl:call-template name="subitems">
            <xsl:with-param name="itemparent" select="$home" />


  <xsl:template name="subitems">
    <xsl:param name="itemparent" />
      <xsl:for-each select="$itemparent/item[sc:fld('InSiteMap',.) = '1']">
            <sc:text field="Page Title" />
        <xsl:variable  name="hasSubItems" select="item[sc:fld('InSiteMap',.) = '1']"/>
        <xsl:if test="$hasSubItems">
          <xsl:call-template name="subitems">
            <xsl:with-param name="itemparent" select="." />

Above we list the link to the home item in an unordered list:

 <sc:link select="$home">

<sc:link> with no parameters will link to the current item that is being processed by the xslt file – if not specified by any settings this will default to the page that the rendering is on. Since the current page is Not actually the home page, we are choosing to use $home as the source for the link (if the source for the link was actually in a field in the item you’d used field=”field name”).


Moving on, if there are any items under home that are listed as ‘InSiteMap’ it will call our template named subitems and pass the value for the parent of the item.

        <xsl:variable  name="hasSubItems" select="$home/item[sc:fld('InSiteMap',.) = '1']"/>
        <xsl:if test="$hasSubItems">
          <xsl:call-template name="subitems">
            <xsl:with-param name="itemparent" select="$home" />


Here the variable hasSubItems is created and the value for this is determined by any items within $home containing the field InSiteMap being checked (=’1’ – unchecked would be =’0’).

If we have any items that will be in the sitemap under $home, we call our template.

Within the template, we create a new unordered list, and then go through each item which will be listed in the site map and display a link using the ‘Page Title’ (this is any field that we want to have displayed to represent the item). We then check to see if we have any subitems to display and run the template again! With a bit of recursion, we head through all the items on the site as long as they and their parents have InSiteMap checked.

All that is left is adding the rendering to a page to be seen, and it will display a nested list of the pages on the site to be styled as needed!

Alternately, instead of checking for a selected checkbox, it could run through all pages with a specific template(or selection of templates) if you did not need to worry about excluding any specific pages that are using it.

Categories: Sitecore
Posted by Amy Winburn on Friday, June 4, 2010 5:29 PM
Permalink | Comments (0) | Post RSSRSS comment feed

Microsoft Dynamics CRM and Editing data in Excel

Customers sometimes need a good way to bulk edit data in CRM. 

One solution is to export the data, adjust it outside of CRM in a tool like Excel, then re-import it.

You can get an ms crm import tool from (read about it at )

A more powerful and user friendly Excel editing tool is EBAX:

In general I think EBAX is a good compromise of raw editing-in-Excel power and CRM's use-our-web-services restrictions, so I recommend it particularly to clients who have a few users who need some powerful or bulk access. 

The only consideration I’m aware of for a bulk editing tool in a deployment is that it’s important to train both the power users and developers that fields which were disabled via Javascript aren’t really disabled – users should avoid editing a custom calculated total field, and developers should avoid expecting a user to be unable to edit a custom calculated total field simply because it was disabled on the web form.

Posted by David Eison on Tuesday, June 1, 2010 1:22 PM
Permalink | Comments (0) | Post RSSRSS comment feed

Microsoft Dynamics 4.0 and SQL Updates

Customers often want to know why they aren’t supposed to update their data via raw SQL and are instead directed to use the CRM web forms or the SDK.

These are the reasons I am aware of:

1) Various important parts of CRM rely on C# code that will only run properly if everything is accessed through the web services (plugins and workflows)

2) Microsoft wants to be able to change the inner workings in future upgrades, and if everyone developed relying on particular columns being in particular tables upgrades would become impossible.

3) It's a complicated system and it's easy to miss a detail, such as the isdeleted column or state codes or timezone conversions.  These details can be abstracted away for read only access when querying against the filtered views, which automatically removed columns with the deleted flag and convert utc timezones to local timezone and check that the user querying has permission to view the record, etc.  But you can't readily do such abstraction for updating.

4) There is a security model in place that gives each user permissions to do certain things based on roles granted to them, and if you rely on working as a priviledged user in the database you might accidentally circumvent that. 

5) Some calculated fields like totals are 'not valid for update', but this is only enforced at the web services level (for example, totals can’t be directly updated because any value you might try to submit will be overwritten by a plugin calculating it).

6) There is an added level of complexity at the raw database level - base vs extensionbase tables, for example - that is more difficult to work with.

7) One wrong value can lead to system errors.  For example, deleting a row from the extensionbase but not the base table, or setting a picklist to an impossible value, can cause spurious errors when later trying to work with the information through the normal interface.

8) Schema changes in particular are tracked with various metadata that you really don't want to get into and you can break everything in very difficult to recover ways by manually changing column definitions.

Posted by David Eison on Tuesday, June 1, 2010 1:15 PM
Permalink | Comments (0) | Post RSSRSS comment feed

Microsoft Dynamics CRM 4.0 and SQL selects

Customers also want to know why they are supposed to use ‘Filtered’ views to query their CRM data directly from the database.

These are the reasons I’m aware of for using filtered views:

The filtered views do five things for you:

1) They are ‘fully supported’, meaning that they shouldn’t break on upgrades. 

2) Convert times from utc to user’s local timezone (UTC times are still available in the filtered views, but the default time column is changed to local time)

3) Filter out records flagged as to be deleted.

4) Filter out records the current user doesn't have permission to see.

5) Clean up and hide a bunch of implementation details, like mapping state codes from ints like 0 to strings like 'Active'.

The only drawback is that filtered views may slow down queries as they join in a lot of tables to present their data, and you will want to keep track of timezone conversion challenges (if a user entered a time in eastern daylight time, it’s stored in UTC/Greenwich Mean, then the querying user is in Australia… what day did the event happen on?)

Posted by David Eison on Tuesday, June 1, 2010 1:13 PM
Permalink | Comments (0) | Post RSSRSS comment feed

Dynamics CRM 4: Fixing broken entities

The reason entities sometimes break after importing in CRM is that CRM only exports relationships between two custom entities if both entities are included in an export. I assume this is to avoid breaking on import into a system that doesn’t have the associated custom entity.  You can avoid the problem if you are able to export all related entities together, but sometimes you can’t do that because one is not ready to be deployed for example.

I saw the problem show up when trying to export all forms with a Javascript export tool (both so I could search our javascript easily with Agent Ransack and so I could check in any recent changes to keep a history):

“"\n 0x80040216\n An unexpected error occurred.\n Platform\n"”

Trace log showed:

System.Web.Services.Protocols.SoapException: Server was unable to process request. ---&gt; System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---&gt; System.NullReferenceException: Object reference not set to an instance of an object.

at Microsoft.Crm.ObjectModel.OrganizationUIService.LabelLoaderPublished.LoadMetadataLabel(Int32 entityType, String attributeName, ExecutionContext context)

Exporting all customizations manually via the export UI resulted in the same exception as was in trace log:

Problem XML

At that point I did a manual binary search of exporting entities to narrow down which entity was the problem.  (It might also show up in trace log; in my case, no such luck.)

Going back to customize entities and looking at the form for the offending entity, there were empty spaces where links to two particular custom entities should be.

Problem Form

Goal now is to delete them from the form and the entity so I can recreate them. has the steps:

Open up the problem attributes and note their names.

Create temporary attributes with the same names. This turns the previously broken references on the form into valid references to a text attribute.

Then delete the attributes from the form and any views & publish. This gets references to the attributes off the list of published attributes.

Then delete the attributes and save and publish again to remove any final traces of them.

Then I can manually recreate the relationships and re-add them to the form and views.

Posted by David Eison on Thursday, May 27, 2010 3:47 PM
Permalink | Comments (0) | Post RSSRSS comment feed

SQL 2008 Won’t Install – Error About Conflict with SQL 2005 Express

I’ve been fighting an error trying to install SQL 2008 R2.  The installer gives an error, “The SQL Server 2005 Express Tools are installed. To continue, remove the SQL Server 2005 Express Tools.”  It turns out, the error was due to Red Gate SQL Compare, not SQL 2005.  Thanks to this article, I was able to uninstall Red Gate to get SQL 2008 R2 installed.

Posted by Eric Stoll on Thursday, May 27, 2010 1:55 PM
Permalink | Comments (0) | Post RSSRSS comment feed

Sitecore:Web Forms for Marketers: Send Email

To have the save action for your form actually send email, you will need to change one of the settings, otherwise you will receive this error whenever submitting the form:

We experience a technical difficulty while processing your request. Your data may not have been correctly saved.

Also in your log (/data/logs/newest log file) you will see this error after the form has been submitted:

Exception: System.Net.WebException
Message: The remote name could not be resolved: ''
Source: System
   at System.Net.ServicePoint.GetConnection(PooledStream PooledStream, Object owner, Boolean async, IPAddress& address, Socket& abortSocket, Socket& abortSocket6, Int32 timeout)
   at System.Net.PooledStream.Activate(Object owningObject, Boolean async, Int32 timeout, GeneralAsyncDelegate asyncCallback)
   at System.Net.PooledStream.Activate(Object owningObject, GeneralAsyncDelegate asyncCallback)
   at System.Net.ConnectionPool.GetConnection(Object owningObject, GeneralAsyncDelegate asyncCallback, Int32 creationTimeout)
   at System.Net.Mail.SmtpConnection.GetConnection(String host, Int32 port)
   at System.Net.Mail.SmtpTransport.GetConnection(String host, Int32 port)
   at System.Net.Mail.SmtpClient.GetConnection()
   at System.Net.Mail.SmtpClient.Send(MailMessage message)
WARN  Web Forms for Marketers: an unhandled exception: Failure sending mail. has occured while trying to execute an action.

In your content tree, browse down to: /sitecore/system/Modules/Web Forms for Marketers/Settings/Actions/Save Actions/Send Mail

Under that item there will be a submit section with parameters:


Just replace with your SMTP host information for the server, and with the address you want to send mail from – save, and republish!

That should correct the error above and, barring any other issues, hopefully the form will be working as intended!

Posted by Amy Winburn on Tuesday, May 18, 2010 6:16 PM
Permalink | Comments (0) | Post RSSRSS comment feed

Nicer Sitecore URL’s

Sitecore has a concept for replacing special characters in a URL. By default, their config file isn’t set to translate spaces. So URLS like, World.aspx translate to For SEO purposes, its best to translate spaces. Add the following line to your Sitecore web.configs so the URL would translate to

    <replace mode="on" find="&amp;" replaceWith=",-a-," />
    <replace mode="on" find="?" replaceWith=",-q-," />
    <replace mode="on" find="/" replaceWith=",-s-," />
    <replace mode="on" find="*" replaceWith=",-w-," />
    <replace mode="on" find="." replaceWith=",-d-," />
    <replace mode="on" find=":" replaceWith=",-c-," />
    <replace mode="on" find=" " replaceWith="-" />

Posted by Eric Stoll on Tuesday, May 18, 2010 10:32 AM
Permalink | Comments (1) | Post RSSRSS comment feed

Sitecore: External and Internal Links as Items

I ran into a problem creating the navigation for the footer rendering on a page – On there I needed to have links to the following pages: terms, contact, careers, and the sitemap. The problem arose when trying to add ‘Contact’ and ‘Careers’: ‘Contact’ was a few levels down in the tree of content items and ‘Careers’ was an external page. Initially, I had a checkbox in the template of each page for if the item was in the footer (ie: InFooterNav checked) and then the XSLT file would go through the entire list of items to find what would be in the footer navigation. It wasn’t an ideal solution when so few items had the tag in the entire site and it did not help with my external link at all. Thus was born: The Link Template!

The new template basically consisted of:

Link Data:

  • Link: General Link //This is the link to our item or the URL for the external page
  • Nav Title: Single Line Text //The title we want to have displayed in our menu – this is the same as in my regular items
  • Target: Single Line Text (or a drop down list) //The target if we want to open the page in a new window


  • InFooterNav: Checkbox //Just a checkbox to see if this link is going to be in our footer, this is the same as regular items.

Within the Standard Values I assigned a new layout for the items with the following code within:

<% Sitecore.Data.Items.Item item = Sitecore.Context.Item; 
   Response.Redirect(((Sitecore.Data.Fields.LinkField)item.Fields["Link"]).Url); %>

The above redirects the user to whatever URL was provided within the “Link” field.

I then created a bunch of items with the appropriate links in the folder where other footer only type items also lived – for our example let us say “/sitecore/content/Home/Footer/”

To retrieve my footer items I now only had to check that folder and not search the entire site to actually see if the box is checked.

And a little more useful - using this template, I could put a link to any external page under any item – allowing me to have external links within my navigation such as Careers.

The above layout will work fine for my footer, but what if you wanted links to open in other windows? Or didn’t want to take the extra step of creating a layout? The rendering can be changed to make use of our new template:

      <xsl:for-each select="$home/Footer/item[sc:fld('InFooterNav',.) = '1']">
            <xsl:when test="@template = 'link template'">
              <xsl:variable name="linkTarget" select="sc:fld('Target',.)" />
              <sc:link field="Link" target="{$linkTarget}" >
                <sc:text field="Nav Title" />
                <sc:text field="Nav Title" />

Above we have a choose statement to determine if this is using the new link template or not (called “Link Template” but listed in lowercase):

<xsl:when test="@template = 'link template'">


Then we create a variable for our target and create the link based on our General Link field called “Link” by specifying it for field:

<xsl:variable name="linkTarget" select="sc:fld('Target',.)" />
<sc:link field="Link" target="{$linkTarget}" >
  <sc:text field="Nav Title" />


Otherwise if it is a normal item in the tree, we give it a normal link and continue to the next item!

     <sc:text field="Nav Title" />

Categories: Sitecore
Posted by Amy Winburn on Thursday, May 13, 2010 2:41 PM
Permalink | Comments (0) | Post RSSRSS comment feed