Database Preparation for Microsoft SQL Server

Database Preparation for Microsoft SQL Server

This section describes the necessary database preparation that must be completed before any of the SICS software can be executed.

Your DBA will be responsible for correctly configuring and creating the SICS database.

Database setup #

Before any of the SICS tables can be created, you will need to create the files necessary for the storage of the database using the DBMS tools. This will generally involve creating the instance, specifying where the tablespace files will be stored, creating and configuring the database for your specific requirements.

See Database Structure for guidance on SICS-specific issues.

NOTE:
DXC highly recommends using the following datasource settings for new SQL Server databases created in SICS:

<optimizedMode>true</optimizedMode>
<useParameterizedSql>true</useParameterizedSql>

This optimizes the database schema for space and speed, and the SQL statement handling for speed.

NOTE:
It is highly recommended that the user/login name who will ‘own’ the database (create/drop tables, indexes, constraints, etc) is defined in UPPERCASE. If this rule is not followed, SICS may not work properly.

NOTE:
SQL statements generated by SICS do NOT use delimited identifiers. This means that all SQL Server identifiers (database names, schema names, login names, user names, table names, etc) must adhere to the SQL Server rules for regular (undelimited) identifiers: The name of an identifier must start with a letter, _,@, or #; followed by one or more letters, numbers, @, $, #, or _.

Unicode Data #

Microsoft SQL Server up to v2019 requires the use of NCHAR/NVARCHAR columns to store unicode data. In order for SICS to create the database schema using these datatypes a special mode needs to be enabled in the sics-database-sources.xml file for the datasource in question.

<unicodeMode>true</unicodeMode>

We recommend using the sendStringParametersAsUnicode=false; directive in the JDBC URL for the SICS data source when using parameterized SQL; ie when the setting <useParameterizedSql>true</useParameterizedSql> is set in the datasource. This recommendation remains even when unicode mode is enabled as we explicitly send unicode data and non-unicode data correctly.

See https://docs.microsoft.com/en-us/sql/connect/jdbc/setting-the-connection-properties?view=sql-server-ver16 for more details on the setting.

NOTE:
Microsoft SQL Server 2019 introduced UTF8 charsets which can be used with CHAR/VARCHAR columns to store UTF8 data. This has not yet been tested with SICS and is not recommended for use although it may work correctly.

See https://docs.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-ver15 for more details of Microsoft native support for UTF8.

Create the Environment table #

The environment table CNU_ENVIRONMENT must be created in its own schema. The suggested schema is SICS. No other tables should be created in this schema. Specifically, one should not create P&C or Life tables in this schema.

  • Create a database user SICS (note: user name in UPPERCASE) with the appropriate database privileges.
  • Run the SICS System Administration Utility. Logon as SICS.
  • When asked for an environment, choose Personal Database.
  • Choose the Database Setup, Manage Environments option.

Insert the required environments into this table (Right-click to add an environment.) Change the name of the Owner as required. This should correspond to the planned SICS Table Owner as described in Creating the SICS Table Owner.

Environment Table Owner Privileges #

Create a login for the SICS user, with access to the SICS database (as default), and assign db_datareader and db_datawriter roles on the database. From the database properties/permissions page, grant Create Table permissions to this user.

Environment Table for P&C and Life co-existence #

It is possible to run both the Life and the P&C system over the same set of database tables. This is described in section Co-existence of SICS P&C and Life. With respect to the environment table, two rows/environments must be inserted into the table. Make one environment for P&C and another for Life, both pointing to the same schema owner.

Database Users file setup #

The file SysUsers.txt is used during SICS Database Tables creation (see section Create Database Tables) to create the initial SICS users. A database record is created for all users defined in this file. The delivered file contains the definition for two sample users named User1 and User2.

Each user entry has the following format:

USERID <tab> LAST NAME <tab> FIRST NAME

