Help me create a jTDS connection string

Question

my sql server instance name is MYPC \ SQLEXPRESS, and I'm trying to create a jTDS connection string to connect to the Blog database. Can someone help me with this?

I'm trying to do like this:

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

and I get the following:

  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 

Answer 1

As detailed in the jTDS Frequently 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 on MYPC SQL Server running MYPC , you can get 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: As for your Connection refused error, double-check that you are using SQL Server on port 1433, that the service is running, and that you do not have a firewall blocking incoming connections.

Answer 2

Indeed, indeed, do verify that TCP / IP is enabled on your local SQLEXPRESS instance.

Follow these steps to verify:

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

If you are having problems, check this blog post for more details, as it contains screenshots and more.

Also check if the Windows Server Browser service is activated and working :

• Go to Control Panel → Administrative Tools → Services
• Open SQL Server Service Server and enable it (make it manual or automatic, depending on your needs)
• Run it.

What is it.

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

Below is the code I use to test the local SQLEXPRESS connection. Of course, you should change the IP, database_name, 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 are using Maven, add this to your pom.xml:

 <dependency> <groupId>net.sourceforge.jtds</groupId> <artifactId>jtds</artifactId> <version>1.2.4</version> </dependency> 

Answer 3

jdbc:jtds:sqlserver://xxxx/database replacing xxxx 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 want to set the username and password in the connection string, and not separately from the connection object:

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

(Updated my incorrect information and added reference to instance syntax )

Answer 4

The picture is in the dark, but From the look of your error message, it seems that either the sqlserver instance is not running on port 1433, or something is blocking requests to this port

Answer 5

SQLServer starts the default instance through port 1433. If you specify the port as port 1433, SQLServer will only look for the default instance. The default instance name was created during setup, and usually it is SQLEXPRESSxxx_xx_ENU.

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

Folders named MSSQL12.myInstanceName (for SQLServer 2012) are called instances in SQL Server and are not accessible through port 1433.

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

If your program accesses a named instance (and not the default instance) in the database NOT , enter the port, but you must specify the name of the instance.

Hopefully this will explain some of the confusion stemming from the above errors.