documentation/Reference/Database Driver Reference/SQL Server Database.md
{% include database-boilerplate.html %}
| Item | Details |
|---|---|
| URL format | <code>jdbc:sqlserver://<i>host</i>:<i>port</i>;databaseName=<i>database</i></code> |
| SSL support | Yes - add ;encrypt=true |
| Ships with Flyway Command-line | Yes |
| Maven Central coordinates | com.microsoft.sqlserver:mssql-jdbc |
| Supported versions | 10.0 and later |
| Default Java class | com.microsoft.sqlserver.jdbc.SQLServerDriver |
SQL-Server-specific configuration can be found here.
SQL Server support is a separate dependency for Flyway and will need to be added to your Java project to access these features.
<dependency>
<groupId>com.redgate.flyway</groupId>
<artifactId>flyway-sqlserver</artifactId>
</dependency>
<dependency>
<groupId>org.flywaydb</groupId>
<artifactId>flyway-sqlserver</artifactId>
</dependency>
buildscript {
dependencies {
implementation "com.redgate.flyway:flyway-sqlserver"
}
}
buildscript {
dependencies {
implementation "org.flywaydb:flyway-sqlserver"
}
}
/* Single line comment */
CREATE TABLE Customers (
CustomerId smallint identity(1,1),
Name nvarchar(255),
Priority tinyint
)
CREATE TABLE Sales (
TransactionId smallint identity(1,1),
CustomerId smallint,
[Net Amount] int,
Completed bit
)
GO
/*
Multi-line
comment
*/
-- TSQL
CREATE TRIGGER dbo.Update_Customer_Priority
ON dbo.Sales
AFTER INSERT, UPDATE, DELETE
AS
WITH CTE AS (
select CustomerId from inserted
union
select CustomerId from deleted
)
UPDATE Customers
SET
Priority =
case
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
end
FROM Customers c
INNER JOIN CTE ON CTE.CustomerId = c.CustomerId
LEFT JOIN (
select
Sales.CustomerId,
SUM([Net Amount]) Total
from Sales
inner join CTE on CTE.CustomerId = Sales.CustomerId
where
Completed = 1
group by Sales.CustomerId
) t ON t.CustomerId = c.CustomerId
GO
-- Placeholder
INSERT INTO ${tableName} (name) VALUES ('Mr. T');
For Flyway Desktop, please see using SQL Server with Flyway Desktop. SQL Server supports several methods of authentication. These include:
SQL Server Authentication works 'out-of-the-box' with Flyway, whereas the others 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.
This uses a straightforward username and password to authenticate. Provide these with the user and password configuration options.
Windows Authentication, also known as Integrated Security, is enabled by amending
your JDBC connection string to set integratedSecurity=true.
Syntax:
jdbc:sqlserver://<host>:<port>;databaseName=<dbname>;integratedSecurity=true.
Example:
jdbc:sqlserver://server01:1234;databaseName=AdventureWorks;integratedSecurity=true.
You must add Microsoft's MSAL4J library to your classpath. For instance, as a Maven or Gradle dependency. For Microsoft Entra MSI, Azure Identity is also required to be added to your classpath.
For command-line users, MSAL4J and Azure Identity are already included, so no extra installation is required.
There are several types of Microsoft Entra authentication:
To use the various authentication types, amend your JDBC URL to set the authentication parameter:
authentication=ActiveDirectoryIntegrated
authentication=ActiveDirectoryMSI
authentication=ActiveDirectoryPassword
user and password configuration optionsauthentication=ActiveDirectoryInteractive
authentication=ActiveDirectoryServicePrincipal
The Microsoft documentation has more details about how these work with JDBC URLs .
Another way to authenticate using Microsoft Entra is through access tokens. As of the time of writing, the access token property on Microsoft's JDBC driver can't be
supplied through the URL. You should use Flyway's jdbcProperties configuration property instead.
E.g, in a flyway.toml file:
[environments.default]
jdbcProperties.accessToken="my-access-token"
This is equivalent to
the process of setting accessToken as described on this Microsoft documentation page
.
{% include teams.html %}
Kerberos authentication can also be used to connect Flyway to your database.
To set this up, you will need to pass the path to your Kerberos configuration file to the parameter kerberosConfigFileand the path to your login module configuration file to the parameter sqlserver.kerberos.login.file.
You may also need to add ;authenticationScheme=JavaKerberos to your JDBC URL.
For more information on Kerberos authentication with SQL Server, you can read the official documentation here.
When connecting to a named instance, the JDBC URL must be of the form:
jdbc:sqlserver://<server_name>;instanceName=<instance_name>;databaseName=<database_name>
For example:
jdbc:sqlserver://test_server;instanceName=test_instance;databaseName=test_database
Note: If a named instance is used along with the <host>:<port> syntax in the JDBC URL, the driver will connect to the port over the named instance.
CREATE/ALTER/DROP statements acting on memory-optimized tables (that is, those created with
WITH (MEMORY_OPTIMIZED = ON)). You will need to override the executeInTransaction setting to be false,
on a per-script basis.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.flyway.schemas=custom_schema, as the first entry will become the
default schema if flyway.defaultSchema itself is not set.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.