Cert SQL Server Reference Documentation

Cert

Current Version: 11.6.0

Chilkat.Cert

Load, inspect, validate, and use X.509 certificates throughout Chilkat.

Chilkat.Cert is the central certificate object used throughout Chilkat for TLS, S/MIME, PKCS7/CMS, PDF signing, XML signing, code signing, decryption, certificate stores, smart cards, HSMs, PKCS#11, and cloud-based signing. It provides a broad set of features for loading certificates, inspecting certificate fields, checking validity, accessing private keys, building chains, checking revocation status, exporting certificate data, and diagnosing certificate-related problems.

Load certificates

Load public certificates from PEM, DER, Base64, files, strings, binary data, certificate stores, PFX/P12 files, smart cards, and other sources.

Inspect certificate details

Read subject and issuer fields, serial numbers, fingerprints, validity dates, key usage, extensions, SAN values, and other X.509 information.

Private key access

Work with certificates backed by private keys from PFX/P12 files, OS certificate stores, smart cards, HSMs, PKCS#11 modules, or cloud signing providers.

Trust and validation

Build and inspect certificate chains, verify signatures, check expiration, examine trust-related information, and work with revocation data.

Use across Chilkat

Pass Cert objects to other Chilkat classes for TLS client authentication, S/MIME, signing, encryption, decryption, and verification.

Export and diagnostics

Export certificate data in common formats and use detailed diagnostic output to troubleshoot loading, validation, private-key, chain, or store problems.

Common pattern: Use certificate-only formats such as PEM or DER when only the public certificate is needed. Use PFX/P12, an OS certificate store, smart-card loading, PKCS#11 linking, HSM integration, or cloud-signing configuration when a private key operation is required.

For an extended overview, see Cert Class Overview.

Object Creation

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

-- ... use @cert ...

EXEC @hr = sp_OADestroy @cert

T-SQL uses the Chilkat ActiveX through the OLE Automation stored procedures. They must be enabled once on the server (EXEC sp_configure 'Ole Automation Procedures', 1; RECONFIGURE;), and the Chilkat ActiveX registered must match the bitness of the SQL Server instance (64-bit for a 64-bit SQL Server). To bind to a specific major version of Chilkat, append the major version number to the ProgID, such as sp_OACreate 'Chilkat.Cert.11' for Chilkat v11.*.*.

sp_OACreate returns an object token (an int) that is passed as the first argument of every sp_OAMethod, sp_OAGetProperty and sp_OASetProperty call, and released with sp_OADestroy. Objects returned by methods (such as an HttpResponse or JsonObject) are also tokens received through an int OUT parameter; they must likewise be destroyed, and the OUT parameter is NULL when the method fails to return an object. Objects passed as arguments are passed by their token. When an OLE Automation procedure itself fails (non-zero @hr), sp_OAGetErrorInfo describes the error.

Data types: strings are nvarchar(4000); integers, booleans (1 or 0) and object tokens are int; dates are datetime. In the signatures on this page, @success, @iResult, @sResult and the like are the OUT variables receiving a method's return value, @iValue / @sValue receive or supply a property value, and the remaining @ variables are the method's arguments in order.

A string returned through an OUT parameter is limited to 4000 characters. For longer values, retrieve the result as a result set into a table variable instead of an OUT parameter, for example DECLARE @tmp TABLE (outputLine ntext) followed by INSERT INTO @tmp EXEC sp_OAGetProperty @cert, 'LastErrorText'. See string length limitations for strings returned by sp_OAMethod calls.

Methods that pass or return raw byte arrays are not shown on this page, because varbinary(max) values cannot be exchanged through sp_OAMethod (see varbinary(max) limitation). Use the BinData-based alternatives (methods ending in Bd) or the base64 / hex string-encoded variants instead. Binary properties (such as LastBinaryResult) can be retrieved as a result set into a table variable, as shown in their signatures. Asynchronous (*Async) methods and event callbacks are not available from SQL Server.

Properties

AuthorityKeyId
EXEC sp_OAGetProperty @cert, 'AuthorityKeyId', @sValue OUT
Introduced in version 9.5.0.40

The authority key identifier of the certificate in base64 string format. This is only present if the certificate contains the extension OID 2.5.29.35.

top
AvoidWindowsPkAccess
EXEC sp_OAGetProperty @cert, 'AvoidWindowsPkAccess', @iValue OUT
EXEC sp_OASetProperty @cert, 'AvoidWindowsPkAccess', @iValue

Applies only when running on a Microsoft Windows operating system. If 1, then any method that returns a certificate will not try to also access the associated private key, assuming one exists. This is useful if the certificate was installed with high-security such that a private key access would trigger the Windows OS to display a security warning dialog. The default value of this property is 0.

top
CertVersion
EXEC sp_OAGetProperty @cert, 'CertVersion', @iValue OUT

The version of the certificate (1, 2, or 3). A value of 0 indicates an error -- the most likely cause being that the certificate object is empty (i.e. was never loaded with a certificate). Note: This is not the version of the software, it is the version of the X.509 certificate object. The version of the Chilkat certificate software is indicated by the Version property.

top
DebugLogFilePath
EXEC sp_OAGetProperty @cert, 'DebugLogFilePath', @sValue OUT
EXEC sp_OASetProperty @cert, 'DebugLogFilePath', @sValue

If set to a file path, this property logs the LastErrorText of each Chilkat method or property call to the specified file. This logging helps identify the context and history of Chilkat calls leading up to any crash or hang, aiding in debugging.

Enabling the VerboseLogging property provides more detailed information. This property is mainly used for debugging rare instances where a Chilkat method call causes a hang or crash, which should generally not happen.

Possible causes of hangs include:

  • A timeout property set to 0, indicating an infinite timeout.
  • A hang occurring within an event callback in the application code.
  • An internal bug in the Chilkat code causing the hang.

More Information and Examples
Example showing how to use DebugLogFilePath.
top
Expired
EXEC sp_OAGetProperty @cert, 'Expired', @iValue OUT

Has a value of 1 if the certificate or any certificate in the chain of authority has expired. (This information is not available when running on Windows 95/98 computers.)

top
ExtendedKeyUsage
EXEC sp_OAGetProperty @cert, 'ExtendedKeyUsage', @sValue OUT
Introduced in version 9.5.0.85

