There are time we might need a certificate, for example, to set up Azure Service Principle without using secret. We can use PowerShell or OpenSSL to generate self-signed certificate.
# 1. Config
$certname = "SQLDataExportCert"
# password for secure the private key file
# use single quote to quote the string, to handle special character like $
$pwdstring = 'YourSecurePassword'
# Define the expiration date (e.g., 5 years from today)
$expiryDate = (Get-Date).AddYears(5)
$filepath = "C:\temp"
$certpath = "$filepath\$certname.cer"
$pfxpath = "$filepath\$certname.pfx"
$pempath = "$filepath\$certname.pem"
$pwd = ConvertTo-SecureString -String $pwdstring -Force -AsPlainText
#2. Create the certificate in your local store
$cert = New-SelfSignedCertificate -Subject "CN=$certname" `
-CertStoreLocation "Cert:\LocalMachine\My" `
-KeyExportPolicy Exportable `
-KeySpec Signature `
-KeyLength 2048 `
-KeyAlgorithm RSA `
-HashAlgorithm SHA256 `
-NotAfter $expiryDate # <--- This sets the expiration
# 3. Export the Public Key (.cer) to upload to Azure Portal
Export-Certificate -Cert $cert -FilePath $certpath
# 4. Export the Private Key (.pfx) to be used by your Python script
# (You will need to install this .pfx on the SQL Server host)
Export-PfxCertificate -Cert $cert -FilePath $pfxpath -Password $pwd
# 5. Convert Private Key to .pem file
openssl pkcs12 -in $pfxpath -out $pempath -nodes -passin pass:$pwdstring
Entra ID currently supports RSA certificates. Ensure your self-signed cert is created with the RSA algorithm (the PowerShell example above does this).
If we need to create a PEM file manually for the certificate:
# If you have OpenSSL installed:
openssl pkcs12 -in C:\Temp\cert.pfx -out C:\SQL_Secrets\SQLDataExportCert.pem -nodes
To create a certification for SQL Server encrypted connection, need to ensure the “Enhanced Key Usage” property must include Server Authentication.
# 1. Define variables
$certname = "SQL_Server_Encryption_Cert"
$certpath = "C:\temp\$certname.cer"
$instanceName = "MSSQLSERVER" # Change this if using a named instance
$serviceAccount = "NT SERVICE\$instanceName"
$dnsName = $env:COMPUTERNAME # or specify the computer name for remote server
# 2. Create the Self-Signed Certificate
$cert = New-SelfSignedCertificate -DnsName $dnsName `
-CertStoreLocation "cert:\LocalMachine\My" `
-FriendlyName $certname `
-Type Custom `
-KeySpec KeyExchange `
-TextExtension @("2.5.29.37={text}1.3.6.1.5.5.7.3.1") # this indicates for Server Authentication
Write-Host "Certificate created with Thumbprint: $($cert.Thumbprint)"
# 3. Export the Public Key (.cer) if needed, such as cert is for remote server
Export-Certificate -Cert $cert -FilePath $certpath
We need to grant the SQL Service account read access to the certificate:
$certname = "SQL_Server_Encryption_Cert"
$serviceAccount = "NT SERVICE\$instanceName"
# 1. Search for the certificate by subject name in the Local Machine store
$cert = Get-ChildItem -Path Cert:\LocalMachine\My |
Where-Object { $_.Subject -like "*$certname*" } |
Select-Object -First 1
# we can search for the certificate by thumbprint if we have it
# $certThumbprint = "YOUR_CERTIFICATE_THUMBPRINT_HERE"
# $cert = Get-Item -Path "Cert:\LocalMachine\My\$certThumbprint"
# Check if a certificate was found
if ($null -eq $cert) {
Write-Error "No certificate found matching that subject name."
} else {
# 2. Grant Read Access to the SQL Service Account
$rsaCert = [System.Security.Cryptography.X509Certificates.RSACertificateExtensions]::GetRSAPrivateKey($cert)
$fileName = $rsaCert.key.UniqueName
$path = "$env:ALLUSERSPROFILE\Microsoft\Crypto\RSA\MachineKeys\$fileName"
$acl = Get-Acl -Path $path
$permission = $serviceAccount,"Read","Allow"
$accessRule = New-Object System.Security.AccessControl.FileSystemAccessRule($permission)
$acl.AddAccessRule($accessRule)
Set-Acl -Path $path -AclObject $acl
Write-Host "Permissions granted to $serviceAccount"
}
Once the certificate is installed on the server:
- Open SQL Server Configuration Manager.
- Expand SQL Server Network Configuration.
- Right-click Protocols for [YourInstanceName] (e.g., Protocols for MSSQLSERVER) and select Properties.
- On the Certificate tab, select the certificate you installed from the dropdown.
- On the Flags tab, set Force Encryption to Yes if needed. Note: Setting this to “Yes” forces all clients connecting to this instance to use encryption.
- Click OK and Restart the SQL Server Service for changes to take effect.