You will need to edit this file to include as many initial users of the SICS database as required. Please note that you must follow this format strictly. Empty lines and spaces instead of tabs will halt the installation-process.

SysUsers.txt can be found in the <installation_root>\setup folder.

Before a SICS user name can be used for logging on to SICS, the user name must be created as a user on the target database management system and privileges assigned. This can be done after the Sysusers.txt file has been loaded when loading base data.

See section Create Database Tables for details.

After the initial users have been created during system setup, additional users may be added as required by using the SICS Create User option.

See section Create Additional Users for details.

Creating the SICS Table Owner #

Before continuing with the next phase of the setup, an initial DBMS user called the ‘Schema Owner’ or ‘Table Owner’ must be created. The suggested name for the table owner is SICSPC for SICS P&C or SICSLF for SICS Life. Each SICS database/environment/schema must have its own, unique table owner user.

For SQL Server, there are two ways to prepare the table owner user:

  1. The SQL Server Database name is X and the SICS database/environment/schema name is X. In SQL Server security create a login (user) X for the table owner. This login must have default database X and a user mapping where X is mapped to default schema X.
  2. The SQL Server Database name is X and the SICS database/environment/schema name is Y (typically, ‘dbo’). In SQL Server security create a login (user) Z for the table owner. This login must have default database X and a user mapping where X is mapped to default schema Y.

In case 1, you log in to SICS as the table owner of schema X by entering the user/login X in the SICS login screen. Then you select an environment which has an empty owner (typically, ‘Personal Database’).

In case 2, you log in to SICS as the table owner of schema Y by entering the user/login Z in the SICS login screen. Then you select an environment which has anempty owner (typically, ‘Personal Database’).

NOTE:
It is highly recommended that the user/login name of the table owner is defined in UPPERCASE. If this rule is not followed, SICS may not work properly.

The user should be created with the appropriate database privileges. See the sections below for your specific DBMS.

When you later run the SICS System Administration Utility to create the SICS tables, you must use this user name to log on to the database.

The SICSPC (SICSLF) user will then become the owner of the SICS tables.
The delivered SICS reporting universe files reference a database user named SICSPC (SICSLF). Customers using the SICS reporting solution without the Business Objects Designer module, cannot change this database connection information and must therefore use the suggested names above as the owners of the SICS tables.
Therefore, if you create the tables with another owner, you will not be able to use the SICS Reporting facility (unless you have the Business Objects Designer module).

Note that the SICSPC (SICSLF) user does not have to be included in the SysUsers.txt file, since the SICS Create Tables function (see section Create Database Tables) will automatically create a SICS user for the table’s owner. This SICS user will be assigned the System Administrator role within the SICS security system.

SICS Table Owner privileges #

Create the SICSPC or SICSLF user from the login using the Enterprise Manager and grant Create Table privileges to this user. The user can be created in the public group.

Grant SELECT permissions on the CNU_ENVIRONMENT table to the user. This can be done by choosing ‘Properties’ from the sub-menu for the CNU_ENVIRONMENT table, clicking ‘Permissions…’, checking SELECT for the SICSPC or SICSLF user and then clicking the OK button.

Create a View for this user to the CNU_ENVIRONMENT table, using the SQL Server Query Analyzer and the following SQL:

CREATE VIEW SICSPC.CNU_ENVIRONMENT AS SELECT * FROM SICS.CNU_ENVIRONMENT

Creating SICS Database tables #

System Users File #

It is assumed that the SysUsers.txt file has been modified to include all required SICS users.
Please refer to section Database Users file setup for details.

Create Database Tables #

Start the SICS System Administration Utility by selecting SICS P&C (Life) System Administration Utility from the SICS menu item on the Windows Programs menu. Login as the SICS Table Owner as defined in Creating the SICS Table Owner. When prompted to select the environment, choose an entry with an empty owner (typically, ‘Personal Database’).