Returns a string containing a comma separated list of keywords with the extended key usages of the certificate. The list of possible extended key usages are:

  • serverAuth - TLS WWW server authentication
  • clientAuth - TLS WWW client authentication
  • codeSigning - Signing of downloadable executable code
  • emailProtection - Email protection
  • timeStamping - Binding the hash of an object to a time
  • OCSPSigning - Signing OCSP responses

top
ForClientAuthentication
EXEC sp_OAGetProperty @cert, 'ForClientAuthentication', @iValue OUT

1 if this certificate can be used for client authentication, otherwise 0.

top
ForCodeSigning
EXEC sp_OAGetProperty @cert, 'ForCodeSigning', @iValue OUT

1 if this certificate can be used for code signing, otherwise 0.

top
ForSecureEmail
EXEC sp_OAGetProperty @cert, 'ForSecureEmail', @iValue OUT

1 if this certificate can be used for sending secure email, otherwise 0.

top
ForServerAuthentication
EXEC sp_OAGetProperty @cert, 'ForServerAuthentication', @iValue OUT

1 if this certificate can be used for server authentication, otherwise 0.

top
ForTimeStamping
EXEC sp_OAGetProperty @cert, 'ForTimeStamping', @iValue OUT

1 if this certificate can be used for time stamping, otherwise 0.

top
IntendedKeyUsage
EXEC sp_OAGetProperty @cert, 'IntendedKeyUsage', @iValue OUT

Bitflags indicating the intended usages of the certificate. The flags are:
Digital Signature: 0x80
Non-Repudiation: 0x40
Key Encipherment: 0x20
Data Encipherment: 0x10
Key Agreement: 0x08
Certificate Signing: 0x04
CRL Signing: 0x02
Encipher-Only: 0x01

top
IsRoot
EXEC sp_OAGetProperty @cert, 'IsRoot', @iValue OUT

1 if this is the root certificate, otherwise 0.

top
IssuerC
EXEC sp_OAGetProperty @cert, 'IssuerC', @sValue OUT

The certificate issuer's country.

top
IssuerCN
EXEC sp_OAGetProperty @cert, 'IssuerCN', @sValue OUT

The certificate issuer's common name.

top
IssuerDN
EXEC sp_OAGetProperty @cert, 'IssuerDN', @sValue OUT

The issuer's full distinguished name.

top
IssuerE
EXEC sp_OAGetProperty @cert, 'IssuerE', @sValue OUT

The certificate issuer's email address.

top
IssuerL
EXEC sp_OAGetProperty @cert, 'IssuerL', @sValue OUT

The certificate issuer's locality, which could be a city, count, township, or other geographic region.

top
IssuerO
EXEC sp_OAGetProperty @cert, 'IssuerO', @sValue OUT

The certificate issuer's organization, which is typically the company name.

top
IssuerOU
EXEC sp_OAGetProperty @cert, 'IssuerOU', @sValue OUT

The certificate issuer's organizational unit, which is the unit within the organization.

top
IssuerS
EXEC sp_OAGetProperty @cert, 'IssuerS', @sValue OUT

The certificate issuer's state or province.

top
LastBinaryResult
INSERT INTO @tmp EXEC sp_OAGetProperty @cert, 'LastBinaryResult'

This property is mainly used in SQL Server stored procedures to retrieve binary data from the last method call that returned binary data. It is only accessible if Chilkat.Global.KeepBinaryResult is set to 1. This feature allows for the retrieval of large varbinary results in an SQL Server environment, which has restrictions on returning large data via method calls, though temp tables can handle binary properties.

top
LastErrorHtml
EXEC sp_OAGetProperty @cert, 'LastErrorHtml', @sValue OUT

Provides HTML-formatted information about the last called method or property. If a method call fails or behaves unexpectedly, check this property for details. Note that information is available regardless of the method call's success.

top
LastErrorText
EXEC sp_OAGetProperty @cert, 'LastErrorText', @sValue OUT

Provides plain text information about the last called method or property. If a method call fails or behaves unexpectedly, check this property for details. Note that information is available regardless of the method call's success.

More Information and Examples
Concept of LastErrorTextLastErrorText Standard Information
top
LastErrorXml
EXEC sp_OAGetProperty @cert, 'LastErrorXml', @sValue OUT

Provides XML-formatted information about the last called method or property. If a method call fails or behaves unexpectedly, check this property for details. Note that information is available regardless of the method call's success.

top
LastMethodSuccess
EXEC sp_OAGetProperty @cert, 'LastMethodSuccess', @iValue OUT
EXEC sp_OASetProperty @cert, 'LastMethodSuccess', @iValue

Indicates the success or failure of the most recent method call: 1 means success, 0 means failure. This property remains unchanged by property setters or getters. This method is present to address challenges in checking for null or Nothing returns in certain programming languages. Note: This property does not apply to methods that return integer values or to boolean-returning methods where the boolean does not indicate success or failure.

top
LastStringResult
EXEC sp_OAGetProperty @cert, 'LastStringResult', @sValue OUT

In SQL Server stored procedures, this property holds the string return value of the most recent method call that returns a string. It is accessible only when Chilkat.Global.KeepStringResult is set to TRUE. SQL Server has limitations on string lengths returned from methods and properties, but temp tables can be used to access large strings.

More Information and Examples
Long Strings Returned by ActiveX Methods in SQL Server
top
LastStringResultLen
EXEC sp_OAGetProperty @cert, 'LastStringResultLen', @iValue OUT

The length, in characters, of the string contained in the LastStringResult property.

top
OcspUrl
EXEC sp_OAGetProperty @cert, 'OcspUrl', @sValue OUT

If present in the certificate's extensions, returns the OCSP URL of the certificate. (The Online Certificate Status Protocol (OCSP) is an Internet protocol used for obtaining the revocation status of an X.509 digital certificate.)

More Information and Examples
Validate Certificate using OCSP Protocol
top
PrivateKeyExportable
EXEC sp_OAGetProperty @cert, 'PrivateKeyExportable', @iValue OUT

(Relevant only when running on a Microsoft Windows operating system.) Indicates whether the private key was installed with security settings that allow it to be re-exported.

