This blog contains experience gained over the years of implementing (and de-implementing) large scale IT applications/software.

Category: Powershell

Create a SAML Key & Cert in Powershell for PowerBI to HANA SSO

Create a SAML Key & Cert in Powershell for PowerBI to HANA SSO

You’ve seen the Microsoft documentation, you want to create a private key and a certificate that can be used with a SAML assertion for single-sign-on between Microsoft PowerBI Gateway and a SAP HANA 2.0 database.

Where’s the problem?

The problem, is that the Microsoft documentation simply says to use “openssl”. Not only that, but it suggests that you create a CA (Certificate Authority) key and certificate first, then create a separate key and certificate for the actual IdP (which in this setup is the PowerBI Gateway), and sign it with the new CA certificate, creating a chain.

This is just not needed in SAML. In a SAML assertion, the certificate used for signature doesn’t need to be signed/issued by anyone in particular because the chain is not validated. The lifetime of the certificate can be loooong, because there is no chain. In the case of a compromised key, the IdP key and certificate would need to be re-created and the new certificate distributed to the target systems (service providers like SAP HANA database).

In this post I’m actually addressing two issues with the Microsoft documentation (I’ve raised one on the github page, but alas it has not been closed yet).

  1. No CA is needed. A self-signed IdP certificate works perfectly fine.
  2. OpenSSL is not needed. We can do it all in Powershell (with an evil laugh added in for extra evilness).
  3. We don’t need to export the key and certificate into a PFX file, then import it into the Windows certificate store.

Show me the Code

After fiddling around with various iterations, I found that the following Powershell code produces a key and certificate that is accepted by PowerBI Gateway (the key uses a valid provider).
The certificate is also accepted by SAP HANA once you’ve imported it into the new Identity Provider that you create in the HANA Cockpit (or HANA Studio if you must – tut tut tut).

Code to create a private key, plus certificate valid for 10 years from today and store it in the Windows “LocalMachine” certificates store in the “Personal” folder on the PowerBI Gateway:

$params = @{
Subject = "CN=PowerBI HANA IdP,OU=MyDept,O=MyCompany,L=MyTown,ST=MyShire,C=GB"
KeyAlgorithm = 'RSA'
KeyLength = 2048
NotAfter = $(Get-Date).AddMonths(120)
FriendlyName = "PowerBI HANA IdP Cert"
KeyFriendlyName = "PowerBI HANA IdP Cert"
HashAlgorithm = 'SHA256'
KeyUsage = 'None'
SuppressOid = '2.5.29.37'
CertStoreLocation = 'Cert:\LocalMachine\My'
Provider = 'Microsoft Enhanced RSA and AES Cryptographic Provider'
KeySpec = 'KeyExchange'
TextExtension = @("2.5.29.19={text}CA=true")
}

$cert = New-SelfSignedCertificate @params

The code above generates an x.509 certificate that contains “CA=true” in the BasicConstraints section.
This is the only part that may not actually be needed. I was really tired after all the investigation. Think of the time I have just saved you here!
If you don’t do this part and it still works, let me know.

Without the “Provider” parameter specifically set to “Microsoft Enhanced RSA and AES Cryptographic Provider” the PowerBI Gateway would not select the key during the SSO connection test.
Instead it would throw an error like so in the GatewayErrors log:

InnerToString=<ccon>System.Security.Cryptography.CryptographicException: Invalid provider type specified.

I had to use the “SuppressOid” parameter to prevent the KeyUsage being added, even if I specifically set “KeyUsage = ‘None'”.
This is really just cosmetic, because I wanted the certificate to look as identical to the openssl generated certificate. If you don’t do this part and it still works, let me know.

I believe that the OpenSSL equivalent command lines are:

openssl req -x509 -sha256 -days 3650 -newkey rsa:2048 -keyout IdP_Key.pem -out IdP_Cert.pem -subj "/C=GB/ST=MyShire/L=MyTown/O=MyCompany/OU=MyDept/CN=PowerBI HANA IdP"
[enter my password]

openssl pkcs12 -export -out IdP_PFX.pfx -in IdP_Cert.pem -inkey IdP_Key.pem -passin pass:my-password -passout pass:my-password

By doing all the work in Powershell, there is no need to enter passwords, export files and transfer files (if you don’t have openssl.exe on the PowerBI Gateway).

