This page provides a guide to Establishing a Connection to MariaDB in CData Cloud, as well as information on the available resources, and a reference to the available connection properties.

Connecting to MariaDB

Establishing a Connection shows how to authenticate to MariaDB and configure any necessary connection properties to create a database in CData Cloud

Accessing Data from CData Cloud Services

Accessing data from MariaDB through the available standard services and CData Cloud administration is documented in further details in the CData Cloud Documentation.

Connect to MariaDB by selecting the corresponding icon in the Database tab. Required properties are listed under Settings. The Advanced tab lists connection properties that are not typically required.

Connecting to MariaDB

The Server and Port properties must be set to a MariaDB server. Optionally, Database can be set to connect to a specific database. If not set, the Cloud reports tables from all databases.

Azure AD

Azure AD is Microsoft’s multi-tenant, cloud-based directory and identity management service. It is user-based authentication that requires that you set AuthScheme to AzureAD.

When the access token expires, the Cloud refreshes it automatically.

LDAP

To authenticate as an LDAP user, set AuthScheme to LDAP.

Your LDAP credentials are auto-detected by default. If you want to designate a different user and account, set the following optional properties:

• User: The user to login as.
• Password: The user's password.

Creating a Custom OAuth Application

CData embeds OAuth Application Credentials with CData branding that can be used when connecting to MariaDB via a desktop application or a headless machine.

However, you must create a custom OAuth application to connect to MariaDB via the Web. And since custom OAuth applications seamlessly support all three commonly-used auth flows, you might want to create custom OAuth applications (use your own OAuth Application Credentials) for those auth flows anyway.

Custom OAuth applications are useful if you want to:

• control branding of the authentication dialog;
• control the redirect URI that the application redirects the user to after the user authenticates; or
• customize the permissions that you are requesting from the user.

Procedure

The following procedure creates a custom OAuth application, registers that application in the Azure portal, and obtains the OAuthClientId and OAuthClientSecret.

1. Log in to https://portal.azure.com.
2. In the left-hand navigation pane, select Azure Active Directory, and then App Registrations.
3. Click New registration.
4. Enter a name for your new application.

5. Specify the desired setup: single-tenant or multi-tenant.

   • If your application is for private use only, Accounts in this organizational directory only (single-tenant; default) should be sufficient. Note that if you specify a single-tenant setup you must set the AzureTenant connection property to the Id of the AzureTenant when establishing a connection with the CData Cloud. Otherwise, the authentication attempt will fail with an error.
   • If you want to distribute your app, choose one of the multi-tenant options.

6. Set the application's redirect url to something like http://localhost:33333, the driver's default, OR set a different port of your choice. If you set a different port, be sure to set CallbackURL to the exact reply URL you defined.
7. To register the new application, click Register. The Azure Portal displays the App Management screen. Note the value in Application (client) ID as the OAuthClientId, and the Directory (tenant) ID as the AzureTenant.
8. To define the application authentication type, go to the Certificates & Secrets section.

9. Choose your preferred type of authentication:

   • Upload a certificate (recommended): In the Certificates & Secrets section, select Upload certificate and select the certificate to upload from your local machine.
   • Create a new application secret: In the Certificates & Secrets section, select New Client Secret for the application, and specify its duration. After saving the client secret, the key value is displayed. This value is displayed only once, so be sure to copy it for future use. (This value is used as the OAuthClientSecret.)

10. Select API Permissions, then click Add.
11. Specify the Delegated permissions.
12. Save your changes.

If you have selected to use permissions that require admin consent, you may grant them from the current tenant on the Azure Portal's API Permissions page.

Customizing the SSL Configuration

By default, the Cloud attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.

To specify another certificate, see the SSLServerCert property for the available formats to do so.

Client SSL Certificates

The MariaDB Cloud also supports setting client certificates. Set the following to connect using a client certificate.

• SSLClientCert: The name of the certificate store for the client certificate.
• SSLClientCertType: The type of key store containing the TLS/SSL client certificate.
• SSLClientCertPassword: The password for the TLS/SSL client certificate.
• SSLClientCertSubject: The subject of the TLS/SSL client certificate.

Connecting Through a Firewall or Proxy

Set the following properties:

• To use a proxy-based firewall, set FirewallType, FirewallServer, and FirewallPort.
• To tunnel the connection, set FirewallType to TUNNEL.
• To authenticate, specify FirewallUser and FirewallPassword.
• To authenticate to a SOCKS proxy, additionally set FirewallType to SOCKS5.

The connection string properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure in the connection string for this provider. Click the links for further details.

For more information on establishing a connection, see Establishing a Connection.

