Making a web request using powershell and reading response

## Sending a web request to a url and getting a response back.
$urlresponse = $null
$response = $null
[Net.ServicePointManager]::ServerCertificateValidationCallback = {$true}
$strURI = "https://www.bing.com"
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
$req = [Net.WebRequest]::CreateHttp($strURI)
$response = $req.GetResponse() ;
[System.IO.Stream]$datastream = $response.GetResponseStream()
$streamreader = New-Object  System.IO.StreamReader($datastream)
$urlresponse = $streamreader.ReadToEnd();
Write-Host $response.ProtocolVersion  -ForegroundColor Yellow
Write-Host $req.servicepoint.certificate.GetExpirationDateString()  -ForegroundColor Yellow
Write-host $req.ServicePoint.Certificate.GetIssuerName()  -ForegroundColor Yellow
## Write-Host $urlresponse -ForegroundColor Yellow ## Should be commented if you expect the response to be too large
$response.Close() | Out-Null;

SCOM agent not connecting. Events 20000,21016,20070

The OpsMgr Connector connected to MS.contoso.com, but the connection was closed immediately after authentication occurred.  The most likely cause of this error is that the agent is not authorized to communicate with the server, or the server has not received configuration.  Check the event log on the server for the presence of 20000 events, indicating that agents which are not approved are attempting to connect

OpsMgr was unable to set up a communications channel to MS.contoso.com and there are no failover hosts.  Communication will resume when MS.contoso.com is available and communication from this computer is allowed.

After all actions have failed like repair, reinstall, delete,approve again etc. The following steps should work. Use at you own risk and don't forget to backup your database.

Uninstall the agent from the computer completely first. Then do the following actions in sequence.

1. Delete pending agent if any

exec p_AgentPendingActionDeleteByAgentName ‘agentname.domain.com’

2. Delete
USE [OperationsManager]
UPDATE dbo.[BaseManagedEntity]
SET
[IsManaged] = 0,
[IsDeleted] = 1,
[LastModified] = getutcdate()
WHERE FullName like ‘%computername%’

3. Grooming

DECLARE @GroomingThresholdUTC datetime

SET @GroomingThresholdUTC = DATEADD(d,-2,GETUTCDATE())
UPDATE BaseManagedEntity
SET LastModified = @GroomingThresholdUTC
WHERE [IsDeleted] = 1
UPDATE Relationship
SET LastModified = @GroomingThresholdUTC
WHERE [IsDeleted] = 1
UPDATE TypedManagedEntity
SET LastModified = @GroomingThresholdUTC
WHERE [IsDeleted] = 1

EXEC p_DataPurging

4.  Groom all partition tables.

/*-------------------------------*/

declare @counter int  set @counter = 0  while @counter < 122

begin

exec p_PartitioningAndGrooming

set @counter = @counter + 1

print 'The counter is ' + cast(@counter as char)

end

/*-----------------------------*/

SQL connection failing SSPI Error

Error:

The target principal name is incorrect. Cannot generate SSPI context. (Microsoft SQL Server)

Steps to resolve this error.

Check if the spn' are registered properly. Lots of information out there on how to create and spn.

Check if kerberos is not blocked by a group policy.

Check if kerberos encryption being used in allowed.

Local security policy: "Network Security: Configure encryption types allowed for Kerberos"

Allowing users who are not part of Azure AD to access Azure SQL managed instance.

Here are the concise steps to achieve this.

1. Login to Azure portal and open Active Directory.
2. Create a guest user and invite to your organization.
3. Once the user has accepted this invitation he/she will show up in the Azure AD users blade.
4. Create a group in the Azure AD and add that user to that group.
5. Connect to the managed instance using SSMS and create the user group with name as same as in Azure AD

Using the following query.
USE master
GO
CREATE LOGIN [TestGroup] FROM EXTERNAL PROVIDER
GO
6. Give permissions to that group on the SQL managed instance using SQL as you would in SSMS.

Link is below.

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-managed-instance-aad-security-tutorial

URL Certificate expiration check using powershell

Using powershell to check for certificate expiration for a url.

Create a folder called C:\URLCertexpiry
add a text file in it named URLsToCheckforCertExpiry.txt. Add the url's you want to check for certificate expiration in this text file.


$ErrorActionPreference = "Stop"

Out-file -FilePath C:\URLCertExpiry\URLCERTExpiryLog.txt -InputObject "Starting Script at $(get-date)" -Append
try {Remove-Item -path C:\URLCertExpiry\URLCERTExpiryReport.txt -Force -ErrorAction Continue} catch {$_.exception}
$UrlList = @()
$Data = @()
$ExpiringCollection = @()
$ExpiredCollection = @()

