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