Authentication

---

Property	Description
AuthScheme	The scheme used for authentication. Accepted entries are Password, AzureAD, AzurePassword, AzureMSI, and LDAP.
Server	The host name or IP address of the server.
Port	The port of the MariaDB server.
User	The MariaDB user account used to authenticate.
Password	The password used to authenticate the user.
Database	The name of the MariaDB database.
Domain	The name of the domain for a Windows (NTLM) security login.
UseSSL	This field sets whether SSL is enabled.
NTLMVersion	The NTLM version.

Azure Authentication

---

Property	Description
AzureTenant	The Microsoft Online tenant being used to access data. If not specified, your default tenant is used.

SSL

---

Property	Description
SSLServerCert	The certificate to be accepted from the server when connecting using TLS/SSL.

SSH

---

Property	Description
SSHAuthMode	The authentication method used when establishing an SSH Tunnel to the service.
SSHClientCert	A certificate to be used for authenticating the SSHUser.
SSHClientCertPassword	The password of the SSHClientCert key if it has one.
SSHClientCertSubject	The subject of the SSH client certificate.
SSHClientCertType	The type of SSHClientCert private key.
SSHServer	The SSH server.
SSHPort	The SSH port.
SSHUser	The SSH user.
SSHPassword	The SSH password.
SSHServerFingerprint	The SSH server fingerprint.
UseSSH	Whether to tunnel the MariaDB connection over SSH. Use SSH.

Logging

---

Property	Description
Verbosity	The verbosity level that determines the amount of detail included in the log file.

Schema

---

Property	Description
BrowsableSchemas	This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.

Miscellaneous

---

Property	Description
AllowUserVariables	When set to True, user variables (prefixed by an @) can be used in SQL queries.
MaxRows	Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
ReplaceInvalidDatesWithNull	Whether or not to return an invalid Date value as NULL.
ServerTimeZone	Specify a specific server time zone id of current platform(.Net or Java) by user.
Timeout	The value in seconds until the connection timeout error is thrown.
ZeroDatesToNull	Whether or not to return Date and DateTime values consisting of all zeros as NULL.

This section provides a complete list of the Authentication properties you can configure in the connection string for this provider.

---

Property	Description
AuthScheme	The scheme used for authentication. Accepted entries are Password, AzureAD, AzurePassword, AzureMSI, and LDAP.
Server	The host name or IP address of the server.
Port	The port of the MariaDB server.
User	The MariaDB user account used to authenticate.
Password	The password used to authenticate the user.
Database	The name of the MariaDB database.
Domain	The name of the domain for a Windows (NTLM) security login.
UseSSL	This field sets whether SSL is enabled.
NTLMVersion	The NTLM version.

The scheme used for authentication. Accepted entries are Password, AzureAD, AzurePassword, AzureMSI, and LDAP.

Data Type

string

Default Value

"Password"

Remarks

The scheme used for authentication. Accepted entries are Password, AzureAD, AzurePassword, AzureMSI, and LDAP.

The host name or IP address of the server.

Data Type

string

Default Value

""

Remarks

The host name or IP of the server hosting the MariaDB database.

The port of the MariaDB server.

Data Type

string

Default Value

"3306"

Remarks

The port of the server hosting the MariaDB database.

The MariaDB user account used to authenticate.

Data Type

string

Default Value

""

Remarks

Together with Password, this field is used to authenticate against the MariaDB server.

The password used to authenticate the user.

Data Type

string

Default Value

""

Remarks

The User and Password are together used to authenticate with the server.

The name of the MariaDB database.

Data Type

string

Default Value

""

Remarks

The default database to connect to when connecting to the MariaDB Server. If this is not set, tables from all databases will be returned.

The name of the domain for a Windows (NTLM) security login.

Data Type

string

Default Value

""

Remarks

By default, the Cloud uses the domain of the PC it is running on or the domain used by the machine running the MariaDB instance.

This field sets whether SSL is enabled.

Data Type

bool

Default Value

true

Remarks

This field sets whether the Cloud will attempt to negotiate TLS/SSL connections to the server. By default, the Cloud checks the server's certificate against the system's trusted certificate store. To specify another certificate, set SSLServerCert.

The NTLM version.

Possible Values

1, 2

Data Type

string

Default Value

"1"

Remarks

This property specifies the NTLM version to use.

This section provides a complete list of the Azure Authentication properties you can configure in the connection string for this provider.

---

Property	Description
AzureTenant	The Microsoft Online tenant being used to access data. If not specified, your default tenant is used.

The Microsoft Online tenant being used to access data. If not specified, your default tenant is used.