More Information and Examples
Backup Windows Current User / Personal Certificates to a .zip
top
Revoked
EXEC sp_OAGetProperty @cert, 'Revoked', @iValue OUT

1 if the certificate or any certificate in the chain of authority has been revoked. This information is not available when running on Windows 95/98 computers. Note: If this property is 0, it could mean that it was not able to check the revocation status. Because of this uncertainty, a CheckRevoked method has been added. It returns an integer indicating one of three possible states: 1 (revoked) , 0 (not revoked), -1 (unable to check revocation status).

top
Rfc822Name
EXEC sp_OAGetProperty @cert, 'Rfc822Name', @sValue OUT

The RFC822 name of the certificate. (The RFC822 name is one part of the Subject Alternative Name extension of a certificate, if it exists. It is often the only part of the SAN.)

If the certificate contains a list of RFC822 names then this property will return the comma separated list of names.

Starting in Chilkat v9.5.0.85, to get the complete Subject Alternative Name extension as XML, use the SubjectAlternativeName property.

top
SelfSigned
EXEC sp_OAGetProperty @cert, 'SelfSigned', @iValue OUT

1 if this is a self-signed certificate, otherwise 0.

top
SerialDecimal
EXEC sp_OAGetProperty @cert, 'SerialDecimal', @sValue OUT
Introduced in version 9.5.0.75

The certificate's serial number as a decimal string.

top
SerialNumber
EXEC sp_OAGetProperty @cert, 'SerialNumber', @sValue OUT

The certificate's serial number as a hexidecimal string.

top
Sha1Thumbprint
EXEC sp_OAGetProperty @cert, 'Sha1Thumbprint', @sValue OUT

Hexidecimal string of the SHA-1 thumbprint for the certificate. (This is the SHA1 hash of the binary DER representation of the entire X.509 certificate.)

top
SignatureVerified
EXEC sp_OAGetProperty @cert, 'SignatureVerified', @iValue OUT

Returns 1 if the certificate and all certificates in the chain of authority have valid signatures, otherwise returns 0.

top
SmartCardFailReason
EXEC sp_OAGetProperty @cert, 'SmartCardFailReason', @iValue OUT
Introduced in version 10.1.0

Set after calling LoadFromSmartcard. Possible values are:

  • 0 - No failure. The call to LoadFromSmartcard was successful.
  • 1 - No smartcard or USB token was detected.
  • 2 - The SmartCardPin was provided and the PIN login failed. (Note: Chilkat only PIN authenticates if PKCS11 was the chosen means for communicating with the smartcard.)
  • 3 - The certificate matching the specified criteria was not found.
  • 99 - Some other undefined failure. Consult the LastErrorText for more information.

top
SmartCardNoDialog
EXEC sp_OAGetProperty @cert, 'SmartCardNoDialog', @iValue OUT
EXEC sp_OASetProperty @cert, 'SmartCardNoDialog', @iValue
Introduced in version 9.5.0.77

If set to 1, then no dialog will automatically popup if the SmartCardPin is incorrect. Instead, the method requiring the private key on the smart card will fail. The default value of this property is 0, which means that if the SmartCardPin property is incorrect, a dialog with prompt will be displayed.

top
SmartCardPin
EXEC sp_OAGetProperty @cert, 'SmartCardPin', @sValue OUT
EXEC sp_OASetProperty @cert, 'SmartCardPin', @sValue
Introduced in version 9.5.0.75

Can be set to the PIN value for a certificate / private key stored on a smart card.

More Information and Examples
Create CAdES-BES .p7m using Smart Card or USB TokenCreate XAdES using Smart Card or USB Token
top
SubjectAlternativeName
EXEC sp_OAGetProperty @cert, 'SubjectAlternativeName', @sValue OUT
Introduced in version 9.5.0.85

The subject alternative name (SAN) name of the certificate returned as XML. See the examples linked below.

More Information and Examples
Subject Alternative NameGet Certificate User Principal Name (UPN)
top
SubjectC
EXEC sp_OAGetProperty @cert, 'SubjectC', @sValue OUT

The certificate subject's country.

top
SubjectCN
EXEC sp_OAGetProperty @cert, 'SubjectCN', @sValue OUT

The certificate subject's common name.

top
SubjectDN
EXEC sp_OAGetProperty @cert, 'SubjectDN', @sValue OUT

The certificate subject's full distinguished name.

top
SubjectE
EXEC sp_OAGetProperty @cert, 'SubjectE', @sValue OUT

The certificate subject's email address.

top
SubjectKeyId
EXEC sp_OAGetProperty @cert, 'SubjectKeyId', @sValue OUT
Introduced in version 9.5.0.40

The subject key identifier of the certificate in base64 string format. This is only present if the certificate contains the extension OID 2.5.29.14.

top
SubjectL
EXEC sp_OAGetProperty @cert, 'SubjectL', @sValue OUT

The certificate subject's locality, which could be a city, count, township, or other geographic region.

top
SubjectO
EXEC sp_OAGetProperty @cert, 'SubjectO', @sValue OUT

The certificate subject's organization, which is typically the company name.

top
SubjectOU
EXEC sp_OAGetProperty @cert, 'SubjectOU', @sValue OUT

The certificate subject's organizational unit, which is the unit within the organization.

top
SubjectS
EXEC sp_OAGetProperty @cert, 'SubjectS', @sValue OUT

The certificate subject's state or province.

top
TrustedRoot
EXEC sp_OAGetProperty @cert, 'TrustedRoot', @iValue OUT

Returns 1 if the certificate has a trusted root authority, otherwise returns 0.

Note: As of version 9.5.0.41, the notion of what your application deems as trusted becomes more specific. The TrustedRoots class/object was added in v9.5.0.0. Prior to this, a certificate was considered to be anchored by a trusted root if the certificate chain could be established to a root (self-signed) certificate, AND if the root certificate was located somewhere in the Windows registry-based certificate stores. There are two problems with this: (1) it's a Windows-only solution. This property would always return 0 on non-Windows systems, and (2) it might be considered not a strong enough set of conditions for trusting a root certificate.

