Applies to:
- Liquibase Pro
Summary:
If your application runs on a Windows-based intranet, you can use Windows Integrated Authentication to access your database.
Integrated security uses the current Windows identity established on the operating system thread to access your SQL Server database and permissions.
Once you have the integrated security setup complete on your server, you can set up Liquibase to manage your changes.
Here are some general instructions from Microsoft's documentation about setting up integrated security with SQL Server: How to: Access SQL Server Using Windows Integrated Security
Here is some additional information from Microsoft's documentation regarding the required JDBC driver configuration: Connecting with integrated authentication on Windows
Environment Conditions:
- Windows Integrated Security
How to:
Here are a few simple steps to get started with Liquibase and Windows Integrated Security:
-
If you haven’t already installed Liquibase, you can download it here.
-
To try out the benefits of Liquibase Pro, you can sign up for a free 30-day Pro trial here.
-
-
Download the JDBC Driver for SQL Server.
-
Note: Make sure the JDBC driver is version 9.4 or higher to avoid getting the following error: This driver is not configured for integrated authentication
-
-
Unzip the file.
-
Note: You will need both the JDBC jar file and the Auth .dll file found inside the zip file.
-
-
Place mssql-jdbc_auth-.x64.dll in a known path in your file system, and set that path in the PATH environment variable.
-
Note: A typical location would be C:\Windows\system32. This location is already in the PATH for most systems.
-
-
Place the JDBC driver in the /lib directory.
-
Set the Liquibase url property, which can be set with Liquibase's Environment Variables feature.
-
jdbc:sqlserver://hostname;portNumber=1433;databaseName=databaseName;integratedSecurity=true;
-
Replace hostname with your actual hostname and databaseName with your actual database name.
-
Note: No need to set the username and password as the authentication will be established on the operating system thread to access the SQL Server database.
-
-
Ensure your SQL Server ports are open to communicate with the server.
-
Ensure with your IT admin that a firewall inbound rule for SQL Server ports 1433 TCP/IP and 1434 UDP/IP is enabled.
-
Restart the server to take the new changes.
-
To test the connection, create a new changelog file called myChangeLog.sql with a test script like the following "create table salesTableZ" changeset:
-
-- liquibase formatted sql
--changeset liquibase: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 - Run the following in the command line.
-
liquibase --changeLogFile=path/to/myChangeLog.sql status --verbose
-
Note: Replace the path/to/changelog.sql with the actual relative path in your system.
-
Sample output:
-
C:\Users\LBUSER\MSSQL_WITH_AUTHliquibase status --verbose
Starting Liquibase at 11:52:34 (version 4.4.3 #53 built at 2021-08-05 18:32+0000)
Liquibase Version: 4.4.3
Liquibase Community 4.4.3 by Datical
1 change sets have not been applied to MicrosoftAccount\user@acme.com@jdbc:sqlserver://DESKTOP-1S4JDRTG:1433;connectRetryInterval=10;connectRetryCount=1;maxResultBuffer=-1;sendTemporalDataTypesAsStringForBulkCopy=true;delayLoadingLobs=true;useFmtOnly=false;useBulkCopyForBatchInsert=false;cancelQueryTimeout=-1;sslProtocol=TLS;jaasConfigurationName=SQLJDBCDriver;statementPoolingCacheSize=0;serverPreparedStatementDiscardThreshold=10;enablePrepareOnFirstPreparedStatementCall=false;fips=false;socketTimeout=0;authentication=NotSpecified;authenticationScheme=nativeAuthentication;xopenStates=false;sendTimeAsDatetime=true;replication=false;trustStoreType=JKS;trustServerCertificate=false;TransparentNetworkIPResolution=true;serverNameAsACE=false;sendStringParametersAsUnicode=true;selectMethod=direct;responseBuffering=adaptive;queryTimeout=-1;packetSize=8000;multiSubnetFailover=false;loginTimeout=15;lockTimeout=-1;lastUpdateCount=true;encrypt=false;disableStatementPooling=true;databaseName=myDB;columnEncryptionSetting=Disabled;applicationName=Microsoft JDBC Driver for SQL Server;applicationIntent=readwrite;
myChangelog.sql::createTable_salesTableZ-1221::TsviZ
Liquibase command 'status' was executed successfully.
-
-
-
-
If the status command fails, make sure to review and follow steps 1 to 12 before trying to rerun the status command again.
- For more detailed instructions about setting the environment variables, please see our doc page here.
- Once the status command is successful, you can run the `liquibase update` command to apply the new change.
Related Article(s):
Using Liquibase and MS SQL Server with Windows Integrated Security
Comments
0 comments
Article is closed for comments.