SQL Server

Collaborator supports Microsoft SQL Server 2012, 2014, 2016, 2017, 2019, 2022.

• SQL Server – database server.

• SQL Server JDBC Driver – database driver for Java platforms.

  There are various JDBC drivers that can be used for various combinations of SQL Server and Java platform:

  JDBC Driver 6.2 – supports SQL Server 2008 - 2017 and Java 7 and 8

  JDBC Driver 6.4 – supports SQL Server 2008R2 - 2017 and Java 7, 8 and 9

  JDBC Driver 7.0 – supports SQL Server 2008R2 - 2017 and Java 8 and 10

  JDBC Driver 7.4 – supports SQL Server 2012 - 2019 and Java 8, 11 and 12

  JDBC Driver 9.2 – supports SQL Server 2012 - 2019 and Java 8, 11 and 15

  Please see Microsoft JDBC Driver for SQL Server support matrix for detailed information on selecting the appropriate driver.

You need to create an empty database for Collaborator. You can do this, for example, in SQL Server Management Studio.

When installing Collaborator on a server, the installation wizard will prompt for the SQL Server host name, TCP/IP port number, the database name, user name, password and path to JDBC driver. The wizard will report any connectivity errors.

When you log in to the Collaborator web client for the first time after installation, it detects that you have a new database and will create all tables, indexes, and views for you automatically.

Important notes:

• When creating the database, be sure to select a case insensitive collation so that the case of column names (and user names and text searches) does not matter.

• It is recommended that you create a user account just for Collaborator and give this account at a minimum db_owner permissions to Collaborator database and no access to other databases.

• We recommend using a native SQL Server account authentication instead of Windows-based authentication, because the Collaborator service might not be running under a normal Windows-based login. SQL Server uses its native authentication by default, so no additional actions will be required. If you decide to use Windows-based authentication, you may need to change the Collaborator settings – see below.

• Collaborator uses the TCP/IP protocol for data exchange. By default, in SQL Server this protocol is disabled. To enable it, open the SQL Server Configuration Manager and navigate to Network Configuration > Protocols. Make sure TCP/IP is enabled.

• By default, SQL Server is configured to use dynamic ports, which means that the port used is changed each time the service is restarted. To use a static port instead, open the TCP/IP settings and change the IPAll setting value. Clear the TCP Dynamic Ports check box and specify the desired port number in TCP Port.

We recommend using a native SQL Server account authentication instead of Windows-based authentication, because the Collaborator service might not be running under a normal Windows-based login. Besides, Windows authentication is not available on *nix platforms. However, if you want to use Windows account, you may need to change the Collaborator connection settings:

1. After downloading the driver package the link above, extract the files.

2. Copy the platform-specific sqljdbc_auth.dll file to the tomcat subfolder of your Collaborator installation folder.

3. Open the following file in any text or XML editor:

   <Collaborator Server>/tomcat/conf/Catalina/localhost/ROOT.xml

4. Insert the IntegratedSecurity=true value into the url attribute of the <Resource driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" ... > element that describes the JDBC configuration.

   After the change, the element should look like this:

   <Resource driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" ...
   url="jdbc:sqlserver://SERVER:1433;databaseName=DATABASE-NAME;IntegratedSecurity=true" ...
   username="DOMAIN\USERNAME" validationQuery="select 1 ... " />

5. Restart the Collaborator service.

• If you get the The TCP/IP connection to the host has failed. java.net.UnknownHostException error, you probably need to enable TCP/IP for your database. See Configure Database above.

• By default, when Collaborator is installed, the service runs under the Local System account. If you are using SQL Server authentication rather Windows-based authentication, the local system account must have the permissions necessary to communicate with the database.

  An error like Error: Login failed for user 'domain\user$' indicates that the local system account does not have these permissions. To handle this situation, you can either grant your Local System account permissions to your database server, or edit the account the Collaborator service is using. To edit the account:

  1. On your Collaborator server computer, from the Control Panel > System and Security > Administrative Tools, open Services.

  2. Find ccollab-server in the service list and stop the service.

  3. Right-click on ccollab-server, and select Properties from the context menu. This will open the Properties dialog.

  4. Go to the Log On tab.

  5. Select This account and then specify the name and password for the account you want the Collaborator service to operate.

     Note:

     The account that you specify must have sufficient privileges on the machine, where the service is running (for instance, file system privileges for folder creation to allow the service create temporary subfolders in the Collaborator server installation folder).

  6. Click Apply.

  7. Start the ccollab-server service.

• If you are using SQL Server with named instances, it is important to know your port configuration. By default, named instances use dynamic ports. This means, every time SQL Server is restarted, it will search for available ports and assign one to your database. In this situation, you might have trouble finding the port upon which your database is running and could see errors when trying to connect to your database with Collaborator.

  Possible solutions:

  • If you are using named instances with dynamic ports, do not specify a port when providing Collaborator your SQL Server connection information. By leaving the port unspecified, a request will be sent to your SQL Server instance on port 1434 that will search for the port your database is running on and will then send that information back to Collaborator.

  • Alternatively, try using a static port. See Configure Database above.

• If you get the error "Unable to connect to database: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption" you probably use JDBC after 10.2, where TLS encryption is enabled by default. To fix problem add a parameter ";encrypt=false" to connection string:

  url=jdbc:sqlserver://<sqlserver hostname>:<db port>;databasename=<db name>;encrypt=false