As of version 9.5.0.41, this property pays attention to the new TrustedRoots class/object, which allows for an application to specificallly indicate which root certificates are to be trusted. Certificates may be added to the TrustedRoots object via the LoadCaCertsPem or AddCert methods, and then activated by calling the TrustedRoots.Activate method. The activated trusted roots are deemed to be trusted in any Chilkat API method/property that needs to make this determination. In addition, the TrustedRoots object has a property named TrustSystemCaRoots, which defaults to 1, which allows for backward compatibility. It will trust CA certificates stored in the Windows registry-based certificate stores, or if on Linux, will trust certificates found in /etc/ssl/certs/ca-certificates.crt.

top
UncommonOptions
EXEC sp_OAGetProperty @cert, 'UncommonOptions', @sValue OUT
EXEC sp_OASetProperty @cert, 'UncommonOptions', @sValue
Introduced in version 9.5.0.87

This is a catch-all property to be used for uncommon needs. This property defaults to the empty string, and should typically remain empty. Possible comma separated keywords can include:

  • CheckRevokedCacheOnly: Added in v11.2.0. In the CheckRevoked method, prevents network access and only checks the cached revocation information on the local computer.

top
ValidFromStr
EXEC sp_OAGetProperty @cert, 'ValidFromStr', @sValue OUT

The date (in RFC822 string format) that this certificate becomes (or became) valid. It is a GMT/UTC date that is returned.

top
ValidToStr
EXEC sp_OAGetProperty @cert, 'ValidToStr', @sValue OUT

The date (in RFC822 string format) that this certificate expires. It is a GMT/UTC date that is returned.

top
VerboseLogging
EXEC sp_OAGetProperty @cert, 'VerboseLogging', @iValue OUT
EXEC sp_OASetProperty @cert, 'VerboseLogging', @iValue

If set to 1, then the contents of LastErrorText (or LastErrorXml, or LastErrorHtml) may contain more verbose information. The default value is 0. Verbose logging should only be used for debugging. The potentially large quantity of logged information may adversely affect peformance.

top
Version
EXEC sp_OAGetProperty @cert, 'Version', @sValue OUT

Version of the component/library, such as "10.1.0"

More Information and Examples
How to get the Chilkat version at runtime.
top

Methods

BuildCertChain
EXEC sp_OAMethod @cert, 'BuildCertChain', @success OUT, @certChain
Introduced in version 11.0.0

Builds the certificate chain in certChain containing all the certificates (including this one), in the chain of authentication to the trusted root (if possible). If this certificate object was loaded from a PFX, then the certiicates contained in the PFX are automatically available for building the certificate chain. The UseCertVault method can be called to provide additional certificates that might be required to build the cert chain. Finally, the TrustedRoots object can be used to provide a way of making trusted root certificates available.

Note: Returns 1 even if certificate chain is not completed to the root. The certificate chain's ReachesRoot property can be examined to see if the chain was completed to the root.

On Windows systems, the registry-based certificate stores are automatically consulted if needed to locate intermediate or root certificates in the chain. Chilkat searches certificate stores in the following order. See System Store Locations for more information.

  1. Current-User CA Certificate Store
  2. Local-Machine CA Certificate Store
  3. Current-User Root Certificate Store
  4. Local-Machine Root Certificate Store
  5. Current-User MY Certificate Store
  6. Local-Machine MY Certificate Store
  7. Current-User ADDRESSBOOK Certificate Store (if it exists)
  8. Local-Machine ADDRESSBOOK Certificate Store (if it exists)

Returns 1 for success, 0 for failure.

top
CheckRevoked
EXEC sp_OAMethod @cert, 'CheckRevoked', @iResult OUT

Returns 1 if the certificate has been revoked, 0 if not revoked, and -1 if unable to check the revocation status.

Note: This method is only implemented on Windows systems. It uses the underlying Microsoft CertVerifyRevocation Platform SDK function to check the revocation status of a certificate. (Search CertVerifyRevocation to get information about it.) Starting in v11.2.0, the keyword "CheckRevokedCacheOnly" can be included in the UncommonOptions to prevent the Microsoft revocation handler from accessing any network based resources for revocation checking. In other words, just check the cached revocation information on the local computer.

Non-Windows (and Windows) applications can send an OCSP request as shown in the example below.

More Information and Examples
Validate Certificate using OCSP Protocol
top
CheckSmartCardPin
EXEC sp_OAMethod @cert, 'CheckSmartCardPin', @iResult OUT
Introduced in version 9.5.0.77

Verifies that the SmartCardPin property setting is correct. Returns 1 if correct, 0 if incorrect, and -1 if unable to check because the underlying CSP does not support the functionality.

More Information and Examples
Validate a Smartcard PIN
top
ExportCertDerBd
EXEC sp_OAMethod @cert, 'ExportCertDerBd', @success OUT, @cerData
Introduced in version 9.5.0.70

Exports the digital certificate in ASN.1 DER format to a BinData object.

Returns 1 for success, 0 for failure.

More Information and Examples
Backup Windows Current User / Personal Certificates to a .zip
top
ExportCertDerFile
EXEC sp_OAMethod @cert, 'ExportCertDerFile', @success OUT, @path

Exports the digital certificate to ASN.1 DER format binary file.

Returns 1 for success, 0 for failure.

top
ExportCertPem
EXEC sp_OAMethod @cert, 'ExportCertPem', @sResult OUT

Exports the digital certificate to an unencrypted PEM formatted string.

Returns NULL on failure

More Information and Examples
Convert Cert + Key to PEM and PFX
top
ExportCertPemFile
EXEC sp_OAMethod @cert, 'ExportCertPemFile', @success OUT, @path

Exports the digital certificate to an unencrypted PEM formatted file.

Returns 1 for success, 0 for failure.

More Information and Examples
Convert Cert + Key to PEM and PFX
top
ExportCertXml
EXEC sp_OAMethod @cert, 'ExportCertXml', @sResult OUT

Exports a certificate to an XML format where the XML tags are the names of the ASN.1 objects that compose the X.509 certificate. Binary data is either hex or base64 encoded. (The binary data for a bits ASN.1 tag is hex encoded, whereas for all other ASN.1 tags, such as octets, it is base64.)

Returns NULL on failure

More Information and Examples
Get Certificate As XML (and gets Signature Algorithm Identifier)
top
ExportToPfxBd
EXEC sp_OAMethod @cert, 'ExportToPfxBd', @success OUT, @password, @includeCertChain, @pfxData
Introduced in version 9.5.0.70