You may also be interested in:

PowerShell Encrypt / Decrypt OpenSSL AES256 CBC

A few months back I had a Korn shell script which used OpenSSL to encrypt some text using AES 256 CBC.
I managed, through the power of stackoverflow.com and various other blogs, to write a Java routine to perform the exact same encrypt/decrypt.
This allowed me to encrypt in Korn on Linux and decrypt in Java which was running inside a SAP Netweaver application server, or the other way around (encrypt in Java and decrypt in Korn using OpenSSL).

About 2 months after that, I needed the same set of routines to be written in PowerShell, allowing the same encrypted text to be encrypted on Linux with OpenSSL and decrypted on Windows in PowerShell (no need for OpenSSL).

I forked the PowerShell code which did the initial encryption and wrote the decryption routine which I’ve published as a Github gist here:

https://gist.github.com/Darryl-G/d1039c2407262cb6d735c3e7a730ee86

You may also be interested in:

Korn Shell vs Powershell and the New AZ Module

Do you know Korn and are thinking about learning Powershell?

Look at this:

function What-am-I {
   echo “Korn or powershell?”
}

what-am-i
echo $?

Looks like Korn, but it also looks like Powershell.
In actual fact, it executes in both Korn shell and Powershell.

There’s a slight difference in the output from “$?” because Powershell will output “True” and Korn will output “0”.
Not much in it really. That is just another reason Linux people are feeling the Microsoft love right now.

Plus, as recently highlighted by a Microsoft blog post, the Azure CLI known as “az” which allows you to interact with Azure APIs and functions, will now also be the name of the new Powershell module used to perform the same operations and replacing “AzureRM”.

It makes sense for Microsoft to harmonise the two names.
It could save them an awful lot of documentation because currently they have to write examples for both “az” CLI and Powershell cmdlets for each new Azure feature/function.

Powershell PSCredential – Exception calling .ctor with 2 arguments

Scenario: You are using Powershell with “System.Management.Automation.PSCredential” to capture credentials to an encrypted file using “ConvertFrom-SecureString”.
You are then trying to read those credentials as a different Windows account under which they were originally captured, using ConvertTo-SecureString.

You get an error like “Exception calling .ctor with 2 arguments”.

As per David Lee’s blog page (here https://blog.kloud.com.au/2016/04/21/using-saved-credentials-securely-in-powershell-scripts/):

“…PowerShell is using the native Windows Data Protection API (DAPI) functionality to encrypt the password from the ‘secure string’ into a text string. This string can be written to a plain text file, but the way that DAPI works is that the encryption is such that only the original user on the original machine the encryption was performed on can decrypt the string back into a ‘Secure string’ to be reused“.

Therefore, following David’s blog, simply use the “-key” parameter when calling “ConvertFrom-SecureString” or “ConvertTo-SecureString” along with a pre-determined AES key.

What I actually did was store the Key file securely with ACL permissions along with the credential file.
Then when reading them back in using “ConvertFrom-SecureString” from a different Windows account on the same machine, I was able to successfully decrypt and use the credential.

Example using credential to map a target UNC to the H: drive on the current machine:

# Some variable declarations.
$keyFile = “C:somefile.key”
$credFile = “C:somefile.cred”
$myUser = “someuser”
$targetPath = “H”
$sourcePath = “//some/unc/path”

# Generate AES key and save to keyFile:
$AESKey = New-Object Byte[] 32
[Security.Cryptography.RNGCryptoServiceProvider]::Create().GetBytes($AESKey)
Set-Content $keyFile $AESKey

# Prompt for a user/password and save the password to a credential file.
$credential = Get-Credential “$myUser”
$credential.Password | ConvertFrom-SecureString -Key (Get-Content $keyFile) | Set-Content $credFile

# As other user account.
# We can now reference the key file (imagine we are running as another user account in another script or something at this point).
# Read in the credential from the file and use it.
$encrypted = Get-Content $credFile | ConvertTo-SecureString -key (Get-Content $keyFile)
$credential = New-Object -TypeName System.Management.Automation.PsCredential -ArgumentList “$myUser”, $encrypted

# Map a drive using the credential.
New-PsDrive -Persist -Name $targetPath -PsProvider “FileSystem” -Scope “Global” -Root $sourcePath -Credential $credential -ErrorAction Stop

Using the separate AES key got around the error.