SQL Server Connection | DataKnits Knowledge Hub

Microsoft SQL Server

On-premises SQL Server 2016 and later, Amazon RDS for SQL Server, and Azure SQL Database / SQL Managed Instance. JDBC-based connectivity using the Microsoft JDBC Driver for SQL Server.

On this page

Connection Fields
Authentication Methods
Named Instances
SSL / TLS
SSH Tunneling
Write Modes
Known Limitations

Connection Fields

Field	Required	Default	Description
Display Name	Yes	—	A human-readable label for this connection.
Host	Yes	—	Hostname or IP address of the SQL Server. For RDS or Azure SQL, use the provided endpoint.
Port	Yes	1433	SQL Server listener port. Default is 1433 for the default instance. Named instances may use a dynamic port — see Named Instances.
Database	Yes	—	The default database to connect to.
Username	Yes*	—	SQL Server login name. Not required when using Windows Authentication.
Password	Yes*	—	SQL Server login password. Not required for Windows Authentication.
Authentication	Yes	`SQL_AUTH`	Authentication method. See Authentication Methods.
Instance Name	No	—	Named instance (e.g., `SQLEXPRESS`). See Named Instances.
Encrypt	No	`true`	Whether to encrypt the JDBC connection. Defaults to `true` for the Microsoft JDBC driver version 10+.
Trust Server Certificate	No	`false`	When `true`, skips server certificate validation. Use only for development environments with self-signed certificates.
Connection Timeout (s)	No	30	Seconds before a connection attempt times out.
Additional JDBC Parameters	No	—	Optional semicolon-separated key=value pairs appended to the JDBC URL.

Authentication Methods

Method	Description	When to Use
`SQL_AUTH`	Standard SQL Server username and password login.	Most common. Works for on-premises, RDS, and Azure SQL with SQL authentication enabled.
`WINDOWS_AUTH`	Kerberos-based integrated Windows Authentication. No username/password in the connection form — identity comes from a Kerberos keytab configured on the Spark execution nodes.	On-premises SQL Server in Active Directory environments. Requires Kerberos setup on all Spark executor nodes.
`AAD_SERVICE_PRINCIPAL`	Azure Active Directory Service Principal. Requires Client ID, Client Secret, and Tenant ID fields.	Azure SQL Database and SQL Managed Instance when using Azure AD authentication. Requires the `mssql-jdbc` driver version 10 or later.

Named Instances

SQL Server named instances (e.g., SERVER\SQLEXPRESS) do not use a fixed port. Instead, SQL Server Browser service dynamically assigns a port to each named instance.

To connect to a named instance in DataKnits:

Ask your DBA for the static port number assigned to the named instance (found in SQL Server Configuration Manager).
Enter the static port in the Port field.
Enter the instance name (e.g., SQLEXPRESS) in the Instance Name field. DataKnits appends ;instanceName=SQLEXPRESS to the JDBC URL.

Dynamic port resolution via SQL Server Browser is not supported. Ensure the named instance has a static port assigned before creating the connection.

SSL / TLS

Microsoft JDBC Driver 10+ enables encryption by default. The relevant fields are Encrypt and Trust Server Certificate:

For cloud-managed instances (RDS, Azure SQL), set Encrypt = true and Trust Server Certificate = false. These instances use certificates from public CAs that the JVM trusts by default.
For on-premises instances with a self-signed certificate, set Trust Server Certificate = true — or upload the server's CA certificate in the SSL CA Cert field to validate without fully bypassing certificate checks.

SSH Tunneling

When SQL Server is in a private subnet, DataKnits can route the JDBC connection through an SSH bastion host.

Field	Required	Description
SSH Host	Yes	Hostname or IP of the SSH bastion server.
SSH Port	Yes	SSH port. Default: 22.
SSH Username	Yes	Linux username on the bastion host.
SSH Private Key (PEM)	Yes*	PEM-encoded private key.
SSH Password	Yes*	Bastion host password. Use only if a private key is not available.

Write Modes

Mode	Behaviour
Overwrite	Truncates the target table and inserts all incoming rows.
Append	Inserts all incoming rows without modifying existing data.
Upsert (Merge)	Generates a T-SQL `MERGE INTO target USING source ON (key) WHEN MATCHED THEN UPDATE ... WHEN NOT MATCHED THEN INSERT ...` statement.
Create if Not Exists	Creates the target table from the DataFrame schema if it does not exist, then appends data.

Known Limitations

SQL Server timestamp / rowversion columns are not datetime values — they are 8-byte binary row change counters used for optimistic locking. DataKnits maps them to BinaryType in the catalog. Do not use them as pipeline watermark columns.
Windows Authentication (WINDOWS_AUTH) requires Kerberos to be configured on all Spark executor nodes. This is an infrastructure prerequisite that DataKnits does not manage.
Azure AD authentication (AAD_SERVICE_PRINCIPAL) requires mssql-jdbc version 10 or later. Older driver versions do not support AAD auth flows.
The schema JDBC parameter sets the default schema for the connection. If your tables are in a schema other than dbo, add schema=YourSchema in Additional JDBC Parameters.
For very large bulk loads, the generated PySpark code uses JDBC batch inserts. For maximum performance, consider using the BCP (Bulk Copy) path by configuring a Pre-SQL that stages data via an external table or linked server. Native BCP orchestration from within DataKnits pipelines is not currently supported.