2

I've designed a database management system using Java and MySQL. I used WAMP as the server. Everything was perfect with that..until now..

Now, I have to go for SQL Express instead of WAMP. I know nothing about SQL Express. I've installed it under defaults.

However, now I can connect to the database in Microsoft SQL Server Management Studio with following selections, (LORDXAX-PC is my computer's name)

enter image description here

But I need to connect the database with my Java program. (I've used JDBC successfully before, with WAMP)

Need help to make this a success?? I've tried searching found certain things, but it gives errors. Can someone provide me with clean instructions..

Any help is appreciated. Thanks!

EDIT (2)

This is a complete code demonstrating the issue..

package ExpressTest;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
public class MAIN {
 public static void main(String[] args) {
 new MAIN().start();
 }
 private void start() {
 System.out.println("START");
 String query = "SELECT * FROM logindata";
 getData(query);
 System.out.println("END");
 }
 public ResultSet getData(String query) {
 Connection CON = null;
 try {
 Class.forName("net.sourceforge.jtds.jdbc.Driver");
 CON = DriverManager.getConnection(URL, USER, PASS);
 RESULT = CON.createStatement().executeQuery(query);
 } catch (Exception ex) {
 ex.printStackTrace();
 }
 return RESULT;
 }
 private String URL = "jdbc:jtds:sqlserver://localhost/brandix;instance=sqlexpress";
 private String USER = "";
 private String PASS = "";
 private static ResultSet RESULT;
}

This is the complete NetBeans output.

run:
START
java.sql.SQLException: Network error IOException: Connection refused: connect
 at net.sourceforge.jtds.jdbc.ConnectionJDBC2.<init>(ConnectionJDBC2.java:417)
 at net.sourceforge.jtds.jdbc.ConnectionJDBC3.<init>(ConnectionJDBC3.java:50)
 at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:185)
 at java.sql.DriverManager.getConnection(DriverManager.java:582)
 at java.sql.DriverManager.getConnection(DriverManager.java:185)
 at ExpressTest.MAIN.getData(MAIN.java:24)
 at ExpressTest.MAIN.start(MAIN.java:16)
 at ExpressTest.MAIN.main(MAIN.java:10)
Caused by: java.net.ConnectException: Connection refused: connect
 at java.net.PlainSocketImpl.socketConnect(Native Method)
 at java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java:333)
 at java.net.PlainSocketImpl.connectToAddress(PlainSocketImpl.java:195)
 at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:182)
 at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:366)
 at java.net.Socket.connect(Socket.java:519)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
END
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
 at java.lang.reflect.Method.invoke(Method.java:597)
 at net.sourceforge.jtds.jdbc.SharedSocket.createSocketForJDBC3(SharedSocket.java:311)
 at net.sourceforge.jtds.jdbc.SharedSocket.<init>(SharedSocket.java:261)
 at net.sourceforge.jtds.jdbc.ConnectionJDBC2.<init>(ConnectionJDBC2.java:318)
 ... 7 more
BUILD SUCCESSFUL (total time: 7 seconds)
asked Aug 22, 2012 at 11:40
14
  • It is a good idea to create an application user in SQL express and use that in your JDBC URL instead of using domain account credentials. BTW, you could post the code snippet you are using to establish a connection for further help from the community. Commented Aug 22, 2012 at 11:46
  • "I've tried searching found certain things, but it gives errors.". What errors ? can you Provide code/ efforts ? Commented Aug 22, 2012 at 11:53
  • 1
    Enable mixed mode. hosting.com/support/windows-vps/mixedmode Commented Aug 22, 2012 at 11:55
  • @Vikdor Then all clients/users will use the same username and password combination and all with the same privileges? Commented Aug 22, 2012 at 14:05
  • Oh! I thought authorization controls at the SQL server layer are disconnected from the domain credentials. If they are connected, probably your approach is the apt one. Commented Aug 22, 2012 at 14:20

4 Answers 4

6

Though many answers are available. I would like to give one.

As you are using Window Authentication.

Change JTDS URL from

private String URL = "jdbc:jtds:sqlserver://localhost/brandix;instance=sqlexpress";

TO

private String URL = "jdbc:jtds:sqlserver://localhost/brandix;instance=sqlexpress;useNTLMv2=tru‌​e;domain=workgroup";

OR Another alternative is:

