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 Microsoft SQL Server.

Prerequisites

Introduction to Liquibase: Dive into Liquibase concepts.
Install Liquibase: Download Liquibase on your machine.
Ensure Java is installed: Liquibase requires Java to run. If you used the Liquibase Installer, Java is included automatically. Otherwise, you must install Java manually.
Get Started with Liquibase: Learn how to use Liquibase with an example database.
Design Your Liquibase Project: Create a new Liquibase project folder and organize your changelogs.
How to Apply Your Liquibase Pro License Key: If you use Liquibase Pro, activate your license.

Set up your SQL Server database with Windows Integrated Security.

Install drivers

CLI users

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, so you don't need to install it yourself.

Maven users

To use Liquibase with Maven, include the driver JAR(s) as a dependency in your pom.xml file. Using this information, Maven automatically downloads the driver JAR from Maven Central when you build your project.

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

Non-default driver

If you need to install a non-default driver for MSSQL, follow these steps:

Ensure the JDBC driver is version 9.4+ to avoid getting the following error: "This driver is not configured for integrated authentication."
Place your non-default driver in the liquibase/lib directory.
Edit the PATH environment variable: add the filepath of the driver file mssql-jdbc_auth-<version>.x64.dll.

Configure connection

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

Test connection

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

Navigate to your project folder in the CLI and run the Liquibase status command to see whether the connection is successful:

liquibase status --changelog-file=myChangeLog.sql

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

If your connection is successful, you'll see a message like this:

4 changesets have not been applied to <your_jdbc_url>
Liquibase command 'status' was executed successfully.

Inspect the SQL with the update-sql command. Then make changes to your database with the update command.

liquibase update-sql --changelog-file=myChangeLog.sql
liquibase update --changelog-file=myChangeLog.sql

If your update is successful, Liquibase runs each changeset and displays a summary message ending with:

Liquibase: Update has been successful.
Liquibase command 'update' was executed successfully.

From a database UI tool, ensure that your database contains the salesTableZ object you added along with the DATABASECHANGELOG table and DATABASECHANGELOGLOCK table.

Now you're ready to start making deployments with Liquibase!

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