Exports the certificate and private key (if available) to pfxData. The password is what will be required to access the PFX contents at a later time. If includeCertChain is 1, then the certificates in the chain of authority are also included in the PFX.

Returns 1 for success, 0 for failure.

More Information and Examples
Backup Windows Current User / Personal Certificates to a .zip
top
ExportToPfxFile
EXEC sp_OAMethod @cert, 'ExportToPfxFile', @success OUT, @pfxFilename, @pfxPassword, @bIncludeCertChain

Exports the certificate and private key (if available) to a PFX (.pfx or .p12) file. The output PFX is secured using the pfxPassword. If bIncludeCertChain is 1, then the certificates in the chain of authority are also included in the PFX output file.

Returns 1 for success, 0 for failure.

More Information and Examples
Convert Cert + Key to PEM and PFX
top
FindIssuer
EXEC sp_OAMethod @cert, 'FindIssuer', @cert OUT -- returns a ChilkatCert object token

Finds and returns the issuer certificate. If the certificate is a root or self-issued, then the certificate returned is a copy of the caller certificate. (The IsRoot property can be check to see if the certificate is a root (or self-issued) certificate.)

Returns NULL on failure

More Information and Examples
Get Certificates from .p12 / .pfx
top
GetEncoded
EXEC sp_OAMethod @cert, 'GetEncoded', @sResult OUT

Returns a base64 encoded string representation of the certificate's binary DER format, which can be passed to SetFromEncoded to recreate the certificate object.

Returns NULL on failure

top
GetExtensionAsText
EXEC sp_OAMethod @cert, 'GetExtensionAsText', @sResult OUT, @oid
Introduced in version 9.5.0.89

Returns the certificate extension data as a string. This method should only be called for those extensions with text values NOT stored as binary ASN.1. In most cases, applications should call GetExtensionAsXml because most extensions contain ASN.1 values that need to be decoded..

Returns NULL on failure

More Information and Examples
Get Text Certificate Extensions by OID
top
GetExtensionAsXml
EXEC sp_OAMethod @cert, 'GetExtensionAsXml', @sResult OUT, @oid
Introduced in version 9.5.0.49

Returns the certificate extension data in XML format (converted from ASN.1). The oid is an OID, such as the ones listed here: http://www.alvestrand.no/objectid/2.5.29.html

Note: In many cases, the data within the XML is returned base64 encoded. An application may need to take one further step to base64 decode the information contained within the XML.

Returns NULL on failure

More Information and Examples
Get Certificate Authority Information AccessGet Certificate Policy
top
GetExtensionBd
EXEC sp_OAMethod @cert, 'GetExtensionBd', @success OUT, @oid, @bd
Introduced in version 9.5.0.96

Returns the certificate extension data specified by oid in bd.

Returns 1 for success, 0 for failure.

More Information and Examples
Binary Certificate Extension Data
top
GetIssuer
EXEC sp_OAMethod @cert, 'GetIssuer', @success OUT, @issuer
Introduced in version 11.0.0

Finds and returns the issuer certificate in issuer. If the certificate is a root or self-issued, then the certificate returned is a copy of the caller certificate. (The IsRoot property can be check to see if the certificate is a root (or self-issued) certificate.)

Returns 1 for success, 0 for failure.

top
GetPrivateKey
EXEC sp_OAMethod @cert, 'GetPrivateKey', @success OUT, @privKey
Introduced in version 11.0.0

Loads the privKey with the certificate's private key, if it exists. Returns 0 if the certificate has no associated private key.

Returns 1 for success, 0 for failure.

top
GetPrivateKeyPem
EXEC sp_OAMethod @cert, 'GetPrivateKeyPem', @sResult OUT

Exports the certificate's private key to a PEM string (if the private key is available).

Returns NULL on failure

top
GetPubKeyDer
EXEC sp_OAMethod @cert, 'GetPubKeyDer', @success OUT, @preferPkcs1, @bd
Introduced in version 9.5.0.92

Loads the bd with the certificate's public key in ASN.1 DER format. If the key type (such as RSA) supports both PKCS1 and PKCS8 formats, then preferPkcs1 selects which format to return.

Returns 1 for success, 0 for failure.

top
GetPublicKey
EXEC sp_OAMethod @cert, 'GetPublicKey', @success OUT, @pubKey
Introduced in version 11.0.0

Loads the pubKey with the certificate's public key.

Returns 1 for success, 0 for failure.

top
GetSignature
EXEC sp_OAMethod @cert, 'GetSignature', @success OUT, @bd
Introduced in version 9.5.0.92

Loads the bd with the certificate's signature.

Returns 1 for success, 0 for failure.

top
GetSpkiFingerprint
EXEC sp_OAMethod @cert, 'GetSpkiFingerprint', @sResult OUT, @hashAlg, @encoding
Introduced in version 9.5.0.55

Returns the SPKI Fingerprint suitable for use in pinning. (See RFC 7469.) An SPKI Fingerprint is defined as the output of a known cryptographic hash algorithm whose input is the DER-encoded ASN.1 representation of the Subject Public Key Info (SPKI) of an X.509 certificate. The hashAlg specifies the hash algorithm and may be sha256, sha384, sha512, sha1, md2, md5, haval, ripemd128, ripemd160,ripemd256, or ripemd320. The encoding specifies the encoding, and may be base64, hex, or any of the encoding modes specified in the article at the link below.

Returns NULL on failure

More Information and Examples
List of Chilkat Encoding ModesSPKI Fingerprint
top
GetSubjectPart
EXEC sp_OAMethod @cert, 'GetSubjectPart', @sResult OUT, @partNameOrOid
Introduced in version 9.5.0.85

Returns a part of the certificate's subject by name or OID. The partNameOrOid can be a part name, such as CN, O, OU, E, S, L, C, or SERIALNUMBER, or it can be an OID such as 2.5.4.3.

Returns NULL on failure

More Information and Examples
Get Certificate Subject Part by Name or OID
top
HashOf
EXEC sp_OAMethod @cert, 'HashOf', @sResult OUT, @part, @hashAlg, @encoding
Introduced in version 9.5.0.75

Returns an encoded hash of a particular part of the certificate. The part may be one of the following:

  • IssuerDN
  • IssuerPublicKey
  • SubjectDN
  • SubjectPublicKey

