List all advanced configuration options in MSSQL with sp_configure

Hello Everyone,

While writing the Technical document, we all would need to specify the configuration options details of the SQL Server which was Installed on Dynamics Server along with the version and the Instance Name.

 

The SQL configuration options can be fetched from the master table using the query:

Exec sp_configure

sp_configure_1

 

However, this is not all the options. To show all the advanced options need to run the following query:

1. EXEC sp_configure 'show advanced option', '1'

sp_configure_2

Upon successful query execution, the result will show the following message as in image above.

 

2. RECONFIGURE; 

sp_configure_3

 

Now when I execute the query sp_configure, I see many result rows.

sp_configure_4

 

Many other syntax and features about sp_configure are documented in MS article. Hope this will be helpful.

Thanks ! 😀

Advertisements

The cluster resource ‘SQL Server’ could not be brought online due to an error bringing the dependency resource ‘SQL Network Name’ online : Access is denied

Hello Everyone,

While I was installing the SQL Server 2016 Failover Cluster Instance on a first node of cluster DB, I faced the following permission error:

The cluster resource ‘SQL Server’ could not be brought online due to an error bringing the dependency resource ‘SQL Network Name’ online

 

SQL Error1

As suggested, to investigate on the detailed error list I opened the Cluster Events in the Failover Cluster Manager and found:

Error: Cluster network name resource failed to create its associated computer object in domain.

Error Code: Access is denied.

 

SQL Error2

 

Resolution:

The error occurred because the permission “Create Computer Object” and “Read all properties” has not been granted to the Cluster Name Object (CNO).

  1. Kindly follow the steps provided under Resolutions -> Option #1 in the MSDN article below:

https://blogs.msdn.microsoft.com/psssql/2013/09/30/error-during-installation-of-an-sql-server-failover-cluster-instance/

2. Also need to provide the account for person who install the cluster, the permissions “Create Computer Object” and “Read all properties” as mentioned in the document below:

https://docs.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2008-R2-and-2008/cc731002(v=ws.10)#to-configure-the-account-for-the-person-who-installs-the-cluster

 

Once the permissions were updated, I clicked on the Retry button and the Installation continued and completed successfully.

While browsing for issues I came across this detailed MS doc on the Event ID – 1194.  Hope this will be helpful to someone.

Thanks ! 😀

Host Sseparate Instance of Report Server Database on SQL Server 2016 Failover Cluster

Hello Everyone,

I setup an active-passive cluster DB for my recent Dynamics 365 Installation. Everything worked smoothly except for the Reporting Services because SSRS is not cluster aware.

 

My Experiences:

Testing purposes: I tried to Install the Reporting Services on existing node (node B) of a Cluster Instance, I then received the following error:

Cluster_SSRS_01

I read a couple of articles that explains to bypass this Issue by running the setup from cmd and adding the rule to bypass the validation check for StandaloneInstall_HasClusterOrPreparedInstanceCheck :

Setup.exe /SkipRules=StandaloneInstall_HasClusteredOrPreparedInstanceCheck /Action=Install

The same has been described in the couple of great blog posts here and here with detailed steps.

 

Using the above process I was able to bypass the first validation check. However, when I tried to use the same existing instance I encountered another error:

Instance name ‘XXXXXXXXXX’ is already in use. To continue, specify a unique instance name.

Cluster_SSRS_02

This left me with no other option but continue with the recommended approach.

 

 

The Recommended Approach:

The only way of doing this is to Install the separate instance of Reporting Server on another node and point it to the same reporting server DB using Reporting Services Configuration Manager. The same method has been described in the Microsoft Document and discussed in the SQLServer Forum.

After the Installation and configuration of Reporting Server on another node in cluser, use the scale out deployment to join both the instances on the different nodes together. During this process there might be need to Restore the Encryption key. I made sure to use the same Cluster Server Name during setup so that whenever there is a failover, it points to the correct node and that node SSRS starts working. Finally my reports under CRM were working without any errors after the Failover to alternative nodes.

 

Hope this will be helpful to someone.

Thanks ! 😀

 

The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel

Hello Everyone,

In my previous blog I settled my SSL Certificate issue and was able to bind it successfully with my CRM. To start with development,  we created a web-api and hosted it on same IIS as in CRM; it is supposed to consume the CRM webservice to do the data verification operation.

When I started the Unit testing, it threw an error: Unable to Login to Dynamics CRM

I cross checked all the credentials and everything seemed to be in place. To make sure the connection, using the same code I created a console app and tried to connect to CRM, and it was connected! 😮