$username = "username"
$password = "Password" | ConvertTo-SecureString -asPlainText -Force
$cred = New-Object System.Management.Automation.PSCredential($username,$password)
$UrlList = GC "C:\URLCertExpiry\URLsToCheckforCertExpiry.txt"
$TLS12urls = ("https://www.google.com",
"https://www.bing.com"
)
foreach ($url in $UrlList )
{
try{
$message = "Working on $url"
Write-host $message -ForegroundColor Yellow
Out-file -FilePath C:\URLCertExpiry\URLCERTExpiryLog.txt -InputObject $message -Append
$minimumCertAgeDays = 30
 $timeoutMilliseconds = 20000
#disabling the cert validation check. This is what makes this whole thing work with invalid certs...
 [Net.ServicePointManager]::ServerCertificateValidationCallback = {$true}
if($TLS12urls -contains $url)
{

 [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
}

else
{
 [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls
}

 Write-Host Checking $url -f Green
 $req = [Net.WebRequest]::Create($url)
 $req.Credentials = $cred
 $req.Timeout = $timeoutMilliseconds
try {
 $response = $req.GetResponse()
 $response.Close()
[datetime]$expiration = $req.ServicePoint.Certificate.GetExpirationDateString()
 [int]$certExpiresIn = ($expiration - $(get-date)).Days

 if($certExpiresIn -gt $minimumCertAgeDays)
 {
   $expirationState = "NotExpiring"
 }

 elseif($certExpiresIn -lt $minimumCertAgeDays -and $certExpiresIn -gt 0)
 {
   $expirationState = "Expiring"
$ExpiringCerts= $url + "        " +  $expirationState + "      Validity   "+ $certExpiresIn +" " + "days"
$ExpiringCollection+=$ExpiringCerts
 }

elseif($certExpiresIn -lt $minimumCertAgeDays -and $certExpiresIn -lt 0)
 {
   $expirationState = "Expired"
$ExpiredCerts= $url + "        " +  $expirationState + "      Validity   "+ $certExpiresIn +" " + "days"
$ExpiredCollection+=$ExpiredCerts
 }

 $Data1 = $url + "        " +  $expirationState + "      Validity   "+ $certExpiresIn +" " + "days"
  Write-Host $data1 -ForegroundColor Yellow
 $Data+=$Data1


}

catch {
$message =  "Exception while checking URL $url`: $_ "
Out-file -FilePath C:\URLCertExpiry\URLCERTExpiryLog.txt -InputObject $message -Append
continue
}
}

catch {
$Message = $_.exception.Message
Out-file -FilePath C:\URLCertExpiry\URLCERTExpiryLog.txt -InputObject $(get-date) -Append
Out-file -FilePath C:\URLCertExpiry\URLCERTExpiryLog.txt -InputObject $message -Append
Continue
}


}

Write-Host $data -ForegroundColor Yellow
Out-file -FilePath C:\URLCertExpiry\URLCERTExpiryReport.txt -InputObject $Data -Append
$Data
Out-file -FilePath C:\URLCertExpiry\URLCERTExpiryReport.txt -InputObject "Email Data" -Append
Out-file -FilePath C:\URLCertExpiry\URLCERTExpiryReport.txt -InputObject $ExpiringCollection -Append
$ExpiringCollection
Out-file -FilePath C:\URLCertExpiry\URLCERTExpiryReport.txt -InputObject $ExpiredCollection -Append
$ExpiredCollection

$ExpiringCollectionArray=$null
$ExpiredCollectionArray=$null
$ExpiringCollection | %{$ExpiringCollectionArray+=$_}
$ExpiredCollection | %{$ExpiredCollectionArray+=$_}

$ExpiringCollection | Select @{label='Expiring Certificates:';expression={$_}} | ConvertTo-HTML -Fragment -Property 'Expiring Certificates:' -As List | % { $_ -replace '<td>Expiring Certificates::</td>', ''} | % { $_ -replace '<tr><td><hr></td></tr>', '' } | Out-File C:\URLCertExpiry\report.html -append
$ExpiredCollection | Select @{label='Expired Certificates:';expression={$_}} | ConvertTo-HTML -Fragment -Property 'Expired Certificates:' -As List| % { $_ -replace '<td>Expired Certificates::</td>', '' } | % { $_ -replace '<tr><td><hr></td></tr>', '' }   | Out-File C:\URLCertExpiry\report.html -append


Out-file -FilePath C:\URLCertExpiry\URLCERTExpiryReport.txt -InputObject "Collection Values" -Append
Out-file -FilePath C:\URLCertExpiry\URLCERTExpiryReport.txt -InputObject $ExpiredCollectionArray -Append
Out-file -FilePath C:\URLCertExpiry\URLCERTExpiryReport.txt -InputObject $ExpiringCollectionArray -Append

Out-file -FilePath C:\URLCertExpiry\URLCERTExpiryLog.txt -InputObject "Ending Script at $(get-date)" -Append

$Data
$ExpiringCollectionArray
$ExpiredCollectionArray

Openssl convert pfx to key and cert file

Link to full list of commands is here.

https://stackoverflow.com/questions/13732826/convert-pem-to-crt-and-key

Convert pfx to cert without keys

openssl pkcs12 -in ServerName.pfx -clcerts -nokeys -out ServerName.crt

Convert pfx to pem. add -nokeys to only export cert. add -nocerts to only export keys

openssl pkcs12 -in ServerName.pfx -out ServerName.pem -nodes

Convert pem to cert

openssl x509 -outform der -in ServerName.pem -out ServerName-Cert.crt

RDP issues

Turn of NLA:

https://fixingitpro.com/2011/07/06/disabling-rdp-network-level-authentication-nla-remotely-via-the-registry/

Give network service account read permissions to the Keys folder.

https://serverfault.com/questions/541364/how-to-fix-rdp-on-windows-server-2012

https://support.microsoft.com/en-us/help/2001849/how-to-force-remote-desktop-services-on-windows-7-to-use-a-custom-serv

Make sure Remote Desktop Services(TermService) service is running under network service.

SQL spn and linked server double hop and kerberos

Good link to setup linked server and spn's

https://sqljana.wordpress.com/2017/06/16/sql-server-curse-of-linked-server-security-and-the-fix-pass-through-authentication/


https://www.alexandreviot.net/2014/09/30/sql-server-could-not-register-the-service-principal-name-spn/


Query to see if Kerberos is being used.

Collection of usefull excel formulas

This formula will give a day of the week with name from a cell

=CHOOSE(WEEKDAY(C2),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")

Use a matching value in a cell to get another cells value

If you use match formula. It gives you the cell number of the matching value found.










 

Adding a disk to Azure vm using powershell

Powershell Script to add a disk to Azure RM virtual machine.

$Credentials = Get-Credential
param([string] $VirtualMachineName,$DiskSize)

Login-AzureRmAccount -EnvironmentName AzureUSGovernment -Credential $Credentials

$Managed_Prod_Sub = Get-AzureRMSubscription -SubscriptionId "Your subscription id"
Select-AzureRMSubscription -SubscriptionId $Managed_Prod_Sub.SubscriptionId ##
$Managed_ProdVMS = Get-AzureRMVM


if($VM.name -eq $VirtualMachineName)
  {
  Write-host "Working on $($VM.name)" -ForegroundColor Yellow
     $rgName = $VM.ResourceGroupName
    $vmName = $VM.Name
    $location = $VM.location
    $storageType = 'Premium_LRS'
    $dataDiskName = $vmName + '_datadisk1'

   
    $diskConfig = New-AzureRmDiskConfig -AccountType PremiumLRS -Location $location -CreateOption Empty -DiskSizeGB $DiskSize -OsType Windows

    $dataDisk1 = New-AzureRmDisk -DiskName $dataDiskName -Disk $diskConfig -ResourceGroupName $rgName
   
    $vm = Get-AzureRmVM -Name $vmName -ResourceGroupName $rgName
    $vm = Add-AzureRmVMDataDisk -VM $vm -Name $dataDiskName -CreateOption Attach -ManagedDiskId $dataDisk1.Id -Lun 1

    Update-AzureRmVM -VM $vm -ResourceGroupName $rgName


   }

Good trick for using Try Catch in Powershell

    try{

 Something......

    }

    Catch{
$formatstring = "{0} : {1}`n{2}`n" +
                "    + CategoryInfo          : {3}`n" +
                "    + FullyQualifiedErrorId : {4}`n"
$fields = $_.InvocationInfo.MyCommand.Name,
          $_.ErrorDetails.Message,
          $_.InvocationInfo.PositionMessage,
          $_.CategoryInfo.ToString(),
          $_.FullyQualifiedErrorId

Out-File -FilePath 'C:\Temp\error.txt' -inputobject ($formatstring -f $fields) -Append
 
  }