Connect Microsoft SQL Server

The MSSQL source reads tables and views from a Microsoft SQL Server into the Lake. Use this page to fill in each field of the MSSQL form. The steps shared by every SQL engine are on Connect a SQL database.

---

Prerequisites

• A SQL Server login (user name and password) with read access to the databases and tables you want to load.
• The server accepts connections from DataMind on its port, usually 1433.
• Create/Edit Lake Objects in DataMind. See Who can do what in the Lake.

---

Add the source

1. Open Lake and click Add datasource, or open the New menu in the toolbar above the tree.
2. Choose MSSQL in the picker.
3. Fill in the General, Connection and Datetime steps as described below.
4. Click Add.

---

Connection settings

General step

Name (Mandatory)

• The name the source carries in the Lake.
• At least 3 characters. The field reads Enter name until you type.

Code (Mandatory)

• A short code, at least 2 characters, made of letters and numbers only.
• It becomes the prefix of the table names the source forms. It fills in from Name while you type; edit it to set your own.
• Name and Code cannot be changed after the source is created.

Folder (Optional)

• The Lake folder the source is filed under. Leave it empty to keep the source at the top level.

Connection step: Address

Paste connection string (Optional)

• Click Paste connection string, paste a JDBC URL or an ADO.NET string, and click Fill.
• Example: jdbc:sqlserver://<server>:1433;databaseName=sales.
• The string fills Host, Port and Database. A password inside it is never read.
• A named instance written as <server>\<instance> is not carried over: only the server name and the port are filled.

Host (Mandatory)

• The server name or IP address of the SQL Server.
• Enter the host only, without the port or an instance name.

Port (Mandatory)

• The TCP port SQL Server listens on.
• Default: 1433. For a named instance, enter the port that instance listens on.
• A whole number from 1 to 65535.

Database (Optional)

• The database to read from.
• Leave it empty to load all databases the user can read. The Lake then lists every database the login can access that holds at least one table or view.
• With a database entered, the Lake lists that database only.

Connection step: Authentication

Username (Mandatory)

• The SQL Server login the source signs in with.

Password (Mandatory)

• The password of that login.
• After you save, the field reads Stored password with Replace beside it. The password is never shown again.

Connection step: Security

SSL/TLS (Optional)

• Encrypts the connection between DataMind and the server.
• Default: off.

Trust server certificate (Optional)

• Accepts a self-signed or internal-CA certificate without validating it.
• Shown only while SSL/TLS is on. Default: on.
• Turn it off when the server presents a certificate from a public authority and you want it validated.

Connection step: Advanced

Server version (Optional)

• 2008 or newer (default): the default driver with modern TLS.
• 2005 or older: the legacy driver for servers that only speak TLS 1.0.
• The Advanced card opens by itself when 2005 or older is selected.

Datetime step

Timezone (Optional)

• The IANA timezone the server writes its datetime values in, for example Europe/Berlin.
• Leave it empty when the server runs in the same timezone as the platform.

Datetime values (Optional)

• Keep as written (wall clock) (default) stores each value exactly as the server shows it.
• The other option converts each value from the source timezone into platform time on load.

1C source (+2000 years) (Optional)

• For 1C databases, which store dates shifted by 2000 years.
• Subtracts 2000 years from each value during extraction and loads the 1C empty date 2001-01-01 as NULL.
• Default: off.

Timezones and datetime values explains the three settings in full.

---

Test and save

1. On the Connection step, click Test connection. The button reads Testing… while it runs.
2. Connection test passed means DataMind reached the server and signed in. Connection test failed shows the server's message with a copy button; fix the settings and test again.
3. On Datetime, check the summary: Name, Server, Database (or All databases), Username, Security and, for the legacy driver, Server version.
4. Click Add. Then import its tables.

The test is optional and never saves the source. To edit or duplicate the source later, see Connect a SQL database.

---

Questions, answered

Should I leave Database empty?

Leave it empty when you want to pick tables from several databases on the same server. The Lake lists every database the login can access that holds at least one table or view. Enter a database to keep the source to that one database.

My server uses a named instance. What do I enter?

Enter the server name in Host and the TCP port of the instance in Port. The form connects by host and port, so the instance name is not used; a pasted <server>\<instance> string fills the server name only.

Can I sign in with Windows authentication?

No. The MSSQL form signs in with a SQL Server login: a user name and password defined on the server.

Why can't I see Trust server certificate?

It appears only while SSL/TLS is on. Turn on SSL/TLS under Security and the switch shows beneath it.

I use Azure SQL Database. Should I pick MSSQL?

No. Choose the Azure SQL Database card. It always encrypts the connection and also offers sign-in with a Microsoft Entra service principal. See Connect Azure SQL Database.

Can I send data back to SQL Server?

Yes. MSSQL is also offered as a Writeback destination, with the same connection fields. See Add and manage destinations.