SQL Server
SQL Server is a relational database management system (RDBMS) developed by Microsoft that supports storing, retrieving, and managing structured data. It offers features like advanced analytics, in-memory processing, and integration with cloud services. It is widely used in enterprise environments, it ensures scalability, security, and high availability for mission-critical applications.
Prerequisites
The following prerequisites must be met for a user to create and test a successful connection.
- The SQL Server must be accessible from the iceDQ server.
- Valid credentials to access the database.
- SQL Server version Microsoft SQL Server 2019 or above.
Driver Types
Two drivers are available for connecting to SQL Server.
| Driver | Description |
|---|---|
| SQL Server Native JDBC (Recommended) | This is a driver sourced from Microsoft. |
| SQL Server Custom JDBC | This is a third-party maintained custom driver. |
SQL Server Native Driver
Authentication Mechanisms
The following authentication mechanisms are supported.
- SQL Server
- Windows (NTLM)
- Active Directory Password
Connection Properties
SQL Server Authentication
Use the following properties to create a valid connection. Properties marked with an asterisk (*) are required.
| Name | Description | Example Values |
|---|---|---|
| Connection Name * | Name that uniquely identifies the connection. | Prod_sales_conn |
| Driver * | Driver used to establish the connection. | SQL Server Native JDBC |
| Custom JDBC URL | Standardized string used to define the connection details. Use this format supported by the driver: jdbc:sql:Server=[host];Port=[port];Database=[database] | jdbc:sqlserver://sqlserver.acme.com:1433;databaseName=SalesDB |
| Host * | The endpoint (hostname) of the SQL Server instance. | sqlserver.acme.com |
| Port * | Port on which the SQL Server listens. Default is 1433. | 1433 |
| Database * | Target database name. | SalesDB |
| Type * | Connection type – System Connection or User Connection. | System or User |
| Username * | SQL Server login username. | john_doe |
| Password * | Password for the specified username. | Admin@123 |
Windows Authentication (NTLM)
Use the following properties to create a valid connection. Properties marked with an asterisk (*) are required.
| Name | Description | Example Values |
|---|---|---|
| Connection Name * | Name that uniquely identifies the connection. | Prod_sales_conn |
| Driver * | Driver used to establish the connection. | SQL Server Native JDBC |
| Domain Name | On-prem AD domain name used for authentication. | CORPDOMAIN |
| Custom JDBC URL | Standardized string used to define the connection details. Use this format supported by the driver: jdbc:sql:Server=[host];Port=[port];Database=[database] | jdbc:sqlserver://sqlserver.acme.com:1433;databaseName=SalesDB |
| Host * | The endpoint (hostname) of the SQL Server instance. | sqlserve.acme.com |
| Port * | Port on which the SQL Server listens. Default is 1433. | 1433 |
| Database * | Target database name. | SalesDB |
| Type * | Connection type – System Connection or User Connection. | System or User |
| Username * | Windows domain username (without domain prefix). | john_doe |
| Password * | Password for the Windows domain user. | Admin@123 |
Active Directory Password Authentication
Use the following properties to create a valid connection. Properties marked with an asterisk (*) are required.
| Name | Description | Example Values |
|---|---|---|
| Connection Name * | Name that uniquely identifies the connection. | Prod_sales_conn |
| Driver * | Driver used to establish the connection. | SQL Server Native JDBC |
| Custom JDBC URL | Standardized string used to define the connection details. Use this format supported by the driver: jdbc:sql:Server=[host];Port=[port];Database=[database] | jdbc:sqlserver://sqlserver.acme.com:1433;databaseName=SalesDB |
| Host * | Hostname or IP address of the SQL Server instance. | sqlserver.acme.com |
| Port * | Port on which the SQL Server listens. Default is 1433. | 1433 |
| Database * | Target database name. | SalesDB |
| Type * | Connection type – System Connection or User Connection. | System or User |
| Username * | AD user in UPN format. | [email protected] |
| Password * | Password for the AD user. | Admin@123 |
Custom Properties
Custom properties are optional connection parameters in the SQL Server Native driver that allow customization of settings such as timeouts and proxy configurations. A list of supported properties is available here. The availability and behavior of custom connection properties may vary depending on the SQL Server Native driver version in use.
Supported Datatypes
The following data types are supported with the Native driver:
- BIGINT
- BINARY
- BIT
- CHAR
- DATE
- DATETIME2
- DATETIME3
- DATETIMEOFFSET2
- DECIMAL
- FLOAT
- GEOGRAPHY
- GEOMETRY
- IMAGE
- INT
- JSON
- MONEY
- NCHAR
- NTEXT
- NUMERIC
- NVARCHAR
- NVARCHAR(MAX)
- REAL
- SMALLDATETIME
- SMALLINT
- SMALLMONEY
- SQLVARIANT
- TEXT
- TIME
- TIMESTAMP
- TINYINT
- UDT
- UNIQUEIDENTIFIER
- VARBINARY
- VARBINARY(MAX)
- VARCHAR
- VARCHAR(MAX)
- VECTOR
- XML
SQL Server Custom Driver
Authentication Mechanisms
The following authentication mechanisms are supported.
- SQL Server
- Windows (NTLM)
Connection Properties
SQL Server Authentication
Use the following properties to create a valid connection. Properties marked with an asterisk (*) are required.
| Name | Description | Example Values |
|---|---|---|
| Connection Name * | Name that uniquely identifies the connection. | Prod_sales_conn |
| Driver * | Driver used to establish the connection. | SQL Server Custom JDBC |
| Custom JDBC URL | Standardized string used to define the connection details. Use this format supported by the driver: jdbc:sql:Server=[host];Port=[port];Database=[database] | jdbc:sql:Server=sqlserver.acme.com;Port=1433;Database=SalesDB |
| Host * | The endpoint (hostname) of the SQL Server instance. | sqlserver-instance.acme.com |
| Port * | Port on which the SQL Server listens. Default is 1433. | 1433 |
| Database * | Target database name. | SalesDB |
| Type * | Connection type – System Connection or User Connection. | System or User |
| Username * | SQL Server login username. | john_doe |
| Password * | Password for the specified username. | Admin@123 |
Windows Authentication (NTLM)
Use the following properties to create a valid connection. Properties marked with an asterisk (*) are required.
| Name | Description | Example Values |
|---|---|---|
| Connection Name * | Name that uniquely identifies the connection. | Prod_sales_conn |
| Driver * | Driver used to establish the connection. | SQL Server Custom JDBC |
| Domain Name | On-prem AD domain name used for authentication. | CORPDOMAIN |
| Custom JDBC URL | Standardized string used to define the connection details. Use this format supported by the driver: jdbc:sql:Server=[host];Port=[port];Database=[database] | jdbc:sql:Server=sqlserver.acme.com;Port=1433;Database=SalesDB |
| Host * | The endpoint (hostname) of the SQL Server instance. | sqlserver-instance.example.com |
| Port * | Port on which the SQL Server listens. Default is 1433. | 1433 |
| Database * | Target database name. | SalesDB |
| Type * | Connection type – System Connection or User Connection. | System or User |
| Username * | Windows domain username (without domain prefix). | john_doe |
| Password * | Password for the Windows domain user. | Admin@123 |
Custom Properties
The following optional connection properties can be configured based on user requirements.
| Property | Description | Note |
|---|---|---|
| Timeout | Times out the operation. | Default is 30. Set 0 for unlimited time. |
| Encrypt | Use this to negotiate TLS/SSL connections to the server | By default, the driver checks the server certificate against the system’s trusted store. |
Supported Datatypes
The following data types are supported with the Custom driver:
- BIGINT
- BIGINT IDENTITY
- BINARY
- BIT
- CHAR
- DATE
- DATETIME
- DATETIME2
- DATETIMEOFFSET
- DECIMAL
- DECIMAL IDENTITY
- FLOAT
- GEOGRAPHY
- GEOMETRY
- HIERARCHYID
- IMAGE
- INT
- INT IDENTITY
- MONEY
- NCHAR
- NTEXT
- NUMERIC
- NUMERIC IDENTITY
- NVARCHAR
- REAL
- ROWVERSION
- SMALLDATETIME
- SMALLINT
- SMALLINT IDENTITY
- SMALLMONEY
- SQL_VARIANT
- TABLE
- TEXT
- TIME
- TIMESTAMP
- TINYINT
- TINYINT IDENTITY
- UNIQUEIDENTIFIER
- VARBINARY
- VARCHAR
- XML