Add support for keyfiles instead of username/password?

[Ekus]

Can you add support for keyfiles instead of username/password? See https://github.com/snowflakedb/snowflake-connector-net (possibly skip private_key_pwd)

conn.ConnectionString = "account=testaccount;authenticator=snowflake_jwt;user=testuser;private_key_file={pathToThePrivateKeyFile};private_key_pwd={passwordForDecryptingThePrivateKey};db=testdb;schema=testschema";

Originally posted by @Ekus in https://github.com/fixer-m/snowflake-db-net-client/issues/1#issuecomment-969158440

[nadjalla]

It will be great to have this. Any planned release date? @fixer-m

[tom-j-irvine]

I created the following class to generate the necessary JWT, based on the following: https://docs.snowflake.com/en/developer-guide/sql-api/authenticating

Once you have the token, you would just add these headers to the request:

Authorization: Bearer <jwt>
X-Snowflake-Authorization-Token-Type: KEYPAIR_JWT

This is .NET6 code that uses the System.IdentityModel.Tokens.Jwt package by Microsoft. It was a little bit of a battle to find all of the right parameters, but it works for both plain and encrypted keys generated based on the Snowflake docs here: https://docs.snowflake.com/en/user-guide/key-pair-auth

NOTE: the PrivateKeyPem input expects to have the BEGIN and END tokens from the files - like these:

-----BEGIN ENCRYPTED PRIVATE KEY-----
MIIE6TAbBgkqhkiG9w0BBQMwDgQILYPyCppzOwECAggABIIEyLiGSpeeGSe3xHP1
wHLjfCYycUPennlX2bd8yX8xOxGSGfvB+99+PmSlex0FmY9ov1J8H1H9Y3lMWXbL
...
-----END ENCRYPTED PRIVATE KEY-----

using Microsoft.IdentityModel.Tokens;
using System.IdentityModel.Tokens.Jwt;
using System.Security.Claims;
using System.Security.Cryptography;
using System.Text;

public class SnowflakeJwt
{
    public string Account { get; set; }
    public string User { get; set; }
    public string PrivateKeyPem { get; set; }        
    public string PassPhrase { get; set; }

    public string QualifiedUsername
    {
        get { return $"{Account}.{User}".ToUpper(); }
    }

    public SnowflakeJwt(string account, string user, string privateKeyPem, string passPhrase = null)
    {
        Account = account;
        User = user;
        PrivateKeyPem = privateKeyPem;
        PassPhrase = passPhrase;
    }

    public string GetToken(TimeSpan lifetime)
    {            
        string publicKeyFingerprint;

        using RSA rsa = RSA.Create();
        if (string.IsNullOrEmpty(PassPhrase))
        {
            // non-encrypted PEM
            rsa.ImportFromPem(PrivateKeyPem);
        }
        else
        {
            // encrypted PEM
            rsa.ImportFromEncryptedPem(PrivateKeyPem, Encoding.UTF8.GetBytes(PassPhrase));
        }

        // generate the public key fingerprint
        using (SHA256 sha256 = SHA256.Create())
        {
            publicKeyFingerprint = "SHA256:" + Convert.ToBase64String(
                sha256.ComputeHash(
                    rsa.ExportSubjectPublicKeyInfo()
                    )
                );
        }

        // define the token
        DateTime now = DateTime.UtcNow;

        var tokenHandler = new JwtSecurityTokenHandler();
        var tokenDescriptor = new SecurityTokenDescriptor
        {
            IssuedAt = now,
            Expires = now + lifetime,
            Issuer = QualifiedUsername + "." + publicKeyFingerprint,
            Subject = new ClaimsIdentity(new List<Claim> { new Claim("sub", QualifiedUsername) }),
            SigningCredentials = new SigningCredentials(new RsaSecurityKey(rsa), SecurityAlgorithms.RsaSha256)
            {
                CryptoProviderFactory = new CryptoProviderFactory { CacheSignatureProviders = false }
            }
        };

        // generate the token
        var token = tokenHandler.CreateToken(tokenDescriptor);

        return tokenHandler.WriteToken(token);
    }
}