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

Ensure you have completed the following steps:

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 put the mssql-jdbc-<version>.jre<version>.jar file in any other directory, specify the path to it in the liquibase.properties file: classpath:../path_to_drivers/mssql-jdbc-<version>.jre<version>.jar. For more information, see Creating and configuring a liquibase.properties file.

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 your 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. Run the status --verbose command to see whether the connection is successful. Specify the name of the changelog you created in place of changelog.xml:

    liquibase --changeLogFile=changelog.xml status

    Note: Alternatively, you can add the changeLogFileattribute to your liquibase.properties file.

  2. Run your first update with the Liquibase update command, which will make changes to your database. You can also run the updateSQL command if you want to inspect the SQL before running the update command.

    liquibase --changeLogFile=changelog.xml updateSQL
    liquibase --changeLogFile=changelog.xml update

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

Related links