The hashAlg is the name of the hash algorithm, such as sha1, sha256, sha384, sha512, md5, etc. The encoding is the format to return, such as hex, base64, etc.

Returns NULL on failure

top
HasPrivateKey
EXEC sp_OAMethod @cert, 'HasPrivateKey', @success OUT

Returns 1 if a private key associated with the certificate is available.

top
IsEcdsa
EXEC sp_OAMethod @cert, 'IsEcdsa', @success OUT
Introduced in version 10.1.0

Returns 1 if certificate is ECDSA-based (i.e. has an ECDSA key).

top
IsRsa
EXEC sp_OAMethod @cert, 'IsRsa', @success OUT
Introduced in version 10.1.0

Returns 1 if certificate is RSA-based (i.e. has an RSA key).

top
LinkPkcs11
EXEC sp_OAMethod @cert, 'LinkPkcs11', @success OUT, @session
Introduced in version 9.5.0.96

Links to the certificate's private key located on an HSM (smart card, token, or cloud HSM). Once linked, the certificate can be used for signing where the signing occurs on the HSM. See the example below for more detailed information.

Returns 1 for success, 0 for failure.

More Information and Examples
Sign PDF in the Cloud using AWS CloudHSMSign XML (XAdES) using AWS CloudHSM
top
LoadByCommonName
EXEC sp_OAMethod @cert, 'LoadByCommonName', @success OUT, @cn

This method searches for a certificate with the specified common name (CN) on different operating systems. - On Windows, it searches the Local Machine and Current User certificate stores. - On macOS and iOS, it searches the Default and System Keychains. If the certificate is found, it is loaded and ready for use.

Note: Chilkat v10.0.0 or later is needed for Apple Keychain.

Returns 1 for success, 0 for failure.

More Information and Examples
Windows Certificate Stores - Find Certificate by Common NameCreate P7M Using Pre-Installed Windows Certificate
top
LoadByEmailAddress
EXEC sp_OAMethod @cert, 'LoadByEmailAddress', @success OUT, @emailAddress

This method searches for a certificate with the specified email address on different operating systems. - On Windows, it searches the Local Machine and Current User certificate stores. - On macOS and iOS, it searches the Default and System Keychains. If the certificate is found, it is loaded and ready for use.

Note: Chilkat v10.0.0 or later is needed for Apple Keychain.

Returns 1 for success, 0 for failure.

More Information and Examples
Select Cert for Encrypted Email
top
LoadByIssuerAndSerialNumber
EXEC sp_OAMethod @cert, 'LoadByIssuerAndSerialNumber', @success OUT, @issuerCN, @serialNumber

This method searches for a certificate with the specified issuer common name (issuerCN) and serial number (serialNumber) on different operating systems. - On Windows, it searches the Local Machine and Current User certificate stores. - On macOS and iOS, it searches the Default and System Keychains. If the certificate is found, it is loaded and ready for use.

Note: Chilkat v10.0.0 or later is needed for Apple Keychain.

Returns 1 for success, 0 for failure.

top
LoadBySubjectOid
EXEC sp_OAMethod @cert, 'LoadBySubjectOid', @success OUT, @oid, @value
Introduced in version 9.5.0.85

This method searches for a certificate containing a subject part matching the oid and value. On Windows, it searches the Local Machine and Current User certificate stores. - On macOS and iOS, it searches the Default and System Keychains. If the certificate is found, it is loaded and ready for use.

Note: Chilkat v10.0.0 or later is needed for Apple Keychain.

Returns 1 for success, 0 for failure.

More Information and Examples
Common Certificate Subject OIDs
top
LoadByThumbprint
EXEC sp_OAMethod @cert, 'LoadByThumbprint', @success OUT, @thumbprint, @encoding
Introduced in version 9.5.0.83

This method searches for a certificate with the specified thumbprint. The thumbprint is passed as a string using the encoding specified by encoding (such as base64, hex, etc.).

On Windows, it searches the Local Machine and Current User certificate stores. - On macOS and iOS, it searches the Default and System Keychains. If the certificate is found, it is loaded and ready for use.

Note: Chilkat v10.0.0 or later is needed for Apple Keychain.

Returns 1 for success, 0 for failure.

More Information and Examples
Find and Load Certificate by Thumbprint
top
LoadFromBase64
EXEC sp_OAMethod @cert, 'LoadFromBase64', @success OUT, @encodedCert

Loads an ASN.1 or DER encoded certificate represented in a Base64 string.

Returns 1 for success, 0 for failure.

More Information and Examples
Get a Certificate's Key SizeDuplicate Java Verify RSA Signature
top
LoadFromBd
EXEC sp_OAMethod @cert, 'LoadFromBd', @success OUT, @certBytes
Introduced in version 9.5.0.70

Loads an X.509 certificate from data contained in certBytes.

Note: The certBytes may contain the certificate in any format. It can be binary DER (ASN.1), PEM, Base64, etc. Chilkat will automatically detect the format.

Returns 1 for success, 0 for failure.

More Information and Examples
Download and Trust the DigiCert Global Root CA
top
LoadFromFile
EXEC sp_OAMethod @cert, 'LoadFromFile', @success OUT, @path

Loads a certificate from a .cer, .crt, .p7b, or .pem file. This method accepts certificates from files in any of the following formats:
1. DER encoded binary X.509 (.CER)
2. Base-64 encoded X.509 (.CER)
3. Cryptographic Message Syntax Standard - PKCS #7 Certificates (.P7B)
4. PEM format
This method decodes the certificate based on the contents if finds within the file, and not based on the file extension. If your certificate is in a file having a different extension, try loading it using this method before assuming it won't work. This method does not load .p12 or .pfx (PKCS #12) files.

Returns 1 for success, 0 for failure.

top
LoadFromSmartcard
EXEC sp_OAMethod @cert, 'LoadFromSmartcard', @success OUT, @certSpec
Introduced in version 9.5.0.77

Important: It's important to set the SmartCardPin property before calling this method.

Loads a certificate and its associated private key (if available) from a connected USB token or smart card. This function works on Windows, Linux, MacOS, iOS, and Alpine Linux. If multiple USB tokens and/or smartcards are connected to the system, then each will be searched and the first matching certificate will be loaded.

