CRM 2011 database sql investigation

--List down the name of solutions and entity/form where this label is used.
select FriendlyName, LocalizedLabel.* from [LocalizedLabel]

inner join Solution on LocalizedLabel.SolutionId = Solution.SolutionId where LocalizedLabelId = 'localised label id'

--Published Form Xml
select FriendlyName,SystemForm.* from SystemForm inner join Solution on SystemForm.SolutionId = Solution.SolutionId where FormId = 'formid'

--Form Xml exists in solution
select FriendlyName,SystemFormBase.* from SystemFormBase inner join Solution on SystemFormBase.SolutionId = Solution.SolutionId where FormId = 'form id'

--List down the Form Xml for the solution which matches the published formxml solution

select FriendlyName,SystemFormBase.* from SystemFormBase inner join Solution on SystemFormBase.SolutionId = Solution.SolutionId inner join SystemForm on SystemForm.FormXml = SystemFormBase.FormXml where SystemForm.FormId = 'form id'

systemuser with Id = guid Does Not Exist

I have created a crm org in one server, then imported that org from that server to dev server and then imported few solutions on top it.

things were fine however in order to diagnose one of the bug client was facing I deployed solution to debug as I used to do in my old dev machine/domain.

When solution was deployed some of the plugins were working fine however on couple of plugins I was getting following error

Unhandled Exception: System.ServiceModel.FaultException`1[[Microsoft.Xrm.Sdk.OrganizationServiceFault, Microsoft.Xrm.Sdk, Version=5.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35]]: systemuser With Id = 56702f79-c33a-e111-8820-00155d450361 Does Not ExistDetail:
<OrganizationServiceFault xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/xrm/2011/Contracts">
  <ErrorCode>-2147220969</ErrorCode>
  <ErrorDetails xmlns:d2p1="http://schemas.datacontract.org/2004/07/System.Collections.Generic" />
  <Message>systemuser With Id &amp;#61; 000000000-0000-0000-0000-000000000000 Does Not Exist</Message>
  <Timestamp>2013-03-13T10:05:18.7976683Z</Timestamp>
  <InnerFault i:nil="true" />
  <TraceText>
[Plugins:Plugins.PluginName]
[000000000-0000-0000-0000-000000000000: PluginName]
Entered Plugins.PluginName.Execute(), Correlation Id: 000000000-0000-0000-0000-000000000000, Initiating User: 000000000-0000-0000-0000-000000000000 Plugins.PluginName is firing for Entity: prefix_entityname, Message: Update, Correlation Id: 000000000-0000-0000-0000-000000000000, Initiating User: 000000000-0000-0000-0000-000000000000 Exception: System.ServiceModel.FaultException`1[Microsoft.Xrm.Sdk.OrganizationServiceFault]: systemuser With Id = 000000000-0000-0000-0000-000000000000 Does Not Exist (Fault Detail is equal to Microsoft.Xrm.Sdk.OrganizationServiceFault)., Correlation Id: 000000000-0000-0000-0000-000000000000, Initiating User: 000000000-0000-0000-0000-000000000000
Exiting Plugins.PluginName.Execute(), Correlation Id: 000000000-0000-0000-0000-000000000000, Initiating User: 000000000-0000-0000-0000-000000000000
</TraceText>
</OrganizationServiceFault>

when trying to find something from plugin registeration tool, nothing was strange, plugins are running under calling user.

REASON/Resolution: This was happing due to I am using crm development toolkit, and in RegisterFile.crmregister I am using ImpersinatingUser to run plugin under certain useraccount. and here it had old crm user GUID which was valid in different domain and crm org but for my current new crm orgs under new domain are invalid.

SQL Profiling

I was investigating a bug on UAT environment which was one-off and apparently not reproduce able in  other environments. After spending many hours I didn't know what was causing the issue due to lack of logging and error message in event viewer. I was left with 2 options first to enable tracing on CRM server and second was to go to SQL profiling which is not a preferred option generally. However due to number of front end servers running, turning on tracing on each of them was not my preferred option in this case and I opted for SQL profiling.  I was generating SQL tracing information for the action which was giving error, and I made sure we do SQL profiling after business hours.

