61

my sql server instance name is MYPC\SQLEXPRESS and I'm trying to create a jTDS connection string to connect to the database 'Blog'. Can anyone please help me accomplish that?

I'm trying to do like this:

DriverManager.getConnection("jdbc:jtds:sqlserver://127.0.0.1:1433/Blog", "user", "password");

and I get this:

 java.sql.SQLException: Network error IOException: Connection refused: connect
    at net.sourceforge.jtds.jdbc.ConnectionJDBC2.<init>(ConnectionJDBC2.java:395)
    at net.sourceforge.jtds.jdbc.ConnectionJDBC3.<init>(ConnectionJDBC3.java:50)
    at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:184)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at SqlConnection.Connect(SqlConnection.java:19)
    at main.main(main.java:11)
Caused by: java.net.ConnectException: Connection refused: connect
    at java.net.PlainSocketImpl.socketConnect(Native Method)
    at java.net.PlainSocketImpl.doConnect(Unknown Source)
    at java.net.PlainSocketImpl.connectToAddress(Unknown Source)
    at java.net.PlainSocketImpl.connect(Unknown Source)
    at java.net.SocksSocketImpl.connect(Unknown Source)
    at java.net.Socket.connect(Unknown Source)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at net.sourceforge.jtds.jdbc.SharedSocket.createSocketForJDBC3(SharedSocket.java:305)
    at net.sourceforge.jtds.jdbc.SharedSocket.<init>(SharedSocket.java:255)
    at net.sourceforge.jtds.jdbc.ConnectionJDBC2.<init>(ConnectionJDBC2.java:323)
    ... 6 more
0

5 Answers 5

124

As detailed in the jTDS Frequenlty Asked Questions, the URL format for jTDS is:

jdbc:jtds:<server_type>://<server>[:<port>][/<database>][;<property>=<value>[;...]]

So, to connect to a database called "Blog" hosted by a MS SQL Server running on MYPC, you may end up with something like this:

jdbc:jtds:sqlserver://MYPC:1433/Blog;instance=SQLEXPRESS;user=sa;password=s3cr3t

Or, if you prefer to use getConnection(url, "sa", "s3cr3t"):

jdbc:jtds:sqlserver://MYPC:1433/Blog;instance=SQLEXPRESS

EDIT: Regarding your Connection refused error, double check that you're running SQL Server on port 1433, that the service is running and that you don't have a firewall blocking incoming connections.

7
  • 4
    your last connection string worked, after i've enabled some tcp, via, pipes in the Protocols for SQLEXPRESS in the sql server configuration manager
    – Omu
    Commented Dec 8, 2009 at 10:50
  • 2
    Thanks mate, the first one worked for me using SQL SERVER 2000
    – sys_debug
    Commented Mar 17, 2012 at 7:58
  • 2
    add a instance property for datasources and don't add the instance to the databaseName.
    – jan
    Commented Apr 25, 2014 at 7:55
  • 2
    Thanks. This is what worked for me when trying to configure it to use windows authentication: jdbc:jtds:sqlserver://[servername]:1433/[DBName];domain=[DomainName];user=[username];password=[password] Commented Jun 23, 2015 at 3:55
  • 3
    7 years later and this post helped me out big time. A tip for finding your sql server's IP: Login with "tcp:" in front of your credentials and then run this query: SELECT c.local_net_address FROM sys.dm_exec_connections AS c WHERE c.session_id = @@SPID;
    – Garrett
    Commented Nov 18, 2016 at 17:42
45

Really, really, really check if the TCP/IP protocol is enabled in your local SQLEXPRESS instance.

Follow these steps to make sure:

  • Open "Sql Server Configuration Manager" in "Start Menu\Programs\Microsoft SQL Server 2012\Configuration Tools\"
  • Expand "SQL Server Network Configuration"
  • Go in "Protocols for SQLEXPRESS"
  • Enable TCP/IP

If you have any problem, check this blog post for details, as it contains screenshots and much more info.

