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.
- Install Liquibase.
- Create a Liquibase project folder to store all Liquibase files. You can do this manually or with the init project command.
- Create a new Liquibase properties file or use the
liquibase.propertiesfile included in the installation package. For more information, see Specifying Properties in a Connection Profile.
If you use Maven, you also need to download the driver JAR file and configure your Maven
pom.xml file to use the local copy of the driver. For example:
<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
Test your connection
Ensure your MSSQL database is configured:
- Ensure your SQL Server ports are open to communicate with the server.
- Ensure with your IT admin that an inbound firewall rule for SQL Server ports 1433 TCP/IP and 1434 UDP/IP is enabled.
- Restart the Server to take the new changes.
Specify the database URL in the Liquibase properties file:
hostnamewith your actual hostname and
databaseNamewith your actual database name. You don't have to set
passwordbecause 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:
liquibaseProLicenseKey: <paste code here>
Create a new changelog file called
myChangeLog.sqlwith the following create table
-- 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:
Note: You can pass arguments in the CLI or keep them in the Liquibase properties file.
- Inspect the SQL with the update-sql command. Then make changes to your database with the update command:
- From a database UI tool, ensure that your database contains the table you added along with the DATABASECHANGELOG table and DATABASECHANGELOGLOCK table.
liquibase --username=test --password=test --changelog-file=<changelog.xml> status
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.