SQL – The target principal name is incorrect. Cannot generate SSPI context.

Recently in one of my Virtual Machine (VM)s I received the above error message when trying to connect to a SQL Server which I used to connect on other times.

After having a look I found that the trust between my virtual machine and the domain was broken. You can find the status of the secure channel by using the PowerShell command Test-ComputerSecureChannel.

This will also be evident when you try to login to the computer using a domain account. It will generate the following message.

To fix this you can try using the below methods.

1. PowerShell

Use the command Test-ComputerSecureChannel.

If your current login has the required access in the domain you can use the below command.

Test-ComputerSecureChannel –Repair

If you need to use another account than the current logged user then you need to use the –Credential parameter when calling the command.

Test-ComputerSecureChannel –Repair –Credential MyDomain\MyUser

2. Joining the domain again.

This will also get fixed by removing the machine from the domain and adding it back. Before removing the computer from the domain make sure you have access to a local administrator account on the computer. Otherwise you will not have a way to login to the computer.

This can be achieved by going to computer system properties,

1. removing the computer from the current domain,
2. restarting the computer.
3. adding the computer to the domain again
4. restarting the computer

To avoid the two restarts you can try using the following PowerShell commands.

$myPC = Get-WmiObject Win32_ComputerSystem
$myPC.UnjoinDomainOrWorkGroup("Account Password", "Account Username”, 0)
$myPC.JoinDomainOrWorkGroup("Domain", "Account Password", "Account Username", $null, 3)
Restart-Computer -Force

Monitoring Windows Services using PowerShell

Recently I was interested in finding a way to monitor a windows service. What I needed was to check whether the service is running and if not running get a notification and try to restart the service. Following PowerShell script does exactly that, it checks for Microsoft CRM Asynchronous Service and the Microsoft CRM Asynchronous Maintenance Service activity and send 2 emails to Admin and Dev. This needs to be then scheduled using Windows Task Scheduler or SQL Server Job.

### Checking for CRM Async and Maintenance service failure and try restarting, if failing send an email notification.

 

## Function to send mail notification.

function Send_Email ([string]$strEmailSubject, [string]$strEmailBody)

{

       $EmailFrom = "Arjuna@Email.com"

       $EmailTo = "Admin@Email.com, Dev@Email.com"

       $EmailSubject = $strEmailSubject

       $EmailBody = $strEmailBody

       $EmailSMTPServer = "SMTP.server.com"

       ## Creating Mail Message object.

       $SMTPMessage = New-Object System.Net.Mail.MailMessage $EmailFrom, $EmailTo, $EmailSubject, $EmailBody

       ## Enabling HTML mail body.

       $SMTPMessage.IsBodyHtml = $true

       ## Creating SMTP client object.

       $SMTPClient = New-Object System.Net.Mail.SMTPClient $EmailSMTPServer

       ## Sending mail.

       $SMTPClient.Send($SMTPMessage)

       ## Sending mail method 2.

       ##send-mailmessage -from "Arjuna@Email.com" -to "Admin@Email.com, Dev@Email.com" -subject "CRM Async Service Failed" -body "Please check." -smtpserver "SMTP.server.com"

       ## Sending mail method 2 using parameters.

       ##send-mailmessage -from $EmailFrom -to $EmailTo -subject $EmailSubject -body $EmailBody -smtpserver $EmailSMTPServer

}

 

## Function to check the service activity.

function Check_Service

{

       ## Get all services which has a Name like MSCRMAsyncService, Start Mode is Auto and service State is Running.

       $FailedAsyncService = Get-WmiObject Win32_Service | Where-Object {$_.Name -like 'MSCRMAsyncService' -and $_.StartMode -eq 'Auto' -and $_.State -ne 'Running'} | Select-Object DisplayName

       ## For Testing.

       ##Write-Host "A: " $FailedAsyncService

 

       ## Get all services which has a Name like MSCRMAsyncService$maintenance, Start Mode is Auto and service State is Running.

       $FailedAsyncMainteService = Get-WmiObject Win32_Service | Where-Object {$_.Name -like 'MSCRMAsyncService$maintenance' -and $_.StartMode -eq 'Auto' -and $_.State -ne 'Running'} | Select-Object DisplayName

       ## For Testing.

       ##Write-Host "B: " $FailedAsyncMainteService

 

       ## Checking whether the Async Service has failed.

       if ($FailedAsyncService -ne $NULL)

       {

              ## Trying to start the failed Async Service.

              Start-Service -displayname "Microsoft Dynamics CRM Asynchronous Processing Service"

              ## Service Name can also be used to start the servie.

              ##Start-Service MSCRMAsyncService

              ## Get all services which has a Name like MSCRMAsyncService, Start Mode is Auto and service State is Running.

              $AsyncServiceStarted = Get-WmiObject Win32_Service | Where-Object {$_.Name -like 'MSCRMAsyncService' -and $_.StartMode -eq 'Auto' -and $_.State -ne 'Running'} | Select-Object Name

              ## Checking the service to see whether it started.

              if ($AsyncServiceStarted -ne $NULL)

              {

                     ## Calling Send_Mail function to notify.

                     Send_Email ("CRM Async Service Failed.") ("System has detected that the following CRM Async Service has failed. System automatically tried restarting the service but it was unsuccessful. Try manual start. <BR/><BR/>" + $FailedAsyncService)

              }

              else

              {

                     ## Calling Send_Mail function to notify.

                     Send_Email ("CRM Async Service Restarted.") ("System has detected that the following CRM Async Service has failed. System automatically tried restarting the service and it was successful. <BR/><BR/>" + $FailedAsyncService)

              }

       }

 

       ## Checking whether the Async Maintenance Service has failed.

       if ($FailedAsyncMainteService -ne $NULL)

       {

              ## Trying to start the failed Async Maintenance Service.

              Start-Service -displayname "Microsoft Dynamics CRM Asynchronous Processing Service (maintenance)"

              ## Get all services which has a Name like MSCRMAsyncService$maintenance, Start Mode is Auto and service State is Running.

              $AsyncMainteServiceStarted = Get-WmiObject Win32_Service | Where-Object {$_.Name -like 'MSCRMAsyncService$maintenance' -and $_.StartMode -eq 'Auto' -and $_.State -ne 'Running'} | Select-Object Name

              ## Checking the service to see whether it started.

              if ($AsyncMainteServiceStarted -ne $NULL)

              {

                     ## Calling Send_Mail function to notify.

                     Send_Email ("CRM Async Service Failed.") ("System has detected that the following CRM Async Service has failed. System automatically tried restarting the service but it was unsuccessful. Try manual start. <BR/><BR/>" + $FailedAsyncMainteService)

              }

              else

              {

                     ## Calling Send_Mail function to notify.

                     Send_Email ("CRM Async Service Restarted.") ("System has detected that the following CRM Async Service has failed. System automatically tried restarting the service and it was successful. <BR/><BR/>" + $FailedAsyncMainteService)

              }

       }

}

 

## Calling the Check_Service function.

Check_Service

Changing Registry Key Values using PowerShell

As you may be knowing, you can use Windows PowerShell to change registry values. In this article I am going to do five things.

I have created few registry entries to use in this example as seen below. In real world you can use whatever entries in your registry. It is always advisable to backup your registry before changing it.

1. Set a registry key value.

To set a value you need to use the “Set-ItemProperty” cmdlet as below.

Set-ItemProperty -Path "HKLM:\Software\Test\Live" -Name "TestValue2" –Value “TestData2”

Above command will put “TestData2” in the registry key “TestValue2” located in HKEY_LOCAL_MACHINE\Software\Test\Live.

 

2. Read a registry key value.

Reading from the registry can be done by using the cmdlet “Get-ItemProperty”.

Below command will get the value in the “TestValue1” key.

Get-ItemProperty -Path "HKLM:\Software\Test\Live" -Name "TestValue1"

 

3. Using variables in PowerShell.

Here I am going to read a registry key value and put it to another registry key. This can be done using a variable. First you need to read the value into a variable using the “Get-ItemProperty” cmdlet and that value can be saved using the “Set-ItemProperty” cmdlet.

1. # Check for the existance of the registry key.
2. IF (Get-ItemProperty -Path "HKLM:\Software\Test\Live" -Name "TestValue1" -ea 0)
3. {
4.     # Fetching the value from TestValue1.
5.     $OldValue = Get-ItemProperty -Path "HKLM:\Software\Test\Live" -Name "TestValue1"
6. }
7. ELSE
8. {
9. # Inserting a blank, if the registry key is not present.
10.     $OldValue = ""
11. }
13. # Printing the value in the variable.
14. Write-Host $OldValue.TestValue1
16. # Setting the value to TestValue2.
17. Set-ItemProperty -Path "HKLM:\Software\Test\Live" -Name "TestValue2" -Value $OldValue.TestValue1

4. Working with registry keys with spaces.

In case your registry keys contain spaces, you need to use double quotes in your script as seen below.

1. # Check for the existance of the registry key.
2. IF (Get-ItemProperty -Path "HKLM:\Software\Test\Live" -Name "Test Value 1" -ea 0)
3. {
4.     # Fetching the value from Test Value 1.
5.     $OldValue = Get-ItemProperty -Path "HKLM:\Software\Test\Live" -Name "Test Value 1"
6. }
7. ELSE
8. {
9.     # Inserting a blank, if the registry key is not present.
10.     $OldValue = ""
11. }
13. # Printing the value in the variable.
14. Write-Host $OldValue."Test Value 1"
16. # Setting the value to Test Value 2.
17. Set-ItemProperty -Path "HKLM:\Software\Test\Live" -Name "Test Value 2" -Value $OldValue."Test Value 1"

 

5. Saving PowerShell commands as scripts and running them.

Both above can be saved as a PowerShell script by saving it in a file with the extension ps1. For example I did save it as “ChangeReg.ps1” in my C drive inside the folder “new”. Then the script can be run by browsing to the folder and using the command “.\ChangeReg.ps1”.

After the script is run my registry keys looked like this.

In case you need to retrieve values from other registry hives (locations), following table may be helpful.

	Registry Hive	Abbreviation
1.	HKEY_CLASSES_ROOT	HKCR
2.	HKEY_CURRENT-USER	HKCU
3.	HKEY_LOCAL_MACHINE	HKLM
4.	HKEY_USERS	HKU
5.	HKEY_CURRENT_CONFIG	HKCC

 

In case you need to read more on “Get-ItemProperty” and “Set-ItemProperty”, use the links to visit official documentation from Microsoft TechNet.