Also check if the "SQL Server Browser" windows service is activated and running:

  • Go to Control Panel -> Administrative Tools -> Services
  • Open "SQL Server Browser" service and enable it (make it manual or automatic, depends on your needs)
  • Start it.

That's it.

After I installed a fresh local SQLExpress, all I had to do was to enable TCP/IP and start the SQL Server Browser service.

Below a code I use to test the SQLEXPRESS local connection. Of course, you should change the IP, DatabaseName and user/password as needed.:

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class JtdsSqlExpressInstanceConnect {
    public static void main(String[] args) throws SQLException {
        Connection conn = null;
        ResultSet rs = null;
        String url = "jdbc:jtds:sqlserver://127.0.0.1;instance=SQLEXPRESS;DatabaseName=master";
        String driver = "net.sourceforge.jtds.jdbc.Driver";
        String userName = "user";
        String password = "password";
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, userName, password);
            System.out.println("Connected to the database!!! Getting table list...");
            DatabaseMetaData dbm = conn.getMetaData();
            rs = dbm.getTables(null, null, "%", new String[] { "TABLE" });
            while (rs.next()) { System.out.println(rs.getString("TABLE_NAME")); }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            conn.close();
            rs.close();
        }
    }
}

And if you use Maven, add this to your pom.xml:

<dependency>
    <groupId>net.sourceforge.jtds</groupId>
    <artifactId>jtds</artifactId>
    <version>1.2.4</version>
</dependency>
2
  • 2
    And make sure that you are using Static port = 1433 - not a dynamic Port under your 'IP All' settings. Commented Apr 16, 2013 at 6:54
  • 2
    Wow the "SQL Server Browser" is what did it for me, FINALLY thanks so much. Can anyone explain what this does though?
    – protango
    Commented Nov 2, 2018 at 4:56
9

jdbc:jtds:sqlserver://x.x.x.x/database replacing x.x.x.x with the IP or hostname of your SQL Server machine.

jdbc:jtds:sqlserver://MYPC/Blog;instance=SQLEXPRESS

or

jdbc:jtds:sqlserver://MYPC:1433/Blog;instance=SQLEXPRESS

If you are wanting to set the username and password in the connection string too instead of against a connection object separately:

jdbc:jtds:sqlserver://MYPC/Blog;instance=SQLEXPRESS;user=foo;password=bar

(Updated my incorrect information and add reference to the instance syntax)

3
  • 1
    where do you specify the DB name ?
    – Omu
    Commented Dec 7, 2009 at 19:30
  • 1
    As Pratik and Pascal mentioned, you should verify that the server is running on port 1433. Commented Dec 7, 2009 at 23:36
  • 1
    sqlexpress is not the database name, it is the instance name Commented Jul 18, 2010 at 0:45
4

A shot in the dark, but From the looks of your error message, it seems that either the sqlserver instance is not running on port 1433 or something is blocking the requests to that port

-1

SQLServer runs the default instance over port 1433. If you specify the port as port 1433, SQLServer will only look for the default instance. The name of the default instance was created at setup and usually is SQLEXPRESSxxx_xx_ENU.

The instance name also matches the folder name created in Program Files -> Microsoft SQL Server. So if you look there and see one folder named SQLEXPRESSxxx_xx_ENU it is the default instance.

Folders named MSSQL12.myInstanceName (for SQLServer 2012) are named instances in SQL Server and are not accessed via port 1433.

So if your program is accessing a default instance in the database, specify port 1433, and you may not need to specify the instance name.

If your program is accessing a named instance (not the default instance) in the database DO NOT specify the port but you must specify the instance name.

I hope this clarifies some of the confusion emanating from the errors above.

2
  • 1
    "SQLServer runs the default instance over port 1433" No, it uses dynamic ports, by default. Also, I never specify the instance name, so I do not have to run SQL browser. Every instance listens on a different IP/port combination, two processes CANNOT listen on the one same IP/port combination.
    – thecarpy
    Commented Jun 26, 2015 at 13:25
  • 1
    I use named instances with fixed port, this does only apply on default parameters
    – Hybris95
    Commented Jul 3, 2017 at 13:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.