What is the correct syntax for a Flyway connection string to SQL Server?

Question

Running SQL Server 2012 Express on a remote machine, trying to get Flyway up and running. I have a database on pcesqldev.pce.local called Hawk (dbo.Hawk, if that matters) that I want to connect to, and the template from the config file looks like this:

SQL Server        : jdbc:jtds:sqlserver://<host>:<port>/<database>

Note, this is different from other jdbc connection strings I have used with other products - most of them do not include the jtds portion and do include the instance name.

Here's a few connection strings that I have tried, all of which failed:

flyway.url=jdbc:jtds:sqlserver://pcesqldev.pce.local:1433/Hawk

Network error IOException: Connection refused: connect

flyway.url=jdbc:jtds:sqlserver://pcesqldev.pce.local\SQLEXPRESS:1433/Hawk

Unknown server host name 'pcesqldev.pce.local\SQLEXPRESS'

flyway.url=jdbc:jtds:sqlserver://pcesqldev.pce.local/SQLEXPRESS:1433/Hawk

Network error IOException: Connection refused: connect

flyway.url=jdbc:jtds:sqlserver://pcesqldev.pce.local:1433/SQLEXPRESS\Hawk

Network error IOException: Connection refused: connect

What am I missing? There must be something obvious, but I can't see it.

Before anybody asks, yes we do have TCP access to the database enabled and it is using port 1433.

score 9 · Accepted Answer · answered Mar 15 '16 at 20:33

9

This one got me, and there was not many answers out there on how to format a connection string with an instance name.

Here's what worked for me:

flyway.url=jdbc:jtds:sqlserver://<host>:<port>/<database>;instance=<instance_name>

answered Mar 15 '16 at 20:33

tbonz

1,087
10
15

This looks promising. I'm in a crunch to get this release done but will try your suggestion as soon as I get a break and let you know how it works. – DaveN59 Mar 28 '16 at 20:32
How can we pass query params like jdbc:mariadb:aurora://myHost/db?socketTimeout=0 – Nayeem Feb 03 '21 at 21:27

score 5 · Answer 2 · answered Oct 05 '15 at 11:58

5

In case anyone's problems, the correct connection string is as follows:

flyway.url=jdbc:jtds:sqlserver://SERVER_INSTANCE_NAME:1433/DB_NAME

It took me some time to realize that, but maybe it will be helpful for someone :)

answered Oct 05 '15 at 11:58

kamil-mrzyglod

4,948
1
20
29

score 3 · Answer 3 · answered Feb 01 '17 at 20:30

This did my head in for a bit.

The connection string which I used was this (passed as parameters to flyway on the commandline).

Note also that the mydatabasename needed to already exist.

./flyway migrate -url=jdbc:jtds:sqlserver://localhost:1433/mydatabasename -user=myuser -password=mypassword -baselineVersion=269 -baselineDescription="Base version" -outOfOrder=true -baselineOnMigrate=

A piece that was missing though was that I wasn't running SQL Server Browser and possibly didn't have TCP set up correctly:

From the SQL Server section here. After the installation is complete, enable TCP/IP:

Launch the Sql Server Configuration Manager Go to SQL Server Network Configuration -> Protocols for SQLEXPRESS Enable TCP/IP TCP/IP Properties -> IP Addresses -> IPAll TCP Dynamic Ports: blank TCP Port: 1433 Then enable remote access:

Launch the Sql Server Configuration Manager SQL Server Services -> SQL Server Browser -> Properties -> Service Tab Start Mode: Automatic OK SQL Server Browser -> Start SQL Server -> Restart

score 0 · Answer 4 · edited Aug 01 '19 at 22:12

My problem was solved starting the service "SQL Server Agent (SQLEXPRESS)"

Steps:

Windows + R -> It'll open "Run" window
Type "services.msc" without double quotes ("")
Search for SQL Server Agent service
Right click over "SQL Server Agent" and go to Properties
Change "Startup type" to "Automatic" and apply the configuration
After clicked over "Apply" button, it'll enable "Start" button, click over this button
After the service has been started, try to execute flyway again

Image Example

What is the correct syntax for a Flyway connection string to SQL Server?

4 Answers4