SQL Server

You can create two types of SQL Server environments:

Both the environments have same:

  • Prerequisites
  • Privileges
  • JDBC driver details
  • TLS connection details

There is a small difference between the two modes in JDBC connection parameters.

Prerequisites

Pre-requisite steps for establishing successful connection:

  1. Creation of dedicated service account for erwin with Metadata Read-only privileges in SQL Server Database
  2. Firewall connection open between SQL Server and erwin DI application server
  3. Opening of SQL Server database port to accept connections from erwin DI application server

Privileges

Following are the privileges given to service account for:

  • Metadata scanning: Grant view definition on Schema
  • Data preview: Db_datareader

JDBC Driver Details

SQL Server JDBC driver is out of box packaged with erwin DI application. Hence, no JDBC driver configuration is required from end user standpoint.

TLS Connection Details

  • The SQL Server JDBC driver supports connection via TLS 1.2.
  • The TLS protocol parameter needs to be added to JDBC URL string to ensure that the connection is via TLS. Otherwise, the source database will reject any incoming request in non-TLS mode.
  • JDBC URL being used to connect via TLS:
    jdbc:sqlserver://SERVER_NAME:PORT#;databaseName=AdventureWorks;sslProtocol=TLSv1.2
  • Additional parameters to configure (if needed):
    integratedSecurity=true;encrypt=true;trustServerCertificate=true;

JDBC Connection Parameters

SQL Authentication

The Connection Properties tab displays the connection parameters to establish connection for SQL Server (SQL authentication) connection.

Once the connection parameters are entered, use these options to go to the next tab, test the connections, save and continue, or save and exit.

Enter appropriate values in the fields (connection parameters). The fields marked with a red asterisk are mandatory.

Field Name

Description

Driver Name

Specifies the JDBC driver name for connecting to the database.

For example, com.microsoft.sqlserver.jdbc.SQLServerDriver

IP Address/Host Name

Specifies the IP address or server host name of the database.

For example, localhost.

DBMS Name/DSN

Specifies the SQL Server database name being used to connect to the environment.

For example, ErwinDIS931.

Port

Specifies the port to connect with the database.

1433 is the default port for a Sql Server database type. You can change it, if required.

User Name

Specifies the SQL Server (Service Account) user name.

For example, sa.

URL

Specifies the full JDBC URL that is used to establish a connection with the database.

For example, jdbc:sqlserver://SERVER_NAME:PORT#;databaseName=DatabaseName

It is autopopulated based on the other parameters.

Password

Specifies the SQL Server (Service Account) password.

For example, goerwin@1.

Save Password

Specifies whether the password is saved

DBMS Instance Schema

Specifies the schema of the database.

Use this option to select multiple or narrow down to single schema.

For example, DBO.

Connection Pool Type

Specifies the connection pool type being used to connect via JDBC.

For example, HIKARICP and BONECP.

Number of Partitions

Specifies the number of partitions of the database.

It is autopopulated with default number of partitions. You can edit and provide the number of partitions as required. For example, 2.

Minimum Connections Per Partitions

Specifies the minimum connections per partitions of the database.

It is autopopulated with default minimum connections per partitions. You can edit and provide the minimum connections per partitions as required. For example, 3.

Maximum Connections Per Partitions

Specifies the maximum connections per partitions of the database.

It is autopopulated with default maximum connections per partitions. You can edit and provide the maximum connections per partitions as required. For example, 5.

To use database options, click (Options).

The Database Options page appears. It displays the available database options.

Select keys and double-click the cells under the Value column to set the values of the keys. Use to save the database options.

SQL (Windows Authentication)

The Connection Properties tab displays the connection parameters to establish connection for SQL Server (Window authentication) connection.

Once the connection parameters are entered, use these options to go to the next tab, test the connections, save and continue, or save and exit.

Enter appropriate values in the fields (connection parameters). The fields marked with a red asterisk are mandatory.

Field Name

Description

Driver Name

Specifies the JDBC driver name for connecting to the database.

For example, com.microsoft.sqlserver.jdbc.SQLServerDriver

IP Address/Host Name

Specifies the IP address or server host name of the database.

For example, localhost.

DBMS Name/DSN

Specifies the SQL Server database name being used to connect to the environment.

For example, ErwinDIS931.

Domain

Specifies the network domain name on which database resides.

For example, U-DOM1.

User Name

Specifies the SQL Server (Service Account) user name.

For example, sa.

URL

Specifies the full JDBC URL that is used to establish a connection to the database.

It is autopopulated based on the other parameters.
jdbc:jtds:sqlserver://SERVER_NAME:PORT#;databaseName=DatabaseName;domain=DomainName;useNTLMv2=true;

Password

Specifies the SQL Server (Service Account) password.

For example, goerwin@1.

Save Password

Specifies whether the password is saved

DBMS Instance Schema

Specifies the schema for the database.

Use this option to select multiple or narrow down to single schema.

For example, DBO.

Connection Pool Type

Specifies the connection pool type being used to connect via JDBC.

For example, HIKARICP and BONECP.

Number of Partitions

Specifies the number of partitions for the database.

It is autopopulated with default number of partitions. You can edit and provide the number of partitions as required. For example, 2.

Minimum Connections Per Partitions

Specifies the minimum connections per partitions for the database. It is autopopulated with default minimum connections per partitions. You can edit and provide the minimum connections per partitions as required. For example, 3.

Maximum Connections Per Partitions

Specifies the maximum connections per partitions for the database. It is autopopulated with default maximum connections per partitions. You can edit and provide the maximum connections per partitions as required. For example, 5.

To use database options, click (Options).

The Database Options page appears. It displays the available database options.

The TestConnectionQuery option is selected by default to validate the internal connection. The system displays exceptions if this option is not selected.

Select keys and double-click the cells under the Value column to set the values of the keys. Use to save the database options.