Friday, April 20, 2018

No Microsoft Dynamics CRM user exists with the specified domain name and user ID


A Microsoft Dynamics CRM user record does not exist with the specified domain name and user ID.

Crm Exception: Message: No Microsoft Dynamics CRM user exists with the specified domain name and user ID, ErrorCode: -2147220652

 Error while retrieving userId. Exception: Microsoft.Crm.CrmSecurityException: Unable to find Active Directory object for SID
   at Microsoft.Crm.SecurityUtils.GetGuidFromSidLimitingSearches(String domain, Byte[] sid)
   at Microsoft.Crm.SecurityUtils.GetUserId(String domainName, Boolean limitGlobalCatalogSearches)

We were getting such error for only certain users after we migrated our CRM organization from CRM 2011 to CRM 2016.

Initial Investigation and results.
  • User was enabled.
  • User has all security roles assigned.
  • Even one of Service Account having System Administrative access was not able to login.

Understanding CRM Tables

There are four main tables where CRM user detail must exist for CRM authentication.

MSCRM_CONFIG.dbo.SystemUserOrganizations
MSCRM_CONFIG.dbo.SystemUserAuthentication
MSCRM_CONFIG.dbo.SystemUser
YourOrgName_MSCRM.dbo.SystemUserBase

Here is a query which should give an outcome with important details for a given CRM User.

select DomainName,ActiveDirectoryGuid,AuthInfo,A.UserId
from MSCRM_CONFIG.dbo.SystemUserOrganizations O
join MSCRM_CONFIG.dbo.SystemUserAuthentication A on A.UserId=O.UserId
join Staging.dbo.SystemUserBase B on B.SystemUserId=O.CrmUserId
Where B.DomainName like '%itsit\vipin.jaiswal%'


If you are not getting SQL Result, then it might be possible that there is missing entry for a CRM user in one of the table.

So, we need to verify in which table we are missing the respective crm user entry and verification must be done in specific order as described here.

First :
Please note SystemUserID of the respective user as this would be an input for our second query.

Select DomainName,SystemUserID
From Staging_MSCRM.dbo.SystemUserBase
Where DomainName like '%itsit\vipin.jaiswal%'



Second :
The SystemUserId from above is an input to below query in where clause.

Select CrmUserId,Id,OrganizationId,UserId,IsDeleted
From MSCRM_CONFIG.dbo.SystemUserOrganizations
Where CrmUserId = 'F2B56B91-CE43-E811-9103-005056A83905'



Note the UserId from above it will be an input to our Third and Fourth Query.

Third and Fourth :

Select AuthInfo,Id,UserId,IsDeleted
From MSCRM_CONFIG.dbo.SystemUserAuthentication
Where UserId = 'F9B56B91-CE43-E811-9103-005056A83905'

Select DefaultOrganizationId,Id,IsDeleted
From MSCRM_CONFIG.dbo.SystemUser
Where Id = 'F9B56B91-CE43-E811-9103-005056A83905'




In most cases we have got an entry in table YourOrgName_MSCRM.dbo.SystemUserBase and rest other 3 tables were missing the entry.

I have created a stored Procedure which will create missing entry in the respective table accordingly.

Disclaimer: As always, any direct changes in the CRM tables are unsupported.

Please Note:

SP require an Active Directory ID of  a CRM User and I know two ways to get it.

     1) Get the ID from your any other existing organization  from below query.
Select AuthInfo,Id,UserId,IsDeleted
From MSCRM_CONFIG.dbo.SystemUserAuthentication
Where UserId = @CrmUserConfigId

      2) Get the ID from running power shell command

Get-ADUser -Filter {SamAccountName -eq 'YourUserName'}



-------------Stored Procedure---------------


Create Procedure usp_CreateCrmUser
(@OrgName varchar(100), 
 @CrmUserDomainName varchar(100), 
 @CrmUserACtiveDirectoryID varchar(100))
As

/* 
----Run this Stored Procedure as

Exec CreateCrmUser 'Staging_MSCRM','itsit\Vipin.Jaiswal','W:S-1-5-21-1328376081-1279679187-339368940-342572' 
*/

Begin

Declare @OrganizationID uniqueidentifier
Declare @CrmUserConfigID uniqueidentifier
Declare @SystemUserID uniqueidentifier

Select @OrganizationID = Id
From MSCRM_Config.Dbo.Organization Where DatabaseName = @OrgName

Select @CrmUserConfigID = NEWID()

Select @SystemUserID = SystemUserId
From Staging_MSCRM.dbo.SystemUserBase Where DomainName = @CrmUserName

       If @OrganizationID IS NULL
       Begin
              Select [Output] = 'Organization Name  ''' + @OrgName + '''  does not exists'
              return
       End

       If @SystemUserID IS NULL
       Begin
       Select [Output] = 'User  ''' + @CrmUserName + '''  does not exists in Organization : '' ' + @OrgName + ''''
       return
       End
      
       Insert into MSCRM_CONFIG.dbo.[SystemUser] (DefaultOrganizationId,Id,IsDisabled,Name,IsDeleted)
       Values (@OrganizationID,@CrmUserConfigID,null,null,0)

       Insert into MSCRM_CONFIG.dbo.SystemUserOrganizations (CrmUserId, Id, OrganizationId, UserId, IsDeleted)
       Values (@SystemUserID, NEWID(), @OrganizationID, @CrmUserConfigID, 0)

       Insert into MSCRM_CONFIG.dbo.SystemUserAuthentication (AuthInfo,Id,UserId,IsDeleted)
       Values (@CrmUserACtiveDirectoryID, NEWID(), @CrmUserConfigID, 0)    
             
    Select [Output] = 'User  ''' + @CrmUserName + '''  Created for Organization : '' ' + @OrgName + ''''

       Select DomainName,ActiveDirectoryGuid,AuthInfo,A.UserId
       From MSCRM_CONFIG.dbo.SystemUserOrganizations O
       join MSCRM_CONFIG.dbo.SystemUserAuthentication A on A.UserId=O.UserId
       join Staging_MSCRM.dbo.SystemUserBase B on B.SystemUserId=O.CrmUserId
       Where B.DomainName = @CrmUserName