Before starting the profiling stop any service which can trigger lots of SQL tracing. In my case i was investigating some ui issue so I disabled Microsoft Async Service and Reporting Service for some time.
Following are the steps to do sql profiling.

Open SQL server Management studio, then go to "Tools" -> "SQL Server Profiler" option.

On Trace properties prompt shown select "Event Selection" tab.
Select "Show all events" check-box.

Expand security option and select
- Audit Login
-Audit Logout

Expand Stored Procedures option and select
-RPC output parameter
-RPC:Completed
-RPC:Starting
-SP:Completed
-SP:Starting
-SP:StmtCompleted
-SP:StmtStarting

Expand TSQL
-SQL:BatchCompleted
-SQL:BatchStarting

Could not load file or assembly 'Microsoft.Crm.Sdk, Version=4.0.0.0, Culture=neutral

If ISV code is giving following error

Could not load file or assembly 'Microsoft.Crm.Sdk, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference

This is most likely due to latest version of CRM is installed which has 5.0.0.0 assembly, and ISV code was developed using Version=4.0.0.0 crm.sdk assembly.


To solve this issue open isv code's web.config file and add following config after <configSections> node and do an iis reset code.

<runtime>

    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">

      <dependentAssembly>

        <assemblyIdentity name="Microsoft.Crm.Sdk" publicKeyToken="31bf3856ad364e35" culture="neutral" />

        <bindingRedirect oldVersion="4.0.0.0" newVersion="5.0.0.0"/>

      </dependentAssembly>

    </assemblyBinding>

  </runtime>

An attempt was made to load an assembly from a network location

When trying to register an assembly (This assembly was extracted from existing unmanaged solution) through plugin registeration tool, i was getting following error

Unhandled Exception: System.IO.FileLoadException: Could not load file or assembly 'file:///C:\Users\svccrmcapdev\Desktop\<<assembly name>>-<<guid>>\<<assembly name>>-<<guid>>\<<assembly name>>.dll' or one of its dependencies. Operation is not supported. (Exception from HRESULT: 0x80131515)

   at System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)

   at System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection, Boolean suppressSecurityChecks)

   at System.Reflection.RuntimeAssembly.InternalLoadFrom(String assemblyFile, Evidence securityEvidence, Byte[] hashValue, AssemblyHashAlgorithm hashAlgorithm, Boolean forIntrospection, Boolean suppressSecurityChecks, StackCrawlMark& stackMark)

   at System.Reflection.Assembly.LoadFrom(String assemblyFile)

   at Microsoft.Crm.Tools.PluginRegistration.AssemblyReader.LoadAssembly(String path)

   at Microsoft.Crm.Tools.PluginRegistration.AssemblyReader.RetrievePluginsFromAssembly(String path)

   at Microsoft.Crm.Tools.PluginRegistration.AssemblyReader.RetrievePluginsFromAssembly(String path)

   at Microsoft.Crm.Tools.PluginRegistration.RegistrationHelper.RetrievePluginsFromAssembly(String pathToAssembly)

   at Microsoft.Crm.Tools.PluginRegistration.PluginRegistrationForm.btnLoadAssembly_Click(Object sender, EventArgs e)

Inner Exception: System.NotSupportedException: An attempt was made to load an assembly from a network location which would have caused the assembly to be sandboxed in previous versions of the .NET Framework. This release of the .NET Framework does not enable CAS policy by default, so this load may be dangerous. If this load is not intended to sandbox the assembly, please enable the loadFromRemoteSources switch. See http://go.microsoft.com/fwlink/?LinkId=155569 for more information.

--------

Resolutions: Right click on assembly and go to properties, In General tab, next to security label following message would be shown "This file came from another computer and might be block to help protect this computer". Click on Unblock to register this assembly through plugin registeration tool. 