Note that after the tables have been created, they will be owned by the table owner’s default schema (X when using option 1 and Y when using option 2).

Open the Database Setup Folder. You have the following options:

  • Manage Environments: With this option, you can add, remove and rename the different environments specified in the CNU_ENVIRONMENT table. See Create the Environment table for configuration details of this table.
  • Manage Tables: This option will present you with a window, allowing you to selectively create or drop tables and views, constraints, indexes and table comments.

ManageDatabaseTables.png

If you create tables using this option, you must also create base data and sample reference data, if required. * Manage Tables - Options: * The Options tab on this window allows you to specify the Tablespaces where you wish to put the tables. * SICS does not support tablespace options for SQL Server. * Use the Aliases window to specify aliases (A, B, C etc.) for your tablespace names, and the Mapper window to map the tables and indexes onto these aliases. If you need to do the mapping in stages, the Mapper window is saved in the SICS System directory as tablespacemappings.ini. The tablespaces you specify must be manually created in advance. * NOTE: See Database Specific Considerations * Manage Tables - Parameters: * The Parameters tab on this window allows you to specify additional DDL parameters. For example, you can specify freespace information for indexes. The parameter string you specify is appended to the appropriate DDL statement. It is your responsibility to supply a string that adheres to the syntax rules of your choice of database. You may leave the parameter field(s) empty. * Every time you use the “Manage Tables” window, you should review the parameters. * Note that your previous choices may or may not be kept between uses of this window.

  • Create Base Data: This option will create all data that is necessary to run the SICS system. This includes Reference Data (System Defined values only, not Sample values); Countries, States, Currencies, Users and Security Information.
  • Create Sample Reference Data: This option will create additional Reference Data values (additional to the System Defined values). If the sample values are not created, many of the reference data types will not contain any values, or only a limited set of system defined values. This will limit the usage of the system. Hence, you will have to go through the Reference Data Types and add your own values. All Sample Reference Data values can be deleted/edited by the System Administrator. For more information on Reference Data, refer to the SICS User’s Guide.
  • Create Sample Data: This option will load the SICS development team sample data into the database. This option is only relevant if you want to create a test database. This option should not be used when setting up the production environment. Some sample Business Partners, Reporting Units, Entry Codes, etc. will be created.

Database Specific Considerations #

Configuration for reducing Deadlocks #

SICS is designed to work with databases that use MVCC. Hence, we recommend that read committed snapshot isolation is turned on when using SQL Server.

ALTER DATABASE <MyDatabase>
SET READ_COMMITTED_SNAPSHOT ON
Tuning for Performance #

It is the responsibility of the DBA at the customer site to make sure the database is properly tuned.

Above all, it is utmost important to make sure database statistics are up-to-date. Without these statistics, the database optimizer will not be able to do its job properly.

Dropping All Tables And Views #

The SICS System Administration Utility ‘Database Setup’ -> ‘Manage Tables’ -> ‘Drop All User Tables’ option will drop all tables owned by the logged on user. The option ‘Drop All User Views’ will drop all views owned by the logged in user. In other words, the drops are not confined to tables (and views) created/used by SICS.

Therefore it is important to make sure that you are NOT logged in as the dbo when you choose this option!

However, it is OK to log in according to option 1 or 2 as described in Creating the SICS Table Owner.

Configuration #

If there seems to be an ‘out-of-resources’ situation, it may help to increase the sp_config ‘open objects’ from 500 to 1000.

Table and Column Comments #

SQL Server supports this via the MS_Description property at the table and column level.

Tablespace Considerations #

SICS creates around 800 tables in the database (this is for a Life/Combined environment).

For the initial setup of SICS, make sure the tablespace(s) in which the tables will be created are defined such that the storage space reserved for each table is not too big, compared to the available total diskspace for the tablespace(s).

After running SICS for a while, the DBA at the customer site should read just the storage parameters based on the experience gained.

Creation of the SICS Role / Group #

