Connecting to SQL Server

This section contains details for SQL Server connections.

General information

Field Description
Data source SQL Server
Supported versions

N/A
Connection string jdbc:sqlserver://
Packaged?

Yes

Certified?

Yes

Supported features
Estimate job

Yes

Analyze data

Yes

Schedule

Yes

Processing capabilities
Pushdown

Yes

Note Pushdown for SQL Server is currently available as a public beta. Since this is a beta feature, some capabilities may be limited. For more information on our beta program, refer to Betas at Collibra.
Spark agent

Yes

Yarn agent

Yes

Parallel JDBC

Yes

Java Platform version compatibility
JDK 8

Yes

JDK 11

No

Minimum user permissions

In order to bring your SQL Server data into Collibra Data Quality & Observability, you need the following permissions.

  • Read access on SQL Server database tables.
  • The Kerberos user has read permissions on SQL Server tables (if using Kerberos authentication)
  • ROLE_ADMIN assigned to your user in Collibra DQ.

Recommended and required connection properties

Required Connection Property Type Value

Yes

Name Text The unique name of your connection. Ensure that there are no spaces in your connection name.

Yes

Connection URL String

The connection string path of your SQL Server connection.

For more information about how to construct your connection URL, see Building the connection URL in the official SQL Server documentation.

When referring to the example below, replace the value between the { } in the ${value} sections of the connection URL with your actual value.

Example jdbc:sqlserver://${host}:1433;databaseName=${dbName}

Yes

Driver Name String

The driver class name of your connection.

com.microsoft.sqlserver.jdbc.SQLServerDriver

Yes

Port Integer

The port number to establish a connection to the datasource.

The default port is 1433

No

Source Name String N/A

No

Target Agent Option The Agent that submits your Spark job for processing.

Yes

Auth Type Option

The method to authenticate your connection.

Note The configuration requirements are different depending on the Auth Type you select. See Authentication for more details on available authentication types.

No

Driver Properties String

The configurable driver properties for your connection. Multiple properties must be comma delimited. For example, abc=123,test=true

Authentication

Select an authentication type from the dropdown menu. The options available in the dropdown menu are the currently supported authentication types for this data source.

Important When you use Active Directory Password, you need to add authentication=ActiveDirectoryPassword to your connection URL and fill out the Username and Password fields described in the table below. For example, the following connection URL properly appends authentication=ActiveDirectoryPassword to the end of the URL: jdbc:sqlserver://example.demo.azuresynapse.net:1433;database=exampleDB;encrypt=true;authentication=ActiveDirectoryPassword

Important When you use Active Directory Service Principal, you need to add authentication=ActiveDirectoryServicePrincipal to your connection URL and fill out the Principal and Principal Secret fields described in the table below. For example, the following connection URL properly appends authentication=ActiveDirectoryServicePrincipal to the end of the URL: jdbc:sqlserver://example.demo.azuresynapse.net:1433;database=exampleDB;encrypt=true;authentication=ActiveDirectoryServicePrincipal

Important When you use Active Directory MSI, you need to add authentication=ActiveDirectoryManagedIdentity to your connection URL and fill out the MSI Client ID field described in the table below. For example, the following connection URL properly appends authentication=ActiveDirectoryManagedIdentity to the end of the URL: jdbc:sqlserver://example.demo.azuresynapse.net:1433;database=exampleDB;encrypt=true;authentication=ActiveDirectoryManagedIdentity

Additionally, your Synapse database must be on an Azure VM with Managed Identity enabled, as the Synapse database cannot be accessed outside of the Azure VM.

Required Field Description

Yes

Username

The username of your SQL Server account.

Yes

Password

The password of your SQL Server account.

Yes

Username

The Azure Active Directory (Azure AD) User Principal Name.

Yes

Password

The Azure Active Directory (Azure AD) password.

Yes

Principal The Microsoft Entra service principal ID.

Yes

Principal Secret The Microsoft Entra service principal secret.

Yes

Principal

The Kerberos entity to authenticate and grant access to your connection.

Example [email protected]

Yes

Keytab

The file path of the keytab file that contains the encrypted key for a Kerberos principal.

Example /tmp/keytab/sqlserver_user.keytab

Yes

Password The secret credential associated with your Kerberos principal.

Yes

Script

The file path that contains the script file used to interact with and authenticate a Kerberos user.

Example /tmp/keytab/sqlserver_pwd_mgr.sh

No

Param $1 Optional. Additional Kerberos parameter.

No

Param $2 Optional. Additional Kerberos parameter.

No

Param $3 Optional. Additional Kerberos parameter.

Yes

TGT The ticket-granting ticket cache that stores the TGT to authenticate your connection.

Yes

MSI Client ID The Microsoft Entra MSI Client ID

Troubleshooting

The Microsoft JDBC Driver for SQL Server requires that TCP/IP be installed and running to communicate with your SQL Server database. You can use the SQL Server Configuration Manager to verify which network library protocols are installed.

A database connection attempt might fail for many reasons. These can include the following:

  • TCP/IP is not enabled for SQL Server, or the server or port number specified is incorrect. Verify that SQL Server is listening with TCP/IP on the specified server and port. This might be reported with an exception similar to: "The login has failed. The TCP/IP connection to the host has failed." This indicates one of the following:
    • SQL Server is installed but TCP/IP has not been installed as a network protocol for SQL Server by using the SQL Server Network Utility for SQL Server 2000 (8.x), or the SQL Server Configuration Manager for SQL Server 2005 (9.x) and later.
    • TCP/IP is installed as a SQL Server protocol, but it is not listening on the port specified in the JDBC connection URL. The default port is 1433, but SQL Server can be configured at product installation to listen on any port. Make sure that SQL Server is listening on port 1433. Or, if the port has been changed, make sure that the port specified in the JDBC connection URL matches the changed port. For more information about JDBC connection URLs, see Building the connection URL.
    • The address of the computer that is specified in the JDBC connection URL does not refer to a server where SQL Server is installed and started.
    • The networking operation of TCP/IP between the client and server running SQL Server is not operable. You can check TCP/IP connectivity to SQL Server by using telnet. For example, at the command prompt, type telnet 192.168.0.0 1433 where 192.168.0.0 is the address of the computer that is running SQL Server and 1433 is the port it is listening on. If you receive a message that states "Telnet cannot connect," TCP/IP is not listening on that port for SQL Server connections. Use the SQL Server Network Utility for SQL Server 2000 (8.x), or the SQL Server Configuration Manager for SQL Server 2005 (9.x) and later to make sure that SQL Server is configured to use TCP/IP on port 1433.
    • The port that is used by the server has not been opened in the firewall. This includes the port that is used by the server or optionally, the port associated with a named instance of the server.
  • The specified database name is incorrect. Make sure that you are logging on to an existing SQL Server database.
  • The user name or password is incorrect. Make sure that you have the correct values.
  • When you use SQL Server Authentication, the JDBC driver requires that SQL Server is installed with SQL Server Authentication, which is not the default. Make sure that this option is included when you install or configure your instance of SQL Server.

See also

Diagnosing problems with the JDBC driver

Connecting to SQL Server with the JDBC driver

FAQ

SQL Server represents RDS, Azure SQL, and traditional SQL Server installations