The certSpec can have the following formats and values:

  • Empty String: Passing the empty string is the most common way to call this function. Pass the empty string when only one HSM (USB token or smart card) is connected to the system, and it contains only one certificate with a private key. The HSM can contain other certificates, but Chilkat will automatically choose and load the certificate that has the associated private key also located on the HSM.
  • cn=<cert subject common name>: Finds and loads the certificate having the specified subject common name (CN).
  • subjectdn_withtags=<distinguished name with tags>: Finds and loads the certificate having the specified DN with tags. For example, subjectdn_withtags=C=US, O=Example Corporation, OU=IT Department, CN=www.example.com
  • subjectdn=<distinguished name without tags>: Finds and loads the certificate having the specified DN without tags. For example, subjectdn=US, California, San Francisco, Example Corp, Engineering, www.example.com, support@example.com
  • issuercn=<subject hex serial:issuer common name>: Finds and loads the certificate having the specified hex serial number and an issuer with the specified common name. For example, 01020304: Let's Encrypt Authority X1
  • serial=<hex serial number>: Finds and loads the certificate having the specified hexidecimal serial number.
  • thumbprint=<hex SHA1 thumbprint>: Finds and loads the certificate having the specified hexidecimal SHA1 thumbprint.
  • policyoid=<certificate policy OID>: Finds and loads the certificate having a policy OID equal to the specified value, such as 2.16.840.1.101.2.1.11.39.
  • o=<subject organization>: Finds and loads the certificate having the O subject part equal to the specified value.
  • c=<subject country>: Finds and loads the certificate having the C subject part equal to the specified value.
  • l=<subject locality>: Finds and loads the certificate having the L subject part equal to the specified value.
  • ou=<subject organizational unit>: Finds and loads the certificate having the OU subject part equal to the specified value.
  • st=<subject state>: Finds and loads the certificate having the ST subject part equal to the specified value.
  • e=<subject email>: Finds and loads the certificate having the E subject part equal to the specified value.

On Windows systems, certSpec can specify a CSP (Cryptographic Service Provider) name, such as YubiHSM Key Storage Provider. This option is retained for legacy support, as Chilkat initially handled USB tokens and smartcards only on Windows via CSPs. Chilkat now supports HSMs across multiple platforms (Windows, Linux, macOS, iOS, Alpine Linux) and uses various methods to communicate with HSMs, including PKCS11, macOS Keychain services, Windows ScMinidriver, MsCNG, and legacy Windows CryptoAPI. Chilkat automatically detects the HSM make/model and selects the optimal communication method.

Some possible CSP values are:

  • Microsoft Smart Card Key Storage Provider
  • Microsoft Base Smart Card Crypto Provider
  • Bit4id Universal Middleware Provider
  • YubiHSM Key Storage Provider (starting in v9.5.0.83)
  • SafeSign Standard Cryptographic Service Provider
  • SafeSign Standard RSA and AES Cryptographic Service Provider
  • eToken Base Cryptographic Provider
  • cryptoCertum3 CSP
  • FTSafe ePass1000 RSA Cryptographic Service Provider
  • SecureStoreCSP
  • EnterSafe ePass2003 CSP v2.0
  • Gemalto Classic Card CSP
  • PROXKey CSP India V1.0
  • PROXKey CSP India V2.0
  • TRUST KEY CSP V1.0
  • Watchdata Brazil CSP V1.0
  • Luna Cryptographic Services for Microsoft Windows
  • Luna SChannel Cryptographic Services for Microsoft Windows
  • Safenet RSA Full Cryptographic Provider
  • nCipher Enhanced Cryptographic Provider
  • MySmartLogon NFC CSP
  • NFC Connector Enterprise
  • ActivClient Cryptographic Service Provider
  • EnterSafe ePass2003 CSP v1.0
  • Oberthur Card Systems Cryptographic Provider
  • Athena ASECard Crypto CSP
  • many others...

Returns 1 for success, 0 for failure.

More Information and Examples
Load Default Certificate from Smart Card or USB TokenLoad Certificate from Smart Card by Common NameLoad Certificate from Smart Card by Serial NumberLoad Certificate from Smartcard in Reader (or from USB Token)Sign String to create a CAdES-T Signature
top
LoadPem
EXEC sp_OAMethod @cert, 'LoadPem', @success OUT, @strPem
Introduced in version 9.5.0.49

Loads the certificate from a PEM string.

Returns 1 for success, 0 for failure.

More Information and Examples
Load Certificate from PEM and Access the Public Key
top
LoadPfxBd
EXEC sp_OAMethod @cert, 'LoadPfxBd', @success OUT, @pfxData, @password
Introduced in version 9.5.0.70

Loads the certificate from the PFX contained in pfxData. Note: If the PFX contains multiple certificates, the 1st certificate in the PFX is loaded.

Returns 1 for success, 0 for failure.

top
LoadPfxFile
EXEC sp_OAMethod @cert, 'LoadPfxFile', @success OUT, @pfxPath, @password

Loads a PFX file. Note: If the PFX contains multiple certificates, the 1st certificate in the PFX is loaded.

Returns 1 for success, 0 for failure.

top
PemFileToDerFile
EXEC sp_OAMethod @cert, 'PemFileToDerFile', @success OUT, @fromPath, @toPath

Converts a PEM file to a DER file.

Returns 1 for success, 0 for failure.

top
SaveToFile
EXEC sp_OAMethod @cert, 'SaveToFile', @success OUT, @path

Saves a certificate object to a .cer file.

Returns 1 for success, 0 for failure.

top
SetCloudSigner
EXEC sp_OAMethod @cert, 'SetCloudSigner', @success OUT, @json
Introduced in version 9.5.0.96

Provides information for a cloud signing service to do the signing via a remote signing server. Current supported services are AWS KMS, Azure Key Vault, and ARSS (Aruba Remote Signing Service). See the examples below.

Returns 1 for success, 0 for failure.

More Information and Examples
Sign PDF in the Cloud using AWS KMSSign PDF in the Cloud using Azure Key VaultSign PDF using ARSS (Aruba Remote Signing Service)
top
SetFromEncoded
EXEC sp_OAMethod @cert, 'SetFromEncoded', @success OUT, @encodedCert