Data Type

string

Default Value

""

Remarks

The Microsoft Online tenant being used to access data. For instance, contoso.onmicrosoft.com. Alternatively, specify the tenant Id. This value is the directory Id in the Azure Portal > Azure Active Directory > Properties.

Typically it is not necessary to specify the Tenant. This can be automatically determined by Microsoft when using the OAuthGrantType set to CODE (default). However, it may fail in the case that the user belongs to multiple tenants. For instance, if an Admin of domain A invites a user of domain B to be a guest user. The user will now belong to both tenants. It is a good practice to specify the Tenant, although in general things should normally work without having to specify it.

The AzureTenant is required when setting OAuthGrantType to CLIENT. When using client credentials, there is no user context. The credentials are taken from the context of the app itself. While Microsoft still allows client credentials to be obtained without specifying which Tenant, it has a much lower probability of picking the specific tenant you want to work with. For this reason, we require AzureTenant to be explicitly stated for all client credentials connections to ensure you get credentials that are applicable for the domain you intend to connect to.

This section provides a complete list of the SSL properties you can configure in the connection string for this provider.

---

Property	Description
SSLServerCert	The certificate to be accepted from the server when connecting using TLS/SSL.

The certificate to be accepted from the server when connecting using TLS/SSL.

Data Type

string

Default Value

""

Remarks

If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.

This property can take the following forms:

Description	Example
A full PEM Certificate (example shortened for brevity)	-----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE-----
A path to a local file containing the certificate	C:\cert.cer
The public key (example shortened for brevity)	-----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY-----
The MD5 Thumbprint (hex values can also be either space or colon separated)	ecadbdda5a1529c58a1e9e09828d70e4
The SHA1 Thumbprint (hex values can also be either space or colon separated)	34a929226ae0819f2ec14b4a3d904f801cbb150d

If not specified, any certificate trusted by the machine is accepted.

Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.

This section provides a complete list of the SSH properties you can configure in the connection string for this provider.

---

Property	Description
SSHAuthMode	The authentication method used when establishing an SSH Tunnel to the service.
SSHClientCert	A certificate to be used for authenticating the SSHUser.
SSHClientCertPassword	The password of the SSHClientCert key if it has one.
SSHClientCertSubject	The subject of the SSH client certificate.
SSHClientCertType	The type of SSHClientCert private key.
SSHServer	The SSH server.
SSHPort	The SSH port.
SSHUser	The SSH user.
SSHPassword	The SSH password.
SSHServerFingerprint	The SSH server fingerprint.
UseSSH	Whether to tunnel the MariaDB connection over SSH. Use SSH.

The authentication method used when establishing an SSH Tunnel to the service.

Possible Values

None, Password, Public_Key

Data Type

string

Default Value

"Password"

Remarks

• None: No authentication is performed. The current User value is ignored, and the connection is logged in as anonymous.
• Password: The Cloud uses the values of User and Password to authenticate the user.
• Public_Key: The Cloud uses the values of User and SSHClientCert to authenticate the user. SSHClientCert must have a private key available for this authentication method to succeed.

A certificate to be used for authenticating the SSHUser.

Data Type

string

Default Value

""

Remarks

SSHClientCert must contain a valid private key in order to use public key authentication. A public key is optional, if one is not included then the Cloud generates it from the private key. The Cloud sends the public key to the server and the connection is allowed if the user has authorized the public key.

The SSHClientCertType field specifies the type of the key store specified by SSHClientCert. If the store is password protected, specify the password in SSHClientCertPassword.

Some types of key stores are containers which may include multiple keys. By default the Cloud will select the first key in the store, but you can specify a specific key using SSHClientCertSubject.

The password of the SSHClientCert key if it has one.

Data Type

string

Default Value

""

Remarks

This property is only used when authenticating to SFTP servers with SSHAuthMode set to PublicKey and SSHClientCert set to a private key.

The subject of the SSH client certificate.

Data Type

string

Default Value

"*"

Remarks

When loading a certificate the subject is used to locate the certificate in the store.

If an exact match is not found, the store is searched for subjects containing the value of the property.

If a match is still not found, the property is set to an empty string, and no certificate is selected.

The special value "*" picks the first certificate in the certificate store.

The certificate subject is a comma separated list of distinguished name fields and values. For instance "CN=www.server.com, OU=test, C=US, [email protected]". Common fields and their meanings are displayed below.

Field	Meaning
CN	Common Name. This is commonly a host name like www.server.com.
O	Organization
OU	Organizational Unit
L	Locality
S	State
C	Country
E	Email Address

If a field value contains a comma it must be quoted.

The type of SSHClientCert private key.

Possible Values

USER, MACHINE, PFXFILE, PFXBLOB, JKSFILE, JKSBLOB, PEMKEY_FILE, PEMKEY_BLOB, PPKFILE, PPKBLOB, XMLFILE, XMLBLOB

Data Type

string

Default Value

"PEMKEY_FILE"

Remarks

This property can take one of the following values:

Types	Description	Allowed Blob Values
MACHINE/USER		Blob values are not supported.
JKSFILE/JKSBLOB		base64-only
PFXFILE/PFXBLOB	A PKCS12-format (.pfx) file. Must contain both a certificate and a private key.	base64-only
PEMKEY_FILE/PEMKEY_BLOB	A PEM-format file. Must contain an RSA, DSA, or OPENSSH private key. Can optionally contain a certificate matching the private key.	base64 or plain text. Newlines may be replaced with spaces when providing the blob as text.
PPKFILE/PPKBLOB	A PuTTY-format private key created using the puttygen tool.	base64-only
XMLFILE/XMLBLOB	An XML key in the format generated by the .NET RSA class: RSA.ToXmlString(true).	base64 or plain text.

The SSH server.

Data Type

string

Default Value

""

Remarks

The SSH server.

The SSH port.

Data Type

string

Default Value

"22"

Remarks

The SSH port.

The SSH user.

Data Type

string

Default Value

""

Remarks

The SSH user.

The SSH password.

Data Type

string

Default Value

""

Remarks

The SSH password.

The SSH server fingerprint.

Data Type

string

Default Value

""

Remarks

The SSH server fingerprint.

Whether to tunnel the MariaDB connection over SSH. Use SSH.

Data Type

bool

Default Value

false

Remarks

By default the Cloud will attempt to connect directly to MariaDB. When this option is enabled, the Cloud will instead establish an SSH connection with the SSHServer and tunnel the connection to MariaDB through it.

This section provides a complete list of the Logging properties you can configure in the connection string for this provider.

---

Property	Description
Verbosity	The verbosity level that determines the amount of detail included in the log file.

The verbosity level that determines the amount of detail included in the log file.

Data Type

string

Default Value

"1"

Remarks

The verbosity level determines the amount of detail that the Cloud reports to the Logfile. Verbosity levels from 1 to 5 are supported. These are detailed in the Logging page.

This section provides a complete list of the Schema properties you can configure in the connection string for this provider.

---

Property	Description
BrowsableSchemas	This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.

This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.

Data Type

string

Default Value

""

Remarks

Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.

This section provides a complete list of the Miscellaneous properties you can configure in the connection string for this provider.

---

Property	Description
AllowUserVariables	When set to True, user variables (prefixed by an @) can be used in SQL queries.
MaxRows	Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
ReplaceInvalidDatesWithNull	Whether or not to return an invalid Date value as NULL.
ServerTimeZone	Specify a specific server time zone id of current platform(.Net or Java) by user.
Timeout	The value in seconds until the connection timeout error is thrown.
ZeroDatesToNull	Whether or not to return Date and DateTime values consisting of all zeros as NULL.

When set to True, user variables (prefixed by an @) can be used in SQL queries.

Data Type

bool

Default Value

false

Remarks

When set to True, user variables (prefixed by an @) can be used in SQL queries. The default behavior is to treat identifiers prefixed with @ as command parameters.

Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.

Data Type

int

Default Value

-1

Remarks

Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.

Whether or not to return an invalid Date value as NULL.

Data Type

bool

Default Value

false

Remarks

Determines whether or not to replace invalid dates with NULL. Effectively, setting this to true means no exceptions will be thrown if an invalid date is found.

Determines whether or not to replace invalid dates with NULL. Effectively, setting this to true means no exceptions will be thrown if an invalid date is found.

Specify a specific server time zone id of current platform(.Net or Java) by user.

Data Type

string

Default Value

""

Remarks

Specify a specific server time zone id of current platform (.NET or Java). When set to LOCAL, the server uses the local timezone and does not apply a timezone conversion.

The value in seconds until the connection timeout error is thrown.

Data Type

int

Default Value

30

Remarks

If the Timeout property is set to 0, the default of 30 seconds will be used instead.

If Timeout expires and the operation is not yet complete, the Cloud throws an exception.

Whether or not to return Date and DateTime values consisting of all zeros as NULL.

Data Type

bool

Default Value

false

Remarks

Whether or not to return Date and DateTime values consisting of all zeros as NULL. A value of all zeros indicates an invalid Date or DateTime value in MariaDB. Retrieving such a value may cause parsing errors unless you set this property to True.