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

Install drivers

To use Liquibase and Microsoft SQL Server, you need the JDBC driver JAR file (Maven download).

The latest version of Liquibase has a pre-installed driver for this database in the liquibase/internal/lib directory. Read more: Adding and Updating Liquibase Drivers.

If you use Maven, you must include the driver JAR as a dependency in your pom.xml file. Read more: Configuring Liquibase Attributes in your Maven POM File.

<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <version>10.2.0.jre8</version>
</dependency>

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. Place your non-default driver in the liquibase/lib directory.

Test your connection

  1. Ensure your MSSQL database is configured:

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

  2. Specify the database URL in the Liquibase properties file:

    url: 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.

    Tip: To apply a Liquibase Pro key to your project, add the following property to the Liquibase properties file: licenseKey: <paste code here>

  3. 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
  4. Navigate to your project folder in the CLI and run the Liquibase status command to see whether the connection is successful:
    5. liquibase status --username=test --password=test --changelog-file=<changelog.xml>

  5. Note: You can pass arguments in the CLI or keep them in the Liquibase properties file.

  6. Inspect the SQL with the update-sql command. Then make changes to your database with the update command:
    7. liquibase update-sql --changelog-file=<changelog.xml>
liquibase update --changelog-file=<changelog.xml>
  7. From a database UI tool, ensure that your database contains the table you added along with the DATABASECHANGELOG table and DATABASECHANGELOGLOCK table.

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

Related links