Contact usRequest a demo

Setting up Microsoft SQL Server for Unblu

Unblu doesn’t support group Managed Service Accounts (gMSA) with MS SQL Server.

Collation

For the Unblu Collaboration Server, case sensitive collation is recommended. If you’re unsure which collation to use, choose Latin1_General_100_CS_AS_KS_WS_SC_UTF8.

MSSQL Server setup

To create the unblu database and users on MSSQL Server, run the following commands:

Listing 1. Create the unblu database and users
CREATE DATABASE unblu; (1)
USE unblu;

CREATE LOGIN unblu WITH PASSWORD = 'Unblu_Secret';
CREATE LOGIN unblu_admin WITH PASSWORD = 'Admin_Secret';

CREATE USER unblu_admin FOR LOGIN unblu_admin;
CREATE USER unblu FOR LOGIN unblu;

GRANT CREATE TABLE TO unblu_admin AS dbo;
GRANT ALTER, REFERENCES ON SCHEMA::dbo TO unblu_admin AS dbo;
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO unblu_admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO unblu;
1 If your server collation is case insensitive, you can use the COLLATE clause to set the database collation to be case sensitive:
Listing 2. Create the unblu`database with a `COLLATE clause
CREATE DATABASE unblu COLLATE <collate-name-with-_CS-suffix>;

Next, set the following Unblu configuration properties:

Listing 3. Unblu MSSQL configuration
com.unblu.storage.database.platform=org.eclipse.persistence.platform.database.SQLServerPlatform
com.unblu.storage.database.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
com.unblu.storage.database.url=jdbc\:sqlserver\://<server>\:1433;DatabaseName\=unblu (1)
com.unblu.storage.database.user=unblu
com.unblu.storage.database.password=<user-pwd> (2)
com.unblu.storage.database.schema=dbo
com.unblu.storage.database.liquibaseSchema=dbo
com.unblu.storage.database.adminUser=unblu_admin
com.unblu.storage.database.adminPassword=<admin-pwd> (3)
com.unblu.storage.database.jdbcProperties=sendStringParametersAsUnicode\=false (4)
1 Replace <server> with your database hostname or IP address.
2 Replace <user-pwd> with the password of the unblu user.
3 Replace <admin-pwd> with the password of the unblu_admin user.
4 Prevents implicit type conversions, can lead to performance problems if not set.
Analyze and optimize indexes at least every night to prevent fragmentation.

Limitations using MSSQL Server

If you use Unblu Spark with Microsoft SQL Server, you can’t create entities whose names only differ in their capitalization. For example, you can’t create two teams called Hong Kong advisory and Hong Kong Advisory, respectively.

Furthermore, with MSSQL Server, searching for entities in Unblu always takes into account the capitalization of the search term. In the web API, for example, setting the caseSensitive field of the ContainsStringOperator to false doesn’t have an effect if you’re using MSSQL Server.