SQL Server Requires Chilkat v11.0.0+
Get Certificate Chain Certs

Demonstrates how to get the certs in the chain of authentication for a certificate loaded from the Windows certificate store.
Chilkat SQL Server Downloads

Download Chilkat for SQL Server
SQL Server
-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    DECLARE @cert int
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    --  This example will get the SSL server certificate for tools.chilkat.io
    --  which we happen to have locally installed in our Windows certificate store.
    EXEC sp_OAMethod @cert, 'LoadByCommonName', @success OUT, 'tools.chilkat.io'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

    DECLARE @chain int
    EXEC @hr = sp_OACreate 'Chilkat.CertChain', @chain OUT

    EXEC sp_OAMethod @cert, 'BuildCertChain', @success OUT, @chain
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @chain
        RETURN
      END

    DECLARE @certInChain int
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @certInChain OUT

    DECLARE @numCerts int
    EXEC sp_OAGetProperty @chain, 'NumCerts', @numCerts OUT
    DECLARE @i int
    SELECT @i = 0
    WHILE @i < @numCerts
      BEGIN
        EXEC sp_OAMethod @chain, 'CertAt', @success OUT, @i, @certInChain

        EXEC sp_OAGetProperty @certInChain, 'SubjectCN', @sTmp0 OUT
        PRINT @i + 1 + ': ' + @sTmp0

        --  Get this certificate in base64 format
        DECLARE @b64 nvarchar(4000)
        EXEC sp_OAMethod @certInChain, 'GetEncoded', @b64 OUT

        PRINT @b64

        SELECT @i = @i + 1
      END

    --  The output of the above code is:

    --  1: tools.chilkat.io
    --  MIIGdzCCBV+gAwIBAgIQah36/0eUf3vjWnJEqykBITANBgkqhkiG9w0BAQsFADCB
    --  jzELMAkGA1UEBhMCR0IxGzAZBgNVBAgTEkdyZWF0ZXIgTWFuY2hlc3RlcjEQMA4G
    --  A1UEBxMHU2FsZm9yZDEYMBYGA1UEChMPU2VjdGlnbyBMaW1pdGVkMTcwNQYDVQQD
    --  Ey5TZWN0aWdvIFJTQSBEb21haW4gVmFsaWRhdGlvbiBTZWN1cmUgU2VydmVyIENB
    --  MB4XDTE5MDkxMjAwMDAwMFoXDTIxMTAwOTIzNTk1OVowVDEhMB8GA1UECxMYRG9t
    --  YWluIENvbnRyb2wgVmFsaWRhdGVkMRQwEgYDVQQLEwtQb3NpdGl2ZVNTTDEZMBcG
    --  A1UEAxMQdG9vbHMuY2hpbGthdC5pbzCCASIwDQYJKoZIhvcNAQEBBQADggEPADCC
    --  AQoCggEBANdVgSFZbZzl7LehEGTmXLSOCagd+f6ullW5jv5Vi79OswJZXDJm4YV3
    --  eotbiSCAioqdkpgH+IqK+GTh1lcHR2GPg5H3qQZiRjLvGKZGSQinhtC3XoYp2Eoh
    --  7WDn4O8X9Kpj+vo2keExtp9vEpfcttSq99Fa6p/jc1MJke6SXhOxpn9AgXRhptQ7
    --  kBekQzVyhCshWweRoS5FdnTU45gXuqPWjiU+ZbJcErS81DHoHInO/Y4goG9ZhSBL
    --  K44IG/Zu6OhNod4bi6Tp+ZxrdYMPmKCM/Bv3Kv3Lk5O7z0wZ9Uwfx6hih4VT7gh1
    --  4/YNfocQ1Hfn0Z8ImA9TsXE2Z8nE/8ECAwEAAaOCAwcwggMDMB8GA1UdIwQYMBaA
    --  FI2MXsRUrYrhd+mb+ZsF4bgBjWHhMB0GA1UdDgQWBBRB2rtEZWLEKmx5tAadh/zf
    --  vxq/uDAOBgNVHQ8BAf8EBAMCBaAwDAYDVR0TAQH/BAIwADAdBgNVHSUEFjAUBggr
    --  BgEFBQcDAQYIKwYBBQUHAwIwSQYDVR0gBEIwQDA0BgsrBgEEAbIxAQICBzAlMCMG
    --  CCsGAQUFBwIBFhdodHRwczovL3NlY3RpZ28uY29tL0NQUzAIBgZngQwBAgEwgYQG
    --  CCsGAQUFBwEBBHgwdjBPBggrBgEFBQcwAoZDaHR0cDovL2NydC5zZWN0aWdvLmNv
    --  bS9TZWN0aWdvUlNBRG9tYWluVmFsaWRhdGlvblNlY3VyZVNlcnZlckNBLmNydDAj
    --  BggrBgEFBQcwAYYXaHR0cDovL29jc3Auc2VjdGlnby5jb20wMQYDVR0RBCowKIIQ
    --  dG9vbHMuY2hpbGthdC5pb4IUd3d3LnRvb2xzLmNoaWxrYXQuaW8wggF9BgorBgEE
    --  AdZ5AgQCBIIBbQSCAWkBZwB1APZclC/RdzAiFFQYCDCUVo7jTRMZM7/fDC8gC8xO
    --  8WTjAAABbSe5NSgAAAQDAEYwRAIge5XZG/r8MyMtCaq3dFIupz0HOuixpiDn7QY7
    --  mZbyCZoCIA8vxER7vGJw4u0Jd3I1hbjmJ5jgd8tuS6IJqUMSLynDAHYARJRlLrDu
    --  zq/EQAfYqP4owNrmgr7YyzG1P9MzlrW2gagAAAFtJ7k1OQAABAMARzBFAiEAt/1A
    --  XP+UsqZLGrYs/tTYEXhbKsbGdqjkiFJ/pMaAzfgCIBFBekEL3VZlcNqiH+VeeUco
    --  J64Q7Beli3EXYkpNBcUWAHYAVYHUwhaQNgFK6gubVzxT8MDkOHhwJQgXL6OqHQcT
    --  0wwAAAFtJ7k1VwAABAMARzBFAiEAx5eeXuST2cUbvPiIMjng9nVOhVThuS8S+LtB
    --  Qmd0kY0CIEM1RNaXfxaVfw55yAgOdDMUkHK30Fr9sEpoHC8JjP/nMA0GCSqGSIb3
    --  DQEBCwUAA4IBAQCPIum32PWfyKW5q08telvzsPRJszPmPEp7OQM7uZebcSwDs9JC
    --  4zm9UAQyOdkUc7YhqFFNnRZqIW4saEJKEo4QkGeDNXHuCYJfFMr4RdZcQWCeGPdQ
    --  cWPp+Ef0nOEw/sUhq9+bBnWsW2xZ78ZmIQd5D/mWoftVDRANCxE3o75Q+bwwsHDx
    --  TEE/KH4dnlAgL8YH1baUcLtp7CrfPe2E20r8SqDa+AOLx6UdOup5Biez7KvFVdP9
    --  QkLUfADZxMzFV1Bnq+8bDCDt6mm184OAQvTS30Uxvy5gZ3FQNHUeqRG2LkL/2qGY
    --  rlswE8OkrED07VhRMt0skT2QZsD2U6aiCkAw
    --  
    --  2: Sectigo RSA Domain Validation Secure Server CA
    --  MIIGEzCCA/ugAwIBAgIQfVtRJrR2uhHbdBYLvFMNpzANBgkqhkiG9w0BAQwFADCB
    --  iDELMAkGA1UEBhMCVVMxEzARBgNVBAgTCk5ldyBKZXJzZXkxFDASBgNVBAcTC0pl
    --  cnNleSBDaXR5MR4wHAYDVQQKExVUaGUgVVNFUlRSVVNUIE5ldHdvcmsxLjAsBgNV
    --  BAMTJVVTRVJUcnVzdCBSU0EgQ2VydGlmaWNhdGlvbiBBdXRob3JpdHkwHhcNMTgx
    --  MTAyMDAwMDAwWhcNMzAxMjMxMjM1OTU5WjCBjzELMAkGA1UEBhMCR0IxGzAZBgNV
    --  BAgTEkdyZWF0ZXIgTWFuY2hlc3RlcjEQMA4GA1UEBxMHU2FsZm9yZDEYMBYGA1UE
    --  ChMPU2VjdGlnbyBMaW1pdGVkMTcwNQYDVQQDEy5TZWN0aWdvIFJTQSBEb21haW4g
    --  VmFsaWRhdGlvbiBTZWN1cmUgU2VydmVyIENBMIIBIjANBgkqhkiG9w0BAQEFAAOC
    --  AQ8AMIIBCgKCAQEA1nMz1tc8INAA0hdFuNY+B6I/x0HuMjDJsGz99J/LEpgPLT+N
    --  TQEMgg8Xf2Iu6bhIefsWg06t1zIlk7cHv7lQP6lMw0Aq6Tn/2YHKHxYyQdqAJrkj
    --  eocgHuP/IJo8lURvh3UGkEC0MpMWCRAIIz7S3YcPb11RFGoKacVPAXJpz9OTTG0E
    --  oKMbgn6xmrntxZ7FN3ifmgg0+1YuWMQJDgZkW7w33PGfKGioVrCSo1yfu4iYCBsk
    --  Haswha6vsC6eep3BwEIc4gLw6uBK0u+QDrTBQBbwb4VCSmT3pDCg/r8uoydajotY
    --  uK3DGReEY+1vVv2Dy2A0xHS+5p3b4eTlygxfFQIDAQABo4IBbjCCAWowHwYDVR0j
    --  BBgwFoAUU3m/WqorSs9UgOHYm8Cd8rIDZsswHQYDVR0OBBYEFI2MXsRUrYrhd+mb
    --  +ZsF4bgBjWHhMA4GA1UdDwEB/wQEAwIBhjASBgNVHRMBAf8ECDAGAQH/AgEAMB0G
    --  A1UdJQQWMBQGCCsGAQUFBwMBBggrBgEFBQcDAjAbBgNVHSAEFDASMAYGBFUdIAAw
    --  CAYGZ4EMAQIBMFAGA1UdHwRJMEcwRaBDoEGGP2h0dHA6Ly9jcmwudXNlcnRydXN0
    --  LmNvbS9VU0VSVHJ1c3RSU0FDZXJ0aWZpY2F0aW9uQXV0aG9yaXR5LmNybDB2Bggr
    --  BgEFBQcBAQRqMGgwPwYIKwYBBQUHMAKGM2h0dHA6Ly9jcnQudXNlcnRydXN0LmNv
    --  bS9VU0VSVHJ1c3RSU0FBZGRUcnVzdENBLmNydDAlBggrBgEFBQcwAYYZaHR0cDov
    --  L29jc3AudXNlcnRydXN0LmNvbTANBgkqhkiG9w0BAQwFAAOCAgEAMr9hvQ5Iw0/H
    --  ukdN+Jx4GQHcEx2Ab/zDcLRSmjEzmldS+zGea6TvVKqJjUAXaPgREHzSyrHxVYbH
    --  7rM2kYb2OVG/Rr8PoLq0935JxCo2F57kaDl6r5ROVm+yezu/Coa9zcV3HAO4OLGi
    --  H19+24rcRki2aArPsrW04jTkZ6k4Zgle0rj8nSg6F0AnwnJOKf0hPHzPE/uWLMUx
    --  RP0T7dWbqWlod3zu4f+k+TY4CFM5ooQ0nBnzvg6s1SQ36yOoeNDT5++SR2RiOSLv
    --  xvcRviKFxmZEJCaOEDKNyJOuB56DPi/Z+fVGjmO+wea03KbNIaiGCpXZLoUmGv38
    --  sbZXQm2V0TP2ORQGgkE49Y9Y3IBbpNV9lXj9p5v//cWoaasm56ekBYdbqbe4oyAL
    --  l6lFhd2zi+WJN44pDfwGF/Y4QA5C5BIG+3vzxhFoYt/jmPQT2BVPi7Fp2RBgvGQq
    --  6jG35LWjOhSbJuMLe/0CjraZwTiXWTb2qHSihrZe68Zk6s+go/lunrotEbaGmAhY
    --  LcmsJWTyXnW0OMGuf1pGg+pRyrbxmRE1a6Vqe8YAsOf4vmSyrcjC8azjUeqkk+B5
    --  yOGBQMkKW+ESPMFgKuOXwIlCypTPRpgSabuY0MLTDXJLR27lk8QyKGOHQ+SwMj4K
    --  00u/I5sUKUErmgQfky3xxzlIPK1aEn8=
    --  
    --  3: USERTrust RSA Certification Authority
    --  MIIFgTCCBGmgAwIBAgIQOXJEOvkit1HX02wQ3TE1lTANBgkqhkiG9w0BAQwFADB7
    --  MQswCQYDVQQGEwJHQjEbMBkGA1UECAwSR3JlYXRlciBNYW5jaGVzdGVyMRAwDgYD
    --  VQQHDAdTYWxmb3JkMRowGAYDVQQKDBFDb21vZG8gQ0EgTGltaXRlZDEhMB8GA1UE
    --  AwwYQUFBIENlcnRpZmljYXRlIFNlcnZpY2VzMB4XDTE5MDMxMjAwMDAwMFoXDTI4
    --  MTIzMTIzNTk1OVowgYgxCzAJBgNVBAYTAlVTMRMwEQYDVQQIEwpOZXcgSmVyc2V5
    --  MRQwEgYDVQQHEwtKZXJzZXkgQ2l0eTEeMBwGA1UEChMVVGhlIFVTRVJUUlVTVCBO
    --  ZXR3b3JrMS4wLAYDVQQDEyVVU0VSVHJ1c3QgUlNBIENlcnRpZmljYXRpb24gQXV0
    --  aG9yaXR5MIICIjANBgkqhkiG9w0BAQEFAAOCAg8AMIICCgKCAgEAgBJlFzYOw9sI
    --  s9CsVw127c0n00ytUINh4qogTQktZAnczomfzD2p7PbPwdzx07HWezcoEStH2jnG
    --  vDoZtF+mvX2do2NCtnbyqTsrkfjib9DsFiCQCT7i6HTJGLSR1GJk23+jBvGIGGqQ
    --  Ijy8/hPwhxR79uQfjtTkUcYRZ0YIUcuGFFQ/vDP+fmyc/xadGL1RjjWmp2bIcmfb
    --  IWax1Jt4A8BQOujM8Ny8nkz+rwWWNR9XWrf/zvk9tyy29lTdyOcSOk2uTIq3XJq0
    --  tyA9yn8iNK5+O2hmAUTnAU5GU5szYPeUvlM3kHND8zLDU+/bqv50TmnHa4xgk97E
    --  xwzf4TKuzJM7UXiVZ4vuPVb+DNBpDxsP8yUmazNt925H+nND5X4OpWaxKXwyhGNV
    --  icQNwZNUMBkTrNN9N6frXTpsNVzbQdcS2qlJC9/YgIoJk2KOtWbPJYjNhLixP6Q5
    --  D9kCnusSTJV882sFqV4Wg8y4Z+LoE53MW4LTTLPtW//e5XOsIzstAL81VXQJSdhJ
    --  WBp/kjbmUZIO8yZ9HE0XvMnsQybQv0FfQKlERPSZ51eHnlAfV1SoPv10Yy+xUGUJ
    --  5lhCLkMaTLTwJUdZ+gQek9QmRkpQgbLevni3/GcV4clXhB4PY9bpYrrWX1Uu6lzG
    --  KAgEJTm4Diup8kyXHAc/DVL17e8vgg8CAwEAAaOB8jCB7zAfBgNVHSMEGDAWgBSg
    --  EQojPpbxB+zirynvgqV/0DCktDAdBgNVHQ4EFgQUU3m/WqorSs9UgOHYm8Cd8rID
    --  ZsswDgYDVR0PAQH/BAQDAgGGMA8GA1UdEwEB/wQFMAMBAf8wEQYDVR0gBAowCDAG
    --  BgRVHSAAMEMGA1UdHwQ8MDowOKA2oDSGMmh0dHA6Ly9jcmwuY29tb2RvY2EuY29t
    --  L0FBQUNlcnRpZmljYXRlU2VydmljZXMuY3JsMDQGCCsGAQUFBwEBBCgwJjAkBggr
    --  BgEFBQcwAYYYaHR0cDovL29jc3AuY29tb2RvY2EuY29tMA0GCSqGSIb3DQEBDAUA
    --  A4IBAQAYh1HcdCE9nIrgJ7cz0C7M7PDmy14R3iJvm3WOnnL+5Nb+qh+cli3vA0p+
    --  rvSNb3I8QzvAP+u431yqqcau8vzY7qN7Q/aGNnwU4M309z/+3ri0ivCRlv79Q2R+
    --  /czSAaF9ffgZGclCKxO/WIu6pKJmBHaIkU4MiRTOok3JMrO66BQavHHxW/BBC5gA
    --  CiIDEOUMsfnNkjcZ7Tvx5Dq2+UUTJnWvu6rvP3t3O9LEApE9GQDTF1w52z97GA1F
    --  zZOFli9d31kWTz9RvdVFGD/tSo7oBmF0Ixa1DVBzJ0RHfxBdiSprhTEUxOipakyA
    --  vGp4z7h/jnZymQyd/teRCBaho1+V
    --  
    --  4: AAA Certificate Services
    --  MIIEMjCCAxqgAwIBAgIBATANBgkqhkiG9w0BAQUFADB7MQswCQYDVQQGEwJHQjEb
    --  MBkGA1UECAwSR3JlYXRlciBNYW5jaGVzdGVyMRAwDgYDVQQHDAdTYWxmb3JkMRow
    --  GAYDVQQKDBFDb21vZG8gQ0EgTGltaXRlZDEhMB8GA1UEAwwYQUFBIENlcnRpZmlj
    --  YXRlIFNlcnZpY2VzMB4XDTA0MDEwMTAwMDAwMFoXDTI4MTIzMTIzNTk1OVowezEL
    --  MAkGA1UEBhMCR0IxGzAZBgNVBAgMEkdyZWF0ZXIgTWFuY2hlc3RlcjEQMA4GA1UE
    --  BwwHU2FsZm9yZDEaMBgGA1UECgwRQ29tb2RvIENBIExpbWl0ZWQxITAfBgNVBAMM
    --  GEFBQSBDZXJ0aWZpY2F0ZSBTZXJ2aWNlczCCASIwDQYJKoZIhvcNAQEBBQADggEP
    --  ADCCAQoCggEBAL5AnfRu4ep2hxxNRUSOvkbIgwadwSr+GB+O5AL686tdUIoWMQua
    --  BtDFcCLNSS1UY8y2bmhGC1Pqy0wkwLxyTurxFa70VJoSCsN6sjNg4tqJVfMiWPPe
    --  3M/vg4aijJRPn2jymJBGhCfHdr/jzDUsi14HZGWCwEiwqJH5YZ92IFCokcdmtet4
    --  YgNW8IoaE+oxox6gmf049vYnMlhvB/VruPsUK6+3qszWY19zjNoFmag4qMsXeDZR
    --  rOme9Hg6jc8P2ULimAyrL58OAd7vn5lJ8S3frHRNG5i1R8XlKdH5kBjHYpy+g8cm
    --  ez6KJcfA3Z3mNWgQIJ2P2N7Sw4ScDV7oL8kCAwEAAaOBwDCBvTAdBgNVHQ4EFgQU
    --  oBEKIz6W8Qfs4q8p74Klf9AwpLQwDgYDVR0PAQH/BAQDAgEGMA8GA1UdEwEB/wQF
    --  MAMBAf8wewYDVR0fBHQwcjA4oDagNIYyaHR0cDovL2NybC5jb21vZG9jYS5jb20v
    --  QUFBQ2VydGlmaWNhdGVTZXJ2aWNlcy5jcmwwNqA0oDKGMGh0dHA6Ly9jcmwuY29t
    --  b2RvLm5ldC9BQUFDZXJ0aWZpY2F0ZVNlcnZpY2VzLmNybDANBgkqhkiG9w0BAQUF
    --  AAOCAQEACFb8AvCb6P+k+tZ7xkSAzk/ExfYAWMymtrwUSWgEdujm7l3sAg9g1o1Q
    --  GE8mTgHj5rCl7r+8dFRBv/38ErjHT1r0iWAFf2C3BUrz9vHCv8S5dIa2LX1rzNLz
    --  Rt0vxuBqw8M0Ayx9lt1awg6nCpnBBYurDC/zXDrPbDdVCYfeU0BsWO/8tqtlbgT2
    --  G9w84FoVxp7Z8VlIMCFlA2zs6SFz7JsDoeA3raAVGI/6ugLOpyypEBMs1OUIJqsi
    --  l2D4kF501KKaU73yqWjgom7C12yxow+ev+to51byrvLjKzg6CYG1a4XXvi3tPxq3
    --  smPi9WIsgtRqAEFQ8TmDn5XpNpaYbg==

    EXEC @hr = sp_OADestroy @cert
    EXEC @hr = sp_OADestroy @chain
    EXEC @hr = sp_OADestroy @certInChain


END
GO