Download the [Microsoft JDBC driver][1]rather than JTDS. Add sqljdbc4.jar to your classpath.

Update your connection string (JDBC URL) for you server

Use Class.forName(com.microsoft.sqlserver.jdbc.SQLServerDriver);

URL : jdbc:sqlserver://localhost;user=root;password=‌​123;"

If still problem, persist. Check with,

Enable TCP/IP network protocol, which is disable by default, and set the TCP/IP port to 1433 which is again default port no.

  1. Open the SQL Server Configuration Manager Start -> Microsoft SQL Server 2008 -> Configuration Tools -> SQL Server Configuration Manager

  2. Then at the left hand tree. Select SQL Server 2005 Network Configuration-> Protocol for SQLEXPRESS-> TCP/IP.

  3. Right click and enable it.

  4. A window box appear on double click the TCP/IP. Click on the "IP Addresses" Tab

  5. Set the TCP Port value to 1433 then click apply

  6. Restart SQL Service.

answered Aug 24, 2012 at 4:43
Sign up to request clarification or add additional context in comments.

8 Comments

Thanks, i've done as you said. How should i replace Class.forName("net.sourceforge.jtds.jdbc.Driver"); statement?
Ah, i've followed your previous answer..anyway, i'll try this too
I've done according to your edited answer. It throws this now: java.sql.SQLException: The USENTLMV2 connection property is invalid.. Do you want the full stacktrace? It's too long to put here, i'll add it to my post, if needed..
@Anubis: Remove the property. I have not deep idea on SQL server with Windows Authentication. If You can resintall to use it SQL server.
Finally, i managed to make it run. You helped me a lot with this. Many thanks, really appreciate your help !!! I used Ms JDBC. I had to use Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); and CON=DriverManager.getConnection("jdbc:sqlserver://localhost;user=root;password=123;");.. Hope it'll help someone.. (I found that these differ from the ones you should use with SQL server 2000)
|
2

You will have to use a driver capable to talk to SQL Server, like jTDS for example.

Now for that case, your code would have to look similar to this:

public ResultSet getData(String query) {
 Connection CON = null;
 try {
 Class.forName("net.sourceforge.jtds.jdbc.Driver");
 CON = DriverManager.getConnection(URL, USER, PASS);
 RESULT = CON.createStatement().executeQuery(query);
 } catch (Exception ex) {
 System.err.println(">> " + ex.getMessage());
 }
 return RESULT;
}
private String URL = "jdbc:jtds:sqlserver://servername/brandix;instance=sqlexpress";
private String USER = "";
private String PASS = "";
private static ResultSet RESULT;

Also see @SPee's answer.

answered Aug 22, 2012 at 12:16

2 Comments

Thanks, I followed your instructions. Downloaded JTDS and added jtds-1.2.6.JAR to my project libraries (I'm using Netbeans). (But I haven't done anything else, like copying a dll or some other file to \windows\* directory...) Then I've tried using the above code. Well, at the first time, firewall appeared asking permission for access and I allowed it. Now every time I try to connect, it waits for couple of seconds and throws this exception,Network error IOException: Connection refused: connect. Any help?
See the FAQ for jTDS for this: jtds.sourceforge.net/faq.html#connectionRefused. Also follow the instructions in this Microsoft article if needed: support.microsoft.com/default.aspx?scid=kb;EN-US;914277.
1

Basically your steps are:

  1. Install the Microsoft JDBC driver of SQL Express into your classpath
  2. Update your connection string (JDBC URL) for you server
  3. Update your queries for SQL Express

Step 2 might require you to make SQL Express available through a TCP/IP connection, instead of a local pipe/named instance.

Step 3 might be the hardest. You must check your queries to be valid for SQL Express, use functions that exist with the correct parameters. And for a Database Management System you must also update all your queries for user management, table management, etc.

answered Aug 22, 2012 at 12:11

Comments

1

First download the JDBC-Driver from the microsoft website.

http://msdn.microsoft.com/en-us/sqlserver/aa937724.aspx

Add sqljdbc4.jar to your application.

Copy the sqljdbc_auth.dll in c:\windows\system32 and the /bin folder of your java installation.

You can use a connection url like this:

jdbc:sqlserver://D-PC-IT-02;instanceName=LOKALEINSTANZ;databaseName=MyDatabase;integratedSecurity=true;

A reboot may be necessary.

answered Aug 22, 2012 at 13:47

Comments

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.