This gave me goosebumps; my last resort my to enable tracing and check for errors. I enabled the tracing and found the following 2 errors:

 

======================================================================================================================
Inner Exception Level 1 : 
Source : System
Method : GetResponse
Date : 8/12/2018
Time : 8:01:53 AM
Error : The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel.
Stack Trace : at System.Net.HttpWebRequest.GetResponse()
at System.ServiceModel.Description.MetadataExchangeClient.MetadataLocationRetriever.DownloadMetadata(TimeoutHelper timeoutHelper)
at System.ServiceModel.Description.MetadataExchangeClient.MetadataRetriever.Retrieve(TimeoutHelper timeoutHelper)
======================================================================================================================
Inner Exception Level 2 : 
Source : System
Method : StartSendAuthResetSignal
Date : 8/12/2018
Time : 8:01:53 AM
Error : The remote certificate is invalid according to the validation procedure.
Stack Trace : at System.Net.Security.SslState.StartSendAuthResetSignal(ProtocolToken message, AsyncProtocolRequest asyncRequest, Exception exception)
at System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.ProcessReceivedBlob(Byte[] buffer, Int32 count, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.StartReceiveBlob(Byte[] buffer, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.ProcessReceivedBlob(Byte[] buffer, Int32 count, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.StartReceiveBlob(Byte[] buffer, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.ProcessReceivedBlob(Byte[] buffer, Int32 count, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.StartReceiveBlob(Byte[] buffer, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.ForceAuthentication(Boolean receiveFirst, Byte[] buffer, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.ProcessAuthentication(LazyAsyncResult lazyResult)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Net.TlsStream.ProcessAuthentication(LazyAsyncResult result)
at System.Net.TlsStream.Write(Byte[] buffer, Int32 offset, Int32 size)
at System.Net.ConnectStream.WriteHeaders(Boolean async)
======================================================================================================================

 

Now I knew the exact error; the hosted API was not able to maintain a trust connection with CRM because of the SSL certificate. I searched for an article to handle/ignore the SSL Certificate issue in my code and came acrosss this piece of code:

ServicePointManager.ServerCertificateValidationCallback = delegate (object s, X509Certificate certificate, X509Chain chain, SslPolicyErrors sslPolicyErrors) { return true; };

 

 

I added it just before I start the connection to CRM and viola, it worked this time! 😀

SSL_CRM_certSSL_CRM

 

 

While I was browsing through some articles, I found few articles which describe the issue in detail here and here; it’s worth reading them once for a better understanding of handling SSL cert in the code.

Hope this will be helpful. Thanks ! 🙂

 

SSL Certificate not visible in IIS : Cert is Missing Private Key !

Hello Everyone,

In development servers, I’m running Dynamics 365 on HTTPS. I generated the custom CSR from Microsoft Management Console: Certificates Snap-In. I passed over the CSR file to my CA (Certificate Authority) to generate the SSL Certificate for my CRM website. After I imported the SSL cert back to Certificates Snap-In: Microsoft Management Console,  I can’t seem to find it in my Certificates list under IIS to bind it to my CRM website! 😮

 

This was surprising, I tried to repeat the process a couple of time but all in vain. Quite perplexed to what’s happening, I googled it and found the issue in this MSDN article. In my case the all the points were correct and matching, except for the one; the Private key was missing!!! 

 

How to restore the Private Key in SSL Certificate ?

The is another utility (CertUtil) which can be used to re-generate/repair the private keys for the certificates.

  1. Start -> mmc.exe -> Add snap-in -> Certificates -> Computer account. Verify that the installed certificate appears in the “Personal/Certificates” tab. If not, import it. A missing private key is visualized by the icon next to the certificate not containing a key icon.
  2. Open the certificate (.cer) file from disk by double-clicking on it. In the Details tab, note the serial number.
  3. Start -> cmd.exe. Type “certutil -repairstore my (serialnumberhere)”. The serial number should have no spaces.

repairCert

After the Certutil: -repairstore command is completed, I double click the cert file and noticed the Key icon is visible now. I went back to IIS and check my certificates, it was there. Yyyyeeeeessssss!!!!! 😀

 

I proceed to bind my CRM HTTPS website with the cert and all looked good. The same has been shared my Microsoft in their Support Article in case you need step by step details.

 

Hope this will be helpful to someone. Many Thanks ! 😉

Vulnerability Assessment (VA) Scan : Windows Unquoted Search Path

Hi Everyone,

In my previous blog I talked about another VA scan : Disable HTTP OPTIONS. Here is another critical issue which was highlighted during the recent vulnerability assessment scan on my Dynamics servers : Windows Unquoted Search Path.

 

What is Windows Unquoted Search Path ?

Basically it is the path to an executable file under local Services that are unquoted and contain spaces. This can easily be exploited by placing a malicious file in between the path. This would run when the services starts as the services would be starting with the SYSTEM privilege.

 

In Windows there are 3 locations to look for the file path at:

  • HKLM\System\CurrentControlSet\Services
  • HKLM\Software\Microsoft\Windows\CurrentVersion\Uninstall
  • HKLM\Software\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall

 

For Dynamics 365, notice the service path for:

  • MSCRM Monitoring Service – Image Path key is Quoted
  • MSCRM Unzip Service – Image Path key is Quoted
  • MSCRM VSS Writer Service – Image Path key is not Quoted
  • Microsoft Help Viewer – Uninstall String is not Quoted

 

MSCRM services can be found at : HKLM\System\CurrentControlSet\Services\MSCRM…

VSSwriter_before

 

Microsoft Help Viewer can be found at : HKLM\Software\Microsoft\Windows\CurrentVersion\Uninstall\

MShelp_before

 

How to fix this unquoted paths?

The simplest way would be to update the registry settings directly, which is exactly what I did to fix these two paths in my dynamics servers.

Note: Kindly take a back up of the registry before you modify. This is to safeguard in case somethings goes wrong as updating a registry incorrectly may have serious impact on the system. 

 

After updating my registry settings for both paths:

  • MSCRM VSS Writer Service – Image Path key is Quoted
  • Microsoft Help Viewer – Uninstall String is Quoted

VSSwriter_after

MShelp_after

 

The same has been explained in much detail with better examples here, it’s worth a reading. For other cases where this needs to be fixed on large scale with multiple systems involved, it would be tough to change the registry manually one by one. Kindly follow this blog where the steps to run scripts are explained in detail or run this Powershell script directly.

 

Hope this would be helpful to someone.

Thanks ! 😀

Vulnerability Assessment (VA) Scan : HTTP Options Method Enabled

Hi Everyone,

As per best security practices, our CRM servers recently went through the vulnerability assessment scan. One of the critical Issue highlighted was : HTTP Options Method Enabled.

 

What is HTTP OPTIONS ?

The OPTIONS method provides a list of methods that are supported by the web server. Although this might seems beneficial sometimes, but it also provides useful information to an attacker. Hence it is recommended to disable the OPTIONS method.

 

How to Disable the HTTP OPTIONS?

HTTP OPTIONS can be disabled by denying the verb “OPTIONS” from the request filtering rules (HTTP Verbs Tab) in IIS.

a. Open IIS manager

b. Select the Website: Microsoft Dynamics CRM

c. Select Request Filtering option

d. Choose the HTTP Verbs tab

 

You might notice the following verbs:

  • OPTIONS = True
  • TRACE = False

HTTP_Options5

 

e. Select the OPTIONS verb and click the button Remove from Actions pane.

f. Following that under the same Action pane, click Deny Verb..

g. Set it to OPTIONS

HTTP_Options6

 

There are few other articles here and here which are worth reading with some additional information on securing the web servers.

 

Hope this would be helpful!

Thanks ! 😀

 

Delete the specific Report Server Instance using RSKeyMgmt

Hi Everyone,

Last month, for some testing purposes I added another Instance of Report Server (Dynamics365_SSRS) pointing to my existing one (MSSQLServer). After I finished all the testing, I wish to remove the additional Report Server Instance. So I proceed to uninstall the additional Instance; all went well and I restarted the server to make sure all the related files and connections are refreshed. To double confirm, I opened the Reporting Services Configuration Manager to make sure the Report Instance is removed but I noticed the Instance (Dynamics365_SSRS) was still there waiting to be joined under Scale-out Deployment section.

My Report Server and Report Manager weren’t working anymore, I checked the SSRS logs and found the following error:

Microsoft.ReportingServices.Portal.WebHost!crypto!1!09/04/2018-11:56:27:: i INFO: Exporting public key
Microsoft.ReportingServices.Portal.WebHost!crypto!1!09/04/2018-11:56:27:: i INFO: Performing sku validation : Scale-Out
Microsoft.ReportingServices.Portal.WebHost!crypto!1!09/04/2018-11:56:27:: i INFO: NT Service not activated: can be added to scale out group with config tool
Microsoft.ReportingServices.Portal.WebHost!library!1!09/04/2018-11:56:27:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.CannotValidateEncryptedDataException: , Microsoft.ReportingServices.Diagnostics.Utilities.CannotValidateEncryptedDataException: The report server was unable to validate the integrity of encrypted data in the database.;

 

From the error message, I understood I might also need to re-encrypt all contents using a new Encryption Key (as I didn’t store my previous encryption keys as well 😦 ) after I successfully remove the additional Instance.  I then read about some articles and found this document from Microsoft on RSKeyMgmt. This utility helps to extract, restore, create and deletes the symmetric key used to protect sensitive report server data against unauthorized access. Also it helps to join/remove report server instances in a scale-out deployment.

RSKeyMgmt_1

 

 

I’m using SQL Server 2016 and I found this tool under: <Drive:>\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\RSKeyMgmt

As per the article I shared above, you would need to be the local administrator of the computer where the Instance is installed as this utility doesn’t work to manage the encryption keys of remote instances.  So to run this utility I open the command prompt using the administrator and browse to it’s location path and ran the following Command.

RSKeyMgmt -l  -> To list the announced servers in the report server database

I found 2 servers as a result:

  1. MSSQLSERVER
  2. DYNAMICS365_SSRS

 

RSKeyMgmt -r  -> To remove a specific installation from a scale out deployment.

RSKeyMgmt

 

After this the additional Instance (Dynamics365_SSRS) was finally removed from the scale-out deployment section under Reporting Services configuration manager.

 

Later I ran the following code:

RSKeyMgmt -r  -> To re-encrypt the secure information using a new Encryption Key

 

I refreshed my Report Server and my Report Manager and I finally had those pages back for browsing.

 

Hope this would he helpful to someone. Thanks ! 🙂

 

 

Dynamics 365: Case form got corrupted and couldn’t open after Publish Customization.

Tags

, , , ,

Hello Everyone,

 

Today would like to share a scenario which literally froze me for a minute while customizing Dynamics 365. Recently we were working on the Case form and suddenly after publishing customization, the Case form couldn’t open anymore!!! Thinking that the newly created fields on the forms would be the culprit, tried to open the case form editor from customization area but that also failed. The page din’t load at all, this was the first time I ever faced this scenario. 😮

Having no clue at all as no event logs were logged on servers, I searched if there had been a single same scenario faced by anyone. Luckily I found one in the Microsoft community here discussing exactly the same issue.

I followed the suggested answer to remove the control with placeholder, having the thought that we were too using Chrome while customizing the Case forms.

 

Solution:

  1. Create an unmanaged solution with the bugged form.
  2. Export the solution.
  3. Unzip the archive and edit the “customization.xml” file to remove all “control” tags containing “placeholder”.
  4. Create a new zip archive with the new “customization.xml” file.
  5. Import it to the CRM and publish all the customizations.

 

We opened our customization file and did find few entries with the Placeholder control.placeholder

I followed the above mentioned steps and bingo, the case form reopened !!! Had a big relief and a sense of victory… 😀

Hope this will help someone caught in a similar issue. Thanks and have a great day!

Add Validation or Custom Script to OOB Buttons on CRM forms

Tags

, , , , , , ,

Hello Everyone,

 

Recently I had a requirement to add in a validation logic before I Close the Opportunity as Won or Lost. The logic was simply i.e. to make sure the Est Close Date is not left empty; rather set to a date value before closure for better reporting and analysis purpose.

The best and recommended approach to do this is hide the OOB button and Add the same functionality to a new button with your embedded validation script. To hide and copy the OOB button functionality I used Ribbon Workbench. I would recommend to watch all the video series posted by Scott Durow himself on Ribbon Workbench here before proceeding to have a clear understanding of the tool.

 

To start with, first I would create a new Solution which will have only the Opportunity Entity and the new WebResource. This is required for the Ribbon Workbench to populate only the opportunity ribbons to edit.RibbonWorkbench3-2

 

Next, open the new XRMToolBox and connect to the Ribbon Workbench 2016.RibbonWorkbench1-2

 

Create a new connection to your Organization. RibbonWorkbench2-2

 

Once the connection to your CRM Organization is successful, select the newly created Solution and click OK.RibbonWorkbench4-2

 

 

The Ribbon Workbench Tool will download the solution and open all the opportunity ribbons in their own format to be edited. RibbonWorkbench5-2

 

Since we need to apply the validation on the OOB buttons Close as Won and Close as Lost. Hence we first make a copy of the OOB button so we don’t touch and keep the original customization as a backup. Note that the OOB button properties are grayed out. RibbonWorkbench6-2

 

After copying the OOB button, right click the same group and select Paste. RibbonWorkbench7-2

 

A new button with the same name but a different ID would be created. Notice that this new button Control properties are not grayed-out and open for editing. RibbonWorkbench8-2

 

Just now only the button properties are editable. To update the commands section, right click on the same button and click on ‘Customise CommandRibbonWorkbench9-2

 

This will open the Command properties to be edited. Since the commands ID is still the same for the new button hence we would take a copy and work on it; without touching the original else the OOB buttons commands will also be updated. RibbonWorkbench10-2

 

Likewise, paste the same under the command section.RibbonWorkbench11-2

 

Notice that a new command with a different ID would be created. You can Update the ID of the command if you wish to more meaningful.RibbonWorkbench12-2

 

Browse to the new button and update the command to the latest copied one. RibbonWorkbench13-2

 

Also update the Command Core properties. RibbonWorkbench14-2

 

Once updated, now we can proceed to change the copied command attached to the new button now. Browse to the command, notice that a Custom Javascript Action is present with Library, Function Name and a Boolean Parameter.  Take of note of these as we would need this back again in our custom webresource later. RibbonWorkbench15-2

 

Since we need to attach our own webresource, hence we would delete the already existing one by right click and Delete on the Action Tab.RibbonWorkbench16-2

 

Now click on Add Actions and select the option Javascript Action.RibbonWorkbench17-2

 

Search for your newly created webresource and add in the function name. For now this function only has the alert to make sure the function is called on the new button click.RibbonWorkbench18-2

 

Once Done, Publish the changes. RibbonWorkbench19-2

 

After publishing, refresh your CRM Organization and Open the Opportunity. Notice the 2 Close as Won buttons on the ribbon (One copied and another OOB). Click on the copied button and my alert works! 😀 RibbonWorkbench21-2

 

 

Now to make sure the OOB button functionality is not updated and remains the same, click on it and the Close Opportunity window will pop-up. RibbonWorkbench22-2

 

Once the button functionalities are verified for both the copied and OOB buttons. Now it’s time to update the webresource with the exact requirement.

Requirement:- If the Est Close Date field is blank then a confirmation Dialog should appear asking the user to update the Est Close Date as this field cannot be left blank. If the User select OK then apply the focus to the field so that user can update the Est Close Date. Else if the user selects Cancel then update the field by default to the current date and pop up the Close Opportunity window to continue. To restore the OOB functionality we would call the same Function Name with the defined patameter we noted earlier. 😀

My code as follows:

function OppCloseDate() {

var CloseDate= Xrm.Page.data.entity.attributes.get(“estimatedclosedate”);

if (CloseDate.getValue() == null)
{
Xrm.Utility.confirmDialog(“Est. Close Date of Opportunity cannot be Blank. Update the Close date?”,
function() {
Xrm.Page.ui.controls.get(“estimatedclosedate”).setFocus(); //Set the focus on Est Close Date Field
} ,
function() {
var TodaysDate= new Date(); // Get Today’s current date
Xrm.Page.getAttribute(“estimatedclosedate”).setValue(TodaysDate); // Update the Close Date to Today’s Date
Mscrm.OpportunityCommandActions.opportunityClose(true); // Call the OOB method, Mark Opp as Won
}
);
}

else {
Mscrm.OpportunityCommandActions.opportunityClose(true); // Call the OOB method, Mark Opp as Won
}
}

 

After Publishing the changes to the webresource, will test my jScript functionality. (M having goosebumps, hopefully would run without errors! ) Reopen the Opportunity record and click on the copied button for the new script to run.

Since the Est Close Date is empty, the confirmation Dialog box will appear. As per the script logic all works well.RibbonWorkbench25-2

 

If select on the Cancel Button, then notice the date in the form field will be updated and Close Opportunity will pop-up, just following the OOB button behavior. RibbonWorkbench26-2

 

Once tested the functionality is working all fine. Now we can proceed to hide the OOB button. Right click on the button and select the option to hide.RibbonWorkbench27-2

 

Once hidden the red cross mark will appear on the button and the Hide Action section will be updated with the record under Solution Elements.RibbonWorkbench28-2

Update the same steps for the Close as Lost and all good to go!

Hope that would be helpful to someone. Thanks and have a great day! 🙂