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

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).

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.