End

Invoking a stored procedure entered the appropriate missing entry in SQL table and users were able to log-on.

Thanks,
Vipin Jaiswal
vipinjaiswal12@gmail.com


Microsoft Dynamic CRM v9.0 and TLS and SSL protocols.


I was trying to log-on to Dynamic 365 CRM v9.0 from Internet Explorer and getting below error page.



This is because Microsoft is enforcing TLS 1.2 protocol when accessing Dynamics 365 v9.0
As this error message clearly mentioned that one is to edit Internet explorer setting.

I am explaining what exactly needed to be done.
1)  Open the Internet Explorer setting. Go to Tools > Internet Options.
2)  Go to Tab Advanced and scroll down and look for heading Security.


 3)  Under heading Security, look for Use TLS 1.2, check the box as depicted in below image.



Try accessing CRM and I hope without any further error, CRM will be accessible.

From a coding perspective if any application is trying to access Dynamic 365 v9.0 we need to invoke below commands to ensure that application is making use of TLS protocol 1.2.

ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12;


Other blogs for more details references are 


https://community.dynamics.com/crm/b/abhinavranjandynamics365/archive/2018/01/31/follow-up-on-tls-1-2-issue-in-dynamics-365



I hope this will save a lot of time.

Thanks,
Vipin Jaiswal

Friday, March 16, 2018

SQL Browser Service and Microsoft Dynamic CRM On-Premise


Recently one of my Client asked to provide remediation for different vulnerability issues for various hosted applications on different Internet facing servers.
 
Microsoft Dynamic CRM was one of the hosted application too and it so happens that few of the action steps which needs to be taken as a remediation for vulnerabilities issues has impacted CRM and we were forced to modify configuration in Microsoft Dynamic CRM web and associated database to bring CRM up and running and keeping its protected from any unknown attack.

I would like to share my learning with you all today!

Let’s understand SQL Browser Service and Issue associated

SQL browser service helps in providing information related to SQL Server instances installed on the computer.

It listens on port 1434/udp and accepts unauthenticated requests by using SQL Server Resolution Protocol (SSRP). When this feature is enabled and publicly accessible from the Internet, attackers may use this service to launch denial of service attacks (amplification attacks using forged UDP packets).

So, Network Team decided to turn OFF SQL browser service and we need to look for some alternate approach to have CRM accessible.

It is possible to install Dynamic CRM On-premises using SQL Server named instance?

Answer is YES, we can install CRM on SQL Server named instance. 

When I looked over internet, I was getting just to turn on SQL browser service to resolve named instance (without its hidden impact). I decided to play around on an extra CRM slot that we use for our RnD.

The Challenges

The moment you turn off SQL browser service and do any of below actions, CRM will not be accessible.
  • Recycle CRM Application Pool
  • Re-Start IIS (Internet Information Services) 
  • Re-Start CRM Application Server

Microsoft CRM Application without SQL Browser Service

We need to provide SQL port number to CRM, so it correctly points SQL Server Named instance during any of these actions:
  • Fresh CRM Installations – from CRM installation wizard.
  • Importing Existing Organization – from deployment manager.
  • Creating a New Organization – from deployment manager


So syntax would be     :           SQLSERVER\InstanceName,PortNumber

An Example                  :           CLUB-VIPINJA01\NO15,1433

Note: After forward slash (\) its a SQL instance name and after comma (,) its a SQL Port



Resolution steps when CRM is already installed

      1) Changes in the Registry of CRM Application Server – (regedit)

The Key path that need  to be changed
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSCRM\configdb

OLD Value
Data Source= CLUB-VIPINJA01;Initial Catalog=MSCRM_CONFIG;Connection Timeout=60;Integrated Security=SSPI

NEW Value
Data Source= CLUB-VIPINJA01\NO15,1433;Initial Catalog=MSCRM_CONFIG;Connection Timeout=60;Integrated Security=SSPI

2) Changes in SQL Server DB

Select * From MSCRM_CONFIG.dbo.Organization

Output
Provider=SQLOLEDB;Data Source=NUEW-SQEKWNP01;Initial Catalog=Production_MSCRM;Integrated Security=SSPI

Update the Connection string using SQL query.

Update Organization
Set ConnectionString = 'Provider=SQLOLEDB;Data Source=CLUB-VIPINJA01\NO15,1433;Initial Catalog=Production_MSCRM;Integrated Security=SSPI'

We can verify if CRM web is still accessible even after restarting IIS or recycling the CRM application pool.



How to check the SQL Port Number of our SQL Server installed.





I hope information provided here is valuable.

Thanks.