Microsoft SQL Server

Last modified 15 Feb 2024 14:09 +01:00
Deprecated
This functionality is deprecated since version 4.4. The functionality is still supported and maintained, but it will no longer be extended. The plan is to remove this functionality sooner or later. Users of this functionality are strongly encouraged to stop using this functionality and migrate to a newer equivalent.
Table of Contents

Tested SQL Server Collations:

  • SQL_Latin1_General_CP1_CS_AS

  • SQL_Czech_CP1250_CS_AS

Be aware that using CS (case-sensitive) collation causes column names in queries to be case-sensitive too!

Database create

The following script is written for sqlcmd and may require adjustments for other SQL clients:

CREATE DATABASE midtest
    COLLATE SQL_Latin1_General_CP1_CS_AS;
GO

ALTER DATABASE midtest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE midtest SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE midtest SET MULTI_USER;

CREATE LOGIN midtest WITH PASSWORD = 'password', CHECK_POLICY = OFF;
GO

USE midtest;
GO

CREATE USER [midtest] FOR LOGIN [midtest] WITH DEFAULT_SCHEMA=[dbo];
GO
ALTER ROLE [db_owner] ADD MEMBER [midtest];
GO

Check policy for password is disabled because very simple password is used, you can use proper password instead.

Assuming the commands above are in a file called create-db.sql, run the commands above in sqlcmd:

sqlcmd -b -S localhost -U sa -P 'Pa55word!' -d master -i create-db.sql

Alternatively, run the sqlcmd without the last -i option and run the commands interactively.

Configuration sample

<configuration>
  <midpoint>
    <repository>
      <repositoryServiceFactoryClass>com.evolveum.midpoint.repo.sql.SqlRepositoryFactory</repositoryServiceFactoryClass>
      <database>sqlserver</database>
      <jdbcUsername>midpoint</jdbcUsername>
      <jdbcPassword>password</jdbcPassword>
      <jdbcUrl>jdbc:sqlserver://localhost:1433;database=midpoint</jdbcUrl>
    </repository>
  </midpoint>
</configuration>

Troubleshooting

Current version cannot be determined. The metadata table (m_global_metadata) is missing or inaccessible.

Log snippet:

*******************************************************************************
*** ***
*** Couldn't start midPoint because of a database schema issue.
***
*** ***
*******************************************************************************

Database schema is not compatible with the executing code.

Current version cannot be determined. The metadata table (m_global_metadata) is missing or inaccessible. This indicates the version is below 3.9.
Required version is: 4.0

Reason:

Since version 3.9, midPoint checks for presence of table m_global_metadata and will not start if table is not found.

Resolution:

However, If you have DB wrongly set - missing the ALLOW_SNAPSHOT_ISOLATION option, you will not get usual "SQLServerException: Snapshot isolation transaction failed" but rather confusing error that m_global_metadata table is missing. So you just run "ALTER DATABASE "midpoint" SET ALLOW_SNAPSHOT_ISOLATION ON;" to fix it.

Issue:

MID-5880

Driver

MS SQL Server driver is not provided as a part of midPoint distribution and must be provided separately:

Now you can start midPoint with SQL Server configured in the config.xml (see example above) and check the logs. If the driver is not found, midPoint will complain about it and will not start.

Preparing Docker container for development

Assuming Linux machine with Docker installed, we can quickly get to a working development MS SQL Server. Let’s start with the SQL Server docker container:

docker run --rm --name sql-server -d -p 1433:1433 \
  -e ACCEPT_EULA=Y -e 'SA_PASSWORD=Pa55word!' \
  mcr.microsoft.com/mssql/server:2019-CU15-ubuntu-20.04

Please note, there is no persistent volume when run like this. Read this for examples with persistent volumes.

Now let’s configure it from inside the container:

docker exec -u 0 -it sql-server /bin/bash
# now we're in shell inside the container
cd # let's not litter in /

cat > create-db.sql << EOF
CREATE DATABASE midtest
    COLLATE SQL_Latin1_General_CP1_CS_AS;
GO

ALTER DATABASE midtest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE midtest SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE midtest SET MULTI_USER;

CREATE LOGIN midtest WITH PASSWORD = 'password', CHECK_POLICY = OFF;
GO

USE midtest;
GO

CREATE USER [midtest] FOR LOGIN [midtest] WITH DEFAULT_SCHEMA=[dbo];
GO
ALTER ROLE [db_owner] ADD MEMBER [midtest];
GO
EOF

# now with the file ready, let's initialize the DB:
/opt/mssql-tools/bin/sqlcmd -b -S localhost -U sa -P 'Pa55word!' -d master -i create-db.sql

At this moment the DB is ready and we can connect to it with any reasonable client (e.g. from IDEA Ultimate). Simply execute sqlserver-*-all.sql and also the cleanup script sqlserver.sql from repo/repo-sql-impl-test/sql-procedures if we want to run tests (which is likely).

Alternatively we can wget the files from GitHub directly:

wget https://github.com/Evolveum/midpoint/raw/master/config/sql/generic/sqlserver-4.9-all.sql
wget https://raw.githubusercontent.com/Evolveum/midpoint/master/repo/repo-sql-impl-test/sql-procedures/sqlserver.sql
# now let's use them:
/opt/mssql-tools/bin/sqlcmd -I -b -S localhost -U midtest -P password -d midtest -i sqlserver-4.9-all.sql -i sqlserver.sql

Or for support-4.8 it would look like this:

wget https://raw.githubusercontent.com/Evolveum/midpoint/support-4.8/config/sql/generic/sqlserver-4.8-all.sql
wget https://raw.githubusercontent.com/Evolveum/midpoint/support-4.4/repo/repo-sql-impl-test/sql-procedures/sqlserver.sql
# now let's use them:
/opt/mssql-tools/bin/sqlcmd -I -b -S localhost -U midtest -P password -d midtest -i sqlserver-4.4-all.sql -i sqlserver.sql

To run a test class with the new SQL Server, the following set of JVM args overrides the default H2 repo configuration (don’t use newlines for the command line):

-Dmidpoint.repository.jdbcUrl=jdbc:sqlserver://192.168.56.201:1433;database=midtest;trustServerCertificate=true
-Dmidpoint.repository.jdbcPassword=password
-Dmidpoint.repository.jdbcUsername=midtest
-Dmidpoint.repository.database=sqlserver
-Dmidpoint.repository.hibernateHbm2ddl=validate

Fix the IP to your Docker VM or use localhost if appropriate.

Was this page helpful?
YES NO
Thanks for your feedback