CRM 2011 Security settings for this service require Windows Authentication but it is not enabled for the IIS application that hosts this service

When trying to connect to org through Plugin registration tool, was getting error.

When tried to browse http://server:5555/XRMServices/2011/Discovery.svc also was getting error and detail of error is in event viewer as 

WebHost failed to process a request.
Sender Information: System.ServiceModel.Activation.HostedHttpRequestAsyncResult/65652305
Exception: System.ServiceModel.ServiceActivationException: The service '/xrmservices/2011/Discovery.svc' cannot be activated due to an exception during compilation. The exception message is: Security settings for this service require Windows Authentication but it is not enabled for the IIS application that hosts this service.. ---> System.NotSupportedException: Security settings for this service require Windows Authentication but it is not enabled for the IIS application that hosts this service.
at System.ServiceModel.Activation.HostedAspNetEnvironment.ValidateHttpSettings(String virtualPath, Boolean isMetadataListener, Boolean usingDefaultSpnList, AuthenticationSchemes& supportedSchemes, ExtendedProtectionPolicy& extendedProtectionPolicy, String& realm)
at System.ServiceModel.Channels.HttpChannelListener.ApplyHostedContext(String virtualPath, Boolean isMetadataListener)
at System.ServiceModel.Channels.HttpTransportBindingElement.BuildChannelListener[TChannel](BindingContext context)
at System.ServiceModel.Channels.Binding.BuildChannelListener[TChannel](Uri listenUriBaseAddress, String listenUriRelativeAddress, ListenUriMode listenUriMode, BindingParameterCollection parameters)
at System.ServiceModel.Description.DispatcherBuilder.MaybeCreateListener(Boolean actuallyCreate, Type[] supportedChannels, Binding binding, BindingParameterCollection parameters, Uri listenUriBaseAddress, String listenUriRelativeAddress, ListenUriMode listenUriMode, ServiceThrottle throttle, IChannelListener& result, Boolean supportContextSession)
at System.ServiceModel.Description.DispatcherBuilder.BuildChannelListener(StuffPerListenUriInfo stuff, ServiceHostBase serviceHost, Uri listenUri, ListenUriMode listenUriMode, Boolean supportContextSession, IChannelListener& result)
at System.ServiceModel.Description.DispatcherBuilder.InitializeServiceHost(ServiceDescription description, ServiceHostBase serviceHost)
at System.ServiceModel.ServiceHostBase.InitializeRuntime()
at System.ServiceModel.ServiceHostBase.OnOpen(TimeSpan timeout)
at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)
at System.ServiceModel.ServiceHostingEnvironment.HostingManager.ActivateService(String normalizedVirtualPath)
at System.ServiceModel.ServiceHostingEnvironment.HostingManager.EnsureServiceAvailable(String normalizedVirtualPath)
--- End of inner exception stack trace ---
at System.Runtime.AsyncResult.End[TAsyncResult](IAsyncResult result)
at System.ServiceModel.Activation.HostedHttpRequestAsyncResult.End(IAsyncResult result)
Process Name: w3wp
Process ID: 3648

--

When opened IIS manager Microsoft Dynamic CRM authentication was set to "Windows Authentication" but was still getting above error.

Resolution:

This was due to mistakenly someone removed "Negotiate" Provider from "Microsoft Dynamic CRM" website,

To add it back perform following steps

Start->Run-> Inetmgr -> Microsoft Dynamic CRM -> select authenticate and click open feature in action panel ->click windows authentication and click on providers in action panel-> select Negotiate from available providers dropdown list and click add, and move it to top.

Restart IIS 

A currency is required message prompt

On entity form a currency type field is placed. When record is opened and some number value is added in it, I got following error message.

---------------------------
Message from webpage
---------------------------
A currency is required if a value exists in a money field. Select a currency and try again.
---------------------------
OK  
---------------------------

Resolution:

Go to File -> Option. Under General Tab go to option "select a default currency" and then click on "Currency" lookup and specify currency to use.