Initializes the certificate object from a base64 encoded string representation of the certificate's binary DER format.

Returns 1 for success, 0 for failure.

top
SetPrivateKey
EXEC sp_OAMethod @cert, 'SetPrivateKey', @success OUT, @privKey

Used to associate a private key with the certificate for subsequent (PKCS7) signature creation or decryption.

Returns 1 for success, 0 for failure.

More Information and Examples
Load Certificate (.cer) and Private Key (.pvk) generated from makecert
top
SetPrivateKeyPem
EXEC sp_OAMethod @cert, 'SetPrivateKeyPem', @success OUT, @privKeyPem

Same as SetPrivateKey, but the key is provided in unencrypted PEM format. (Note: The privKeyPem is not a file path, it is the actual PEM text.)

Returns 1 for success, 0 for failure.

More Information and Examples
Load Certificate from .cer and Private Key from .pem
top
Unload
EXEC sp_OAMethod @cert, 'Unload', @success OUT
Introduced in version 11.0.0

Unload the certificate, which could mean closing a PKCS11 session if the certificate was loaded from a smart card.

top
UploadToCloud
EXEC sp_OAMethod @cert, 'UploadToCloud', @success OUT, @jsonIn, @jsonOut
Introduced in version 9.5.0.96

This is an open-ended method to accomodate uploading the private key to a cloud service, such as AWS KMS, or Azure Key Vault. For details, see the examples below.

Returns 1 for success, 0 for failure.

More Information and Examples
Easy Method to Import Certificate to Azure Key VaultAWS KMS Import PFX Key
top
UseCertVault
EXEC sp_OAMethod @cert, 'UseCertVault', @success OUT, @vault
Introduced in version 9.5.0.40

Adds an XML certificate vault to the object's internal list of sources to be searched for certificates for help in building certificate chains and verifying the certificate signature to the trusted root.

Returns 1 for success, 0 for failure.

More Information and Examples
PKCS11 Certificate Chain
top
VerifySignature
EXEC sp_OAMethod @cert, 'VerifySignature', @success OUT
Introduced in version 9.5.0.40

Verifies the certificate signature, as well as the signatures of all certificates in the chain of authentication to the trusted root. Returns 1 if all signatures are verified to the trusted root. Otherwise returns 0.

top
X509PKIPathv1
EXEC sp_OAMethod @cert, 'X509PKIPathv1', @sResult OUT
Introduced in version 9.5.0.77

Returns the base64 representation of an X509PKIPathv1 containing just the calling certificate. This is typically used in an X.509 Binary Security Token. It is a PKIPath that contains an ordered list of X.509 public certificates packaged in a PKIPath. The X509PKIPathv1 token type may be used to represent a certificate path. (This is sometimes used in XAdES signatures.)

Returns NULL on failure

More Information and Examples
Create X509PKIPathv1 Containing one Certificate
top

Deprecated

ExportPrivateKey
EXEC sp_OAMethod @cert, 'ExportPrivateKey', @privateKey OUT -- returns a PrivateKey object token
This method is deprecated.

Exports the certificate's private key.

Applications should call GetPrivateKey instead.

Returns NULL on failure

More Information and Examples
Transition from Cert.ExportPrivateKey to Cert.GetPrivateKey
top
ExportPublicKey
EXEC sp_OAMethod @cert, 'ExportPublicKey', @publicKey OUT -- returns a PublicKey object token
This method is deprecated.

Exports the certificate's public key.

Applications should call GetPublicKey instead.

Returns NULL on failure

More Information and Examples
Transition from Cert.ExportPublicKey to Cert.GetPublicKeyGet RSA Key Modulus from .cer or .key
top
GetCertChain
EXEC sp_OAMethod @cert, 'GetCertChain', @certChain OUT -- returns a ChilkatCertChain object token
Introduced in version 9.5.0.40
This method is deprecated and replaced by BuildCertChain

This method is deprecated. Applications should call BuildCertChain instead.

Returns a certficate chain object containing all the certificates (including this one), in the chain of authentication to the trusted root (if possible). If this certificate object was loaded from a PFX, then the certiicates contained in the PFX are automatically available for building the certificate chain. The UseCertVault method can be called to provide additional certificates that might be required to build the cert chain. Finally, the TrustedRoots object can be used to provide a way of making trusted root certificates available.

Note: Prior to v9.5.0.50, this method would fail if the certificate chain could not be completed to the root. Starting in v9.5.0.50, the incomplete certificate chain will be returned. The certificate chain's ReachesRoot property can be examined to see if the chain was completed to the root.

On Windows systems, the registry-based certificate stores are automatically consulted if needed to locate intermediate or root certificates in the chain. Chilkat searches certificate stores in the following order. See System Store Locations for more information.

  1. Current-User CA Certificate Store
  2. Local-Machine CA Certificate Store
  3. Current-User Root Certificate Store
  4. Local-Machine Root Certificate Store
  5. Current-User MY Certificate Store
  6. Local-Machine MY Certificate Store
  7. Current-User ADDRESSBOOK Certificate Store (if it exists)
  8. Local-Machine ADDRESSBOOK Certificate Store (if it exists)

Returns NULL on failure

More Information and Examples
Transition from Cert.GetCertChain to Cert.BuildCertChainGet the Server Certificate, Certificate Chain, and Root CA Certificate
top
GetValidFromDt
EXEC sp_OAMethod @cert, 'GetValidFromDt', @ckDateTime OUT -- returns a CkDateTime object token
This method is deprecated.

Returns the date/time this certificate becomes (or became) valid.

This method is deprecated and will be removed in a future version. Applications should use the ValidFromStr property.

Returns NULL on failure

More Information and Examples
Transition from Cert.GetValidFromDt to Cert.ValidFromStrGet Certificate Valid To/From Dates
top
GetValidToDt
EXEC sp_OAMethod @cert, 'GetValidToDt', @ckDateTime OUT -- returns a CkDateTime object token
This method is deprecated.

Returns the date/time this certificate becomes (or became) invalid.

This method is deprecated and will be removed in a future version. Applications should use the ValidToStr property.

Returns NULL on failure

More Information and Examples
Transition from Cert.GetValidToDt to Cert.ValidToStrGet Certificate Valid To/From Dates
top