Using Liquibase and MSSQL Server with Windows Integrated Security

You can use Liquibase to manage changes to your Microsoft SQL Server database. If your application runs on a Windows-based intranet, you can also use Windows Integrated Security to access your database.

To do this, you must first complete the integrated security setup complete on your server. For more information, see How to: Access SQL Server Using Windows Integrated Security. Then you can set up Liquibase to manage your changes.

To set up Liquibase with MSSQL without using Windows Integrated Security, see Using Liquibase with MSSQL.

Prerequisites

Before using Liquibase with your database, ensure you have:

Driver information

To use Liquibase and Microsoft SQL Server, you need to have the JDBC driver .jar file. Liquibase comes with a pre-installed driver for MSSQL in the liquibase/lib directory. For more information, see Adding and Updating Liquibase Drivers.

Note: If you place the mssql-jdbc-<version>.jre<version>.jar file in a different directory, specify the path in the Liquibase properties file, as follows: classpath:../path_to_drivers/mssql-jdbc-<version>.jre<version>.jar. For more information, see Specifying Properties in a Connection Profile.

If you install a non-default driver for MSSQL, make sure the JDBC driver is version 9.4+ to avoid getting the following error: "This driver is not configured for integrated authentication." Then set the filepath of the driver file mssql-jdbc_auth-<version>.x64.dll with the PATH environment variable.

Setup

  1. Ensure that you have set the Liquibase url property in the Liquibase properties file or with Liquibase Environment Variables:

    jdbc:sqlserver://hostname;portNumber=1433;databaseName=databaseName;integratedSecurity=true;

    Replace hostname with your actual hostname and databaseName with your actual database name. You don't have to set username and password because the authentication is established on the operating system thread to access the SQL Server database.

  2. Ensure your SQL Server ports are open to communicate with the server.
  3. Ensure with your IT admin that an inbound firewall rule for SQL Server ports 1433 TCP/IP and 1434 UDP/IP is enabled.
  4. Restart the Server to take the new changes.

  5. Create a new changelog file called myChangeLog.sql with the following create table salesTableZ changeset:

    -- liquibase formatted sql
-- changeset TsviZ:createTable_salesTableZ-1221
CREATE TABLE salesTableZ (
ID int NOT NULL,
NAME varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
REGION varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
MARKET varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
--rollback DROP TABLE salesTableZ

Testing Your Connection

  1. Navigate to your project folder in the CLI and run the Liquibase status command to see whether the connection is successful. You can pass arguments in the CLI or keep them in the Liquibase properties file.
    2. liquibase --username=test --password=test --changelog-file=<changelog.xml> status
  2. Run your first update with the update command, which makes changes to your database. You can also run the update-sql command to inspect the SQL before running the update command.
    3. liquibase --changelog-file=<changelog.xml> update-sql
liquibase --changelog-file=<changelog.xml> update

For more information, including a list of supported commands and Change Types in MSSQL, see Using Liquibase with MSSQL.

Related links