The SICS Role is used to assign the relevant database privileges to the users of the system. The creation of this role negates the need for assigning select, insert, update and delete privileges to all tables to all users, and also allows the users to read the CNU_ENVIRONMENT table. By specifying these privileges once only in a role and assigning the role to all users, this drastically cuts down the user creation time.

Note:
You can not complete the following steps until the SICS database tables have been created (as described in Create Database Tables).

SICS Role #

Create a new role (suggested name SICSG2) from the Enterprise Manager. This can be done by choosing ‘New Database Role…’ from the sub-menu under Roles off the SICS database.

Select the Properties of the role, and then click on the Permissions button, and grant select on the SICS.CNU_ENVIRONMENT table to the group.

Grant select, update, insert and delete rights on all of the SICS tables created earlier under the SICSPC or SICSLF user to the group. This can be done by executing the following SQL command in the SQL Server Query Analyzer and then executing its results:

SELECT 'GRANT SELECT, INSERT, UPDATE, DELETE ON SICSPC.' + NAME + ' TO SICSG2' FROM SYSOBJECTS WHERE TYPE = 'U' AND UID = (SELECT UID FROM SYSUSERS WHERE NAME = 'SICSPC')

Also grant select on SICS views (not including views used by the Business Objects reporting universes) with this SQL command:

SELECT 'GRANT SELECT ON SICSPC.' + NAME + ' TO SICSG2' FROM SYSOBJECTS WHERE TYPE = 'V' AND NAME NOT LIKE 'V_%' AND UID = (SELECT UID FROM SYSUSERS WHERE NAME = 'SICSPC')

Note:
You need to be logged in as the owner of the tables or dbo in order to grant the above privileges.

One and Two Level Roles / Groups #

For a One-Level Login (see One Level Logins), the above role/group/procedure can be used for the First Level user.

For a Two-Level Login (see Two Level Logins), the role/group/procedure created above will be assigned to the Second Level User. The first level users will simply have SELECT privileges on the SICS.CNU_ENVIRONMENT, SICSPC.CNU_USER and SICSPC.CNU_SECUREOPTS tables (see User Database Privileges for more information).

If the User Authorization Interface (UAI) is in use, then the first level users also need SELECT privileges on the SICSPC.SYSTEM_PARAMETERS and SICS4PC.SYS_PARAM_VALUES tables.

The privileges for the first level user can also be assigned to a role or group (suggested name SICSG1.) This will make it easier to create new users in the database. Simply grant the role to the user (or assign the new user to the group) for the user to inherit the privileges.

SICS Configuration #

After the database has been loaded with startup data, as described in the previous section, the SICS System Administrator must configure various system functions before general operation of the system.

As mentioned earlier, the User ID that is used when the database is loaded with initialization data is automatically designated as the system administrator.

User Security Configuration #

The following sections describe how to define users to the system and assign general categories of allowable business functions (User Roles).

One and Two Level Logins #

SICS handles it’s own security internally, utilizing the user role and use cases concepts to facilitate this, and thus the software reads data from the database to control user access. This means that the user has to have database access to all of the data so that the security components of SICS can be utilized. These access privileges are controlled by the database Role in SQL Server (see Creation of the SICS Role / Group.)

This Role or Group has to have select, update, insert and delete privileges to all of the SICS tables in the database. SICS itself then controls whether a particular table can be manipulated by the user, based on his or her user role.

Therefore, a user will have a specific user ID, with SICS Role or Group access that has select, update, insert and delete on all SICS tables and select on all SICS views (and select on the CNU_ENVIRONMENT table.) This is defined as a One-Level Login.

SICS has it’s own application-level security system, preventing unauthorized access to application functions. On a database level, however, a user needs access to all tables. Because of this, there exists a possibility that tables can be accessed and altered from an external source, such as ISQL_w. To solve this security problem, a second level of security has been developed in SICS that removes this possibility. This solution is called the Two-level Login.

The Two-Level Login utilizes two user IDs on the database, one of which is hidden from the user. The SICS System Administrator will control the user ID and password from within the Security window in the SICS System Administration utility. This will only be available to view by users with SICS Security Administration rights.

install_4.png

Figure 9 - One-Level Login

In a One-Level login, the DB User ID shown above has to be empty or equivalent to the Logon ID. This tells SICS that only the One-Level Login is in use. Note that the DB Password field should be left empty.

The Logon ID will be the ID that the user utilizes to log onto SICS.

If this login method is being used, then the user will only require one user ID on the Database, and one equivalent user defined in the SICS Security System. In this example, these will both be the user JOEB. This user will require access to all of the SICS tables and views in the database, which is assigned through the Role or Group. See User Database Privileges below for details on how to assign the Role or Group to the user.

install_5.png

Figure 10 - Two Level Login

In a Two-Level Login system, the Logon ID will be the ID that the user logs on to SICS with, in this case JOEB. This is the first user ID, which MUST be defined on the database with simply SELECT privileges on the CNU_ENVIRONMENT, SICSPC.CNU_USER and SICSPC.CNU_SECUREOPTS tables. (If the User Authorization Interface (UAI) is in use, then the first level users also need SELECT privileges on the SICSPC.SYSTEM_PARAMETERS and SICS4PC.SYS_PARAM_VALUES tables.)

The second user ID is given by the DB User ID field in the window above, in this case F67PK09. This tells SICS that the Two-Level Login is being used, and so after the Environment table and User table have been accessed, the first user will be logged out of the database. The second user will then be logged back into the database automatically, using the password given in the DB Password field. This must match the password for the second-level user on the database or the connection will fail. This user will require access to all of the SICS tables and views in the database, which is assigned through the Role or Group.

Note that in a two-level login system, all Events in SICS will be logged under the first user ID. The second level ID should not be defined in SICS Security. The user will not be aware of the second user ID on the database, and should never be allowed to view the user settings. If the user manages to obtain their second level login ID and password, then they could login to the SICS database using an external program, and perform updates and deletes using SQL.

See User Database Privileges below for details on how to assign the various privileges, Roles or Groups to the two user ID’s.

If security is a key concern in your organisation, then it is recommended to use the Two-Level Login. This will complicate the administration of the system due to the hidden second user ID, and will increase the required time to add new users to the system, but will provide a secure SICS database.

Using Database Remote OS Authentication #

SICS can connect to the database letting the database server check with Windows that the user has been authenticated. In other words, if the user successfully logged in to Windows, then the user can also start SICS and connect to the database without providing an additional password.

install_11.png

Figure 11 - The SICS Login Screen

To log on using “Database Remote OS Authentication” the user ticks the “Connect Using Database Remote OS Authentication” checkbox on the login dialogue. Only the database the user wants to logon to needs to be specified.

To enable this feature, the database administrator has to change the setting on the database, AND the database source in sics-database-sources.xml must be updated. The procedure is described below.

Setting up MS SQL Server for Remote OS Authentication #

Each windows user who wants to log on to SICS with Remote OS Authentication must be defined as a ’login’ in SQL Server as follows (use the SQL Server Management Studio to do the setup):

The SQL Server login must be named <domain>\<user> where <domain> is the Windows domain of the user, and <user> is the Windows user id. The SQL Server login must be defined with ‘Windows authentication’, not with ‘SQL Server authentication’.

Note that in SICS, there must be defined a SICS user with the name <user>, this is, without the domain part.

Updating SICS Database Source Setting #

The connection property integratedSecurity=true MUST be specified on the datasource.

For example:

<dataSource>jdbc:sqlserver://localhost;instanceName=SQLEXPRESS;sendStringParametersAsUnicode=false;selectMethod=cursor;integratedSecurity=true</dataSource>

If some logins use Windows authentication and other logins use SQL Server authentication (userid and password), then it will be necessary to define two database <source> definitions, one with and the other without, integratedSecurity.

For example:

<source name="SQLSERVER_2016_OS">
  <driver>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver>
  <accessor>com.csc.troll.accessor.JDBCDatabaseAccessor</accessor>
  <interface>com.csc.troll.accessor.SQLServer2000Interface</interface>
  <dataSource>jdbc:sqlserver://CSCSICSRV003.dk.emea.csc.com;instanceName=SQLEXPRESS;sendStringParametersAsUnicode=false;selectMethod=cursor;integratedSecurity=true;encrypt=true</dataSource>
  <useParameterizedSql>true</useParameterizedSql>
  <statementCacheSize>100</statementCacheSize>
</source>

<source name="SQLSERVER_2016">
  <driver>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver>
  <accessor>com.csc.troll.accessor.JDBCDatabaseAccessor</accessor>
  <interface>com.csc.troll.accessor.SQLServer2000Interface</interface>
  <dataSource>jdbc:sqlserver://CSCSICSRV003.dk.emea.csc.com;instanceName=SQLEXPRESS;sendStringParametersAsUnicode=false;selectMethod=cursor;encrypt=false</dataSource>
  <useParameterizedSql>true</useParameterizedSql>
  <statementCacheSize>100</statementCacheSize>
</source>
Remote OS Authentication and two-level login #

Follow the procedure outlined below for two-level login. The first-level user must be set up with remote OS authentication as described above. The second-level user must be set up with normal password authentication (SQL Server authentication).

Note: When two-level login is used together with OS authentication some extra setup is necessary. In addition to setting up a data source S1 with integratedSecurity=true, a second data source S2 must be set up that uses SQL Server authentication (without integratedSecurity=true). This is described above. In the properties GUI of the first level user in SICS the DB User ID must be set to a string with the format: <level2User>@<data source name> (if the 2nd level user name is ‘foo’ and the data source is named ‘S2’ the DB User ID must be foo@S2). SICS will then use Windows authentication on the first level login and SQL Server authentication on the second level login.

For example:

sql_os_auth_two_level.png

First level user definition

Create Additional Users #

After the initial users have been created during system setup, additional users may be added by using the SICS Create User option (accessible from the SICS Desktop -> System Administration -> Security -> Users).

  • Login to the SICS Workstation as the system administrator and double-click the Security icon under the System Administration folder.
  • Use the Access Security Manager window to create any additional system users that were not defined in the SysUsers.txt file. From the System Users tab, right click and select New… from the popup menu.

You will also need to give the user privileges to the appropriate tables, and also assign SICS user roles.

NOTE:
If you intend to export the data from one schema into another, then the owner of the target schema should be defined as a SICS user with System Administrator role before the export takes place. Otherwise SICS access security will prevent the target schema owner to log in as himself.

NOTE:
If you intend to export the data from one schema into another, then the owner of the target schema should be defined as a SICS user with System Administrator role before the export takes place. Otherwise SICS access security will prevent the target schema owner to log in as himself.

User Database Privileges #

One-Level Logins #

For each SICS user that you define - either by adding names to the SysUsers.txt file, or by creating users from the SICS System Administration Utility - you must also:

  • Create an equivalent database user.

  • Assign the role SICSG2 to the user from the Enterprise Manager.

  • Create a view on the SICS.CNU_ENVIRONMENT table for this user. This can be done with the following SQL:

    CREATE VIEW <USERID>.CNU_ENVIRONMENT AS SELECT * FROM SICS.CNU_ENVIRONMENT
Two-Level Logins #

For each SICS user that you define - either by adding names to the SysUsers.txt file, or by creating users from the SICS System Administration Utility - you must also:

  • Create two database users, one for the first level login and one for the second level login. A general second level user can be used if preferred (i.e. Use the same second level login for each user.)
  • Assign the SICSG1 role to the 1st level user from the Enterprise Manager.
  • Assign the SICSG2 role to the 2nd level user from the Enterprise Manager.
  • Create a view on the SICS.CNU_ENVIRONMENT table for the 1st level user.
  • Create a view on the SICS.CNU_ENVIRONMENT table for the 2nd level user.

The last two can be done with the following SQL from the SQL Server Query Analyzer:

CREATE VIEW <USERID>.CNU_ENVIRONMENT AS SELECT * FROM SICS.CNU_ENVIRONMENT
General Information #

You can not complete the above steps until the SICS group/role has been created (as described in Creation of the SICS Role / Group).

NOTE:
When you have successfully created new SICS users (both in SICS and on the database) you can log on to SICS using the new user names. These users will not have any initial SICS Access Rights. Thus, initially, the SICS Workstation will appear blank (no folders will be visible).

You have to grant the SICS users access rights, as described in section Assign Users to User Profiles below.

Assign Users to User Profiles #

A few sample, predefined User Roles are created as part of loading the Base Data. In addition, the system defined User Role “System Administrator” is created.

Logon to the SICS System Administration Utility as the System Administrator (the owner of the tables) and choose System Administration, Security.

  • From the User Roles tab, double-click a user role, select the Members tab, right-click on the user list, and select Assign Members… from the popup menu.

Note that if a new SICS user has not been assigned to a user role, the user will not have access to any of the SICS functions.

If you login to SICS as a user without any access rights, the SICS Workstation will appear ‘blank’ (no system folders will be visible).

Verify System Parameters #

The System Parameters have been set up by default as part of the database creation process, but they may be inappropriate for your operation. Therefore, you will need to check and/or alter them as appropriate.

Login to the SICS Workstation as the system administrator and double-click the System Parameter Maintenance icon under the System Administration folder.

Verify or modify the System Parameters as necessary.

JDBC encryption #

Disable encryption #

To allow unencrypted communication on JDBC connection between SICS and the SQL Server DBMS, it is required to explicitly set property encrypt=false on the JDBC connection string.

For details, please check the Microsoft official documentation at: https://docs.microsoft.com/en-us/sql/connect/jdbc/release-notes-for-the-jdbc-driver?view=sql-server-ver16

Sample config (using unencrypted communication):
(exerpt from sics-database-source.xml)

<source name="SQLServer">
  <driver>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver>
  <accessor>com.csc.troll.accessor.JDBCDatabaseAccessor</accessor>
  <interface>com.csc.troll.accessor.SQLServer2000Interface</interface>
  <dataSource>jdbc:sqlserver://hostname;sendStringParametersAsUnicode=false;selectMethod=cursor;encrypt=false</dataSource>
  <useParameterizedSql>true</useParameterizedSql>
  <statementCacheSize>100</statementCacheSize>
  <!--  <caseSensitiveUserNames>true</caseSensitiveUserNames> -->
</source>

Enable encryption #

To enable encrypted JDBC connection, a pre-requisite is to install and configure SSL/TLS support on your MS SQL Server DBMS.

For details, please check Microsofts official documentation at: https://docs.microsoft.com/en-us/sql/connect/jdbc/understanding-ssl-support?view=sql-server-ver16

Sample config (using encrypted communication):
(exerpt from sics-database-source.xml)

<source name="SQLServer">
  <driver>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver>
  <accessor>com.csc.troll.accessor.JDBCDatabaseAccessor</accessor>
  <interface>com.csc.troll.accessor.SQLServer2000Interface</interface>
  <dataSource>jdbc:sqlserver://hostname;sendStringParametersAsUnicode=false;selectMethod=cursor</dataSource>
  <useParameterizedSql>true</useParameterizedSql>
  <statementCacheSize>100</statementCacheSize>
  <!--  <caseSensitiveUserNames>true</caseSensitiveUserNames> -->
</source>