Want more control over your database deployments? – Find out how with Carlos Robles at Redgate Summit Register now

Flyway Documentation

SQL Server

Supported Versions

Support Level

Guaranteed Flyway Teams

Support Level determines the degree of support available for this database (learn more).


URL format jdbc:sqlserver://host:port;databaseName=database
SSL support Yes - add ;encrypt=true
Ships with Flyway Command-line Yes
Maven Central coordinates com.microsoft.sqlserver:mssql-jdbc:9.2.1.jre8
Supported versions 4.0 and later
Default Java class com.microsoft.sqlserver.jdbc.SQLServerDriver

SQL Script Syntax


  • DDL exported by SQL Server can be used unchanged in a Flyway migration.
  • Any SQL Server sql script executed by Flyway, can be executed by Sqlcmd, SQL Server Management Studio and other SQL Server-compatible tools (after the placeholders have been replaced).


/* Single line comment */
CREATE TABLE Customers (
CustomerId smallint identity(1,1),
Name nvarchar(255),
Priority tinyint
TransactionId smallint identity(1,1),
CustomerId smallint,
[Net Amount] int,
Completed bit

CREATE TRIGGER dbo.Update_Customer_Priority
 ON dbo.Sales
 select CustomerId from inserted
 select CustomerId from deleted
UPDATE Customers
 Priority =
     when t.Total < 10000 then 3
     when t.Total between 10000 and 50000 then 2
     when t.Total > 50000 then 1
     when t.Total IS NULL then NULL
FROM Customers c
INNER JOIN CTE ON CTE.CustomerId = c.CustomerId
   SUM([Net Amount]) Total
 from Sales
 inner join CTE on CTE.CustomerId = Sales.CustomerId
   Completed = 1
 group by Sales.CustomerId
) t ON t.CustomerId = c.CustomerId

-- Placeholder
INSERT INTO ${tableName} (name) VALUES ('Mr. T');


SQL Server supports several methods of authentication. These include:

  • SQL Server Authentication
  • Windows Authentication
  • Azure Active Directory

SQL Server Authentication works ‘out-of-the-box’ with Flyway, whereas Windows Authentication and Azure Active Directory require extra manual setup.

The instructions provided here are adapted from the Microsoft JDBC Driver for SQL Server documentation. Refer to this when troubleshooting authentication problems.

Note These instructions may be incomplete. Flyway depends on Microsoft’s JDBC drivers, which in turn have many environmental dependencies to enable different authentication types. You may have to perform your own research to get the JDBC driver working for the different authentication types.

SQL Server Authentication

This uses a straightforward username and password to authenticate. Provide these with the user and password configuration options.

Windows Authentication

Windows Authentication, also known as Integrated Security, is enabled by amending your JDBC connection string to set integratedSecurity=true.

Example: jdbc:sqlserver://<i>host</i>:<i>port</i>;databaseName=<i>database</i>;integratedSecurity=true.

Azure Active Directory

Installing MSAL4J

If you’re using Flyway in an environment that is integrated with Maven or Gradle (Like the Maven or Gradle plugin), you only need to add MSAL4J’s Maven package as a dependency in your pom.xml. Maven should then deal with getting MSAL4J’s dependencies itself.

For command-line users, the necessary dependencies are shipped with Flyway so that zero installation is required.


There are several types of Azure Active Directory authentication:

  • Azure Active Directory with MFA
  • Azure Active Directory Integrated
  • Azure Active Directory MSI
  • Azure Active Directory with Password
  • Azure Active Directory Service Principal
  • Access Tokens

To use the various authentication types, amend your JDBC URL to set the authentication parameter:

  • For Active Directory Integrated set authentication=ActiveDirectoryIntegrated
    • e.g: jdbc:sqlserver://host:port;databaseName=database;authentication=ActiveDirectoryIntegrated
  • For Active Directory MSI set authentication=ActiveDirectoryMSI
    • e.g: jdbc:sqlserver://host:port;databaseName=database;authentication=ActiveDirectoryMSI
  • For Active Directory With Password set authentication=ActiveDirectoryPassword
    • e.g: jdbc:sqlserver://host:port;databaseName=database;authentication=ActiveDirectoryPassword
    • You must also supply a username and password with Flyway’s user and password configuration options
  • For Active Directory Interactive set authentication=ActiveDirectoryInteractive
    • e.g: jdbc:sqlserver://host:port;databaseName=database;authentication=ActiveDirectoryInteractive
    • This will begin an interactive process which expects user input (e.g. a dialogue box), so it’s not recommended in automated environments
  • For Active Directory Service Principal set authentication=ActiveDirectoryServicePrincipal
    • e.g: jdbc:sqlserver://host:port;databaseName=database;authentication=ActiveDirectoryServicePrincipal

The Microsoft documentation has more details about how these work with JDBC URLs.

Azure access tokens

Another way to authenticate using Azure Active Directory is through access tokens. As of the time of writing, the access token property on Microsoft’s JDBC driver cannot be supplied through the URL. Therefore you should use Flyway’s jdbcProperties configuration property.

E.g, in a flyway.conf file:


This is equivalent to the process of setting accessToken as described on this Microsoft documentation page.


  • Flyway’s automatic detection for whether SQL statements are valid in transactions does not apply to CREATE/ALTER/DROP statements acting on memory-optimised tables (that is, those created with WITH (MEMORY_OPTIMIZED = ON)). You will need to override the executeInTransaction setting to be false, either on a per-script basis or globally.
  • SQL Server is unable to change the default schema for a session. Therefore, setting the flyway.defaultSchema property has no value, unless used for a Placeholder in your sql scripts. If you decide to use flyway.defaultSchema, it also must exist in flyway.schemas.
  • By default, the flyway schema history table will try to write to the default schema for the database connection. You may specify which schema to write this table to by setting flyway.schemas=custom_schema, as the first entry will become the default schema if flyway.defaultSchema itself is not set.
  • With these limitations in mind, please refer to the properties or Options mentioned here for descriptions / consequences.
  • If using the JTDS driver, then setting ANSI_NULLS or QUOTED_IDENTIFIER in a script will cause an error. This is a driver limitation, and can be solved by using the Microsoft driver instead.

Azure Synapse