SQL Server

You can create two types of SQL Server environment:

  • SQL authentication
  • Windows authentication

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 Suite application server
  3. Opening of SQL Server database port to accept connections from erwin DI Suite 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 Suite 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

To enter SQL Server (SQL authentication) connection parameters, follow these steps:

  1. Select the Database Type as SqlServer while creating the environment.

    2. When you select database type as Sql Server, the following connection parameters appear on the right hand side.

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

    3. Field Name

    Description

    Driver Name

    Specifies the JDBC driver name for connecting to the database.

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

    DBMS Name/DSN

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

    For example, ErwinDIS931.

    IP Address/Host Name

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

    For example, localhost.

    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.

    Password

    Specifies the SQL Server (Service Account) password.

    For example, goerwin@1.

    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.

    DBMS 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 desired. 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 desired. 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 desired. For example, 5.
  3. Click to use database options.

    4. The Database Options page appears displaying the different options available.

  4. Use the database options in the following way:
    Key ()
    To use a key, select the corresponding check box.
    Value
    To set the value of the selected key, double-click the corresponding cell under the Value column and select the appropriate value from the drop down.
    OK ()
    To save the database options, click .

To enter SQL Server (Window authentication) connection parameters, follow these steps:

  1. Select the Database Type as Sql Server (Windows Authentication).

    2. When you select database type as Sql Server (Windows Authentication), the following connection parameters appear on the right hand side.

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

    3. Field Name

    Description

    Driver Name

    Specifies the JDBC driver name for connecting to the database.

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

    DBMS Name/DSN

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

    For example, ErwinDIS931.

    IP Address/Host Name

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

    For example, localhost.

    Domain

    Specifies the network domain name on which database resides.

    For example, U-DOM1.

    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.

    Password

    Specifies the SQL Server (Service Account) password.

    For example, goerwin@1.

    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;

    DBMS 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 desired. 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 desired. 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 desired. For example, 5.
  3. Click to use database options.

    4. The Database Options page appears displaying the different options available.

  4. Use the database options in the following way:
    Key ()
    To use a key, select the corresponding check box.
    Value
    To set the value of the selected key, double-click the corresponding cell under the Value column and select the appropriate value from the drop down.
    OK ()
    To save the database options, click .