Connect a SQL database

The SQL sources share one connection form: MSSQL, Postgres, MySQL, MariaDB, Clickhouse, Oracle, Azure SQL Database and Amazon Redshift. Use this page to fill in that form, choose the security options and understand how stored passwords behave. When the connection is saved, import its tables. The same database form also configures database Writeback destinations.

Add a SQL source

1. Open Lake and click Add datasource in the header, or open the New menu in the toolbar above the tree.
2. Choose the engine in the picker, for example Postgres, MSSQL or Clickhouse. Supported connectors shows how the picker is grouped.
3. On General, enter Name and Code and choose a Folder if you want one, then click Next. This step is the same for every source; see Add a data source.
4. On Connection, fill in the cards described below. Test connection is optional.
5. On Datetime, set the timezone and how datetime values are read, then click Add. Timezones and datetime values explains both settings.

Fill in the connection fields

The Connection step shows the cards Address, Authentication, Security, Advanced (for the engines that have them) and a test card, in that order.

Address

• Paste connection string opens a field where you paste a JDBC URL, a URI, an EZConnect string such as host:1521/ORCLPDB1, or an ADO.NET string, then click Fill. The string fills Host, Port and the database field, and a password inside it is never read. If the form cannot find a host, it shows "No host found. Paste a JDBC URL, a URI or host:port/database.".
• Host takes the server name or IP address. Port starts on the engine's default port; the table under Engine reference lists the defaults.
• Oracle adds Connect by above the database field: choose Service name for Oracle 12c and newer, or SID for older servers. The label of the database field follows your choice.
• An optional database field carries the hint "Leave empty to load all databases the user can read.".

Authentication

• With SQL login (the default), enter Username and Password.
• Azure SQL Database also offers Sign in with a Microsoft Entra service principal: enter Tenant ID, Client (application) ID and Client secret.

Security

• SSL/TLS encrypts the connection to the server. For Azure SQL Database the switch is replaced by an Always encrypted badge.
• Trust server certificate accepts a self-signed or internal-CA certificate without validating it. It appears for MSSQL and Azure SQL Database while SSL/TLS is on; it starts on for MSSQL and off for Azure SQL Database.
• Oracle with SSL/TLS on adds TLS certificate (PEM, optional) for a server certificate not issued by a public authority. Paste the certificate or click Upload .pem. If the file cannot be read, the form shows "Could not read the certificate file:" and the reason.
• With SSL/TLS on, Clickhouse moves from port 8123 to port 8443 while the port is still a default, and back when you turn SSL/TLS off.

Advanced

• Server version (MSSQL): 2008 or newer uses the standard driver with modern TLS; 2005 or older uses the legacy driver for servers that only speak TLS 1.0.
• Driver (Oracle): Built-in connects directly; Instant Client is required for Oracle 11g and cannot use SSL/TLS.

Test the connection

The Test connection card sits at the foot of the Connection step and starts as Not tested.

• Click Test connection; while it runs the button reads Testing…. The test is optional, and it never saves the connection.
• A passing test reads Connection test passed. A test run on a saved source also detects the source timezone and shows Detected source timezone: with the zone; you can then apply it on the Datetime step.
• A failing test reads Connection test failed, with the server's message and a copy button. Correct the settings and test again, or see Lake troubleshooting.

Engine reference

Set the timezone and finish

On Datetime:

• Timezone is the zone the source writes its datetime values in. Leave it empty to use the platform timezone.
• Datetime values starts on Keep as written (wall clock); the other option converts each value to platform time.
• 1C source (+2000 years) appears for MSSQL and Azure SQL Database only. It subtracts 2000 years from each value during extraction and loads the 1C empty date 2001-01-01 as NULL.

The step also carries the summary of the source: Name, Server, the database row (Database, SID or Service name), Username (or Client ID for an Entra sign-in), Security (Always encrypted, Off, SSL/TLS or SSL/TLS, server certificate trusted) and, for Oracle, Driver. Click Add to create the source. Timezones and datetime values covers the settings and the fixed rules that always apply to values.

Other database sources

• Snowflake has its own form, with key-pair or password sign-in and the steps General, Connection and Datetime: Connect Snowflake.
• MongoDB has its own form: paste a mongodb:// or mongodb+srv:// URI, or fill in the server fields, then choose authentication and TLS: Connect MongoDB.
• Google BigQuery, Firebase, the Google Sheets and Google Analytics sources, and Amazon Athena have their own pages; Supported connectors points to each one.

Stored passwords and keys

Saved values are write-only: after you save, the form never shows the password or certificate again. The field reads Stored password, or another stored item such as Stored certificate, with Replace beside it. Click Replace to type a new value. Connector secrets covers the vault and the masking rules.

If your administrator turned on Require re-entering connector secrets after a host change in System settings, changing an identity field clears the stored value and the form asks for it again. For a database connection the identity fields are Host and Username, so the notice reads "The host changed. Enter the password again." or "The username changed. Enter the password again.". The setting is off by default.

Edit or duplicate a connection

• Edit: open the saved source. The form opens on Connection with stored values masked, Name and Code are locked, and the submit button reads Save.
• Duplicate: opens with empty Name and Code and the rest of the connection loaded. The copy keeps the stored password and any stored certificate, so you do not re-enter them; if you change the host or user name while the re-entry setting is on, the form asks for the password again. Secrets that live in a custom connector's vault are never copied.

Set up a Standard Writeback destination

The same database form is used for Writeback destinations. The destination picker offers MSSQL, Postgres, Clickhouse, Oracle, MySQL and MariaDB with this form, and the destination form is a single page with the same fields. Other connection types are not offered as destinations. See Add and manage destinations for the full flow, from adding a destination to importing its tables.

Permissions

• To open a saved connection and see its masked settings, you need View Lake Objects.
• To add, edit, duplicate or test a connection, and to change a stored password or certificate, you need Add/Modify Lake Objects, which includes View Lake Objects.

For the full list, see Who can do what in the Lake.

Questions, answered

Can I paste a full connection string instead of filling in the fields?

Yes. Click Paste connection string, paste a JDBC URL, a URI, an EZConnect string such as host:1521/ORCLPDB1, or an ADO.NET string, and click Fill. The string fills Host, Port and the database field, and a password inside it is never read. If no host can be found, the form shows "No host found. Paste a JDBC URL, a URI or host:port/database.".

Why did my port change when I turned on SSL/TLS?

Clickhouse moves to port 8443 while the port is still on the default 8123, and turning SSL/TLS off moves it back. A port you typed yourself is left alone.

I need to change the Postgres database. Can I?

No. The database is fixed when a Postgres source is created, and the form notes "The database of a Postgres source cannot be changed after it is created.". Create a new source for the other database.

My password field cleared after I changed the host. Why?

Your administrator turned on Require re-entering connector secrets after a host change, and the host or user name changed, so the stored password no longer applies. Type the password again. See System settings.

Does a duplicate keep the password?

Yes. The copy keeps the stored password and any stored certificate and shows them masked, so you do not re-enter them. If you change the host or user name while duplicating and the re-entry setting is on, the form asks for the password again. Secrets stored in a custom connector's vault are never copied.

Which MSSQL option do I need for SQL Server 2005?

Open Advanced and set Server version to 2005 or older. It uses the legacy driver, for servers that only speak TLS 1.0. The default, 2008 or newer, uses the standard driver with modern TLS.

Why can't I use Instant Client with SSL/TLS?

Instant Client cannot use SSL/TLS, so the two options exclude each other. Turn SSL/TLS off to use Instant Client, or keep Driver on Built-in to use SSL/TLS. The form shows "Instant Client is not available while SSL/TLS is on. Turn SSL/TLS off under Security to use it.".

My Oracle server uses a private CA. What do I do?

Turn on SSL/TLS under Security and paste the server certificate into TLS certificate (PEM, optional), or click Upload .pem. Keep Driver on Built-in, because Instant Client cannot use SSL/TLS. Turning SSL/TLS off clears the certificate.

Why can't I turn off encryption for Azure SQL Database?

Azure SQL Database encrypts every connection. The Security card shows the Always encrypted badge instead of the switch, with the note "Azure SQL Database encrypts every connection; this cannot be turned off.".

The test failed, but I am sure the settings are right. Can I still add it?

Yes. The test is optional, so you can save without a passing test. If the settings are wrong, loads fail later; fix the settings and run the test again from the saved source.