2007-04-29

JDBC to SQL Server Express

Connecting JDBC-based tools such as SQL Developer or DbVisualizer to SQL Server Express required the following steps:

  • Obtain JDBC Driver
  • TCP/IP for SQL Server Express
  • Authentication Method

Obtain JDBC Driver

Using SQL Developer, when you get the following exception …

Unable to find driver: net.sourceforge.jtds.jdbc.Driver

… download the jTDS JDBC driver and install it in your JRE's ext folder. The latest version of the driver is 1.2. Of course, there are other JDBC drivers for SQL Server you can use.

TCP/IP for SQL Server Express

By default, TCP/IP for SQL Server Express is disabled, so JDBC cannot connect to it and you may get the following exception …

Network error IOException: Connection refused: connect

Enable TCP/IP

To enable TCP/IP, start SQL Server Configuration Manager.

  1. Expand SQL Server 2005 Network Configuration node.
  2. In the right pane, select Protocols for SQLEXPRESS. The right pane should now show Protocols and Status columns.
  3. Select Enable from the TCP/IP context menu.

Find or Configure TCP/IP Port

After enabling TCP/IP, you have to find out which port number to use. SQL Server Express allocates a port dynamically each time it is started, so to find or configure the port number, continue using SQL Server Configuration Manager …

  1. Select Properties from the TCP/IP context menu. The TCP/IP Properties dialog should open.
  2. Select the IP Addresses tab.
  3. In the IPAll node …
    1. The TCP Dynamic Ports field shows the currently used port number. If you set that field to blank, then SQL Server Express should not automatically choose another port when it restarts.
    2. Set the desired port number in the TCP Port field.
    3. Press OK to apply your settings and close the dialog.

Test TCP/IP

If you change the TCP/IP port, you have to restart SQL Server Express before it can use the new port number. To test that your port number is used, start a cmd window and type: netstat -an. For instance, if you used port 1433, you should see this line in the list of ports used: 

TCP    0.0.0.0:1433           0.0.0.0:0              LISTENING

Authentication Method

By default, SQL Server Express uses Windows Authentication Mode to authenticate connections. If you get this exception …

Login failed for user '<User name>'. The user is not associated with a trusted SQL Server connection.

… then you may have to enable SQL Server Authentication Mode and create or enable a user.

  1. Start Microsoft SQL Server Management Studio Express (SSMSE) and connect to your database server.
  2. In Object Explorer pane, select Properties from your database's context menu. The Server Properties dialog should open.
  3. Select Security page.
  4. Select SQL Server and Windows Authentication Mode check box.
  5. Press OK button to close the dialog.
  6. In Object Explorer pane, expand Security / Logins node.
  7. Select existing user sa. Note that there is a red downward arrow next to that user's image.
  8. View sa's properties. The Login Properties dialog should open.
  9. Select Status page.
  10. Ensure that the Login: Enabled radio button is selected.
  11. Select General page.
  12. Enter a password for this user.
  13. Press OK button to close the dialog.
  14. If you refresh the Object Explorer pane, note that user sa no longer has a red downward arrow.

Finally …

After all these steps, you should be able to connect to your SQL Server Express database using JDBC.

Posted by at
Labels: ,

31 comments:

  1. Anonymous22 October 2007 at 10:07

    Straightforward and effective advice. Thanks!

    ReplyDelete
  2. Anonymous16 January 2008 at 18:49

    Simple in all aspects... effective to the highest level!!!

    ReplyDelete
  3. Anonymous18 March 2008 at 15:21

    i can able to connect to sql server....its really wonderfull tutorial...

    ReplyDelete
  4. Anonymous6 May 2008 at 01:17

    Good Job. Well done!

    ReplyDelete
  5. Anonymous29 August 2008 at 11:48

    Very effective!.

    ReplyDelete
  6. Anonymous19 March 2009 at 11:30

    It works well for SQL Server 2008 too. Very helpful. Thank you very much.

    ReplyDelete
  7. Anonymous24 March 2009 at 19:29

    It helps me a lot. Thank you very much!

    ReplyDelete
  8. Anonymous14 May 2009 at 01:32

    Thankyou so much for this. It's always so nice to come across something that saves you heaps of time.

    ReplyDelete
  9. Anonymous5 August 2009 at 23:49

    it realy helped me alot. thanks.

    ReplyDelete
  10. Anonymous12 August 2009 at 16:16

    Good Job. Well done!
    It works well for SQL Server 2008 too. Very helpful. Thank you very much.

    ReplyDelete
  11. Unknown30 September 2009 at 19:01

    Great walkthrough, very helpful.

    ReplyDelete
  12. Anonymous11 October 2009 at 08:54

    Thank you very much.
    You can't guess how long I've been looking for a solution to this problem.

    ReplyDelete
  13. Anonymous27 October 2009 at 20:49

    thanks very much, it's very usefull for me :D

    ReplyDelete
  14. Anonymous16 April 2010 at 07:01

    thankyou very much.

    ReplyDelete
  15. Samuel19 April 2010 at 21:45

    Thanks a lot, It worked with SQL Server 2008...I was lost this morning, you saved my afternoon !

    ReplyDelete
  16. icuz21 May 2010 at 04:13

    Very usefull! Thanks a lot..

    ReplyDelete
  17. Anonymous14 July 2010 at 12:41

    Looked for a while for something like this, thanks!

    ReplyDelete
  18. Anonymous4 August 2010 at 07:14

    Worked like a charm. Thanks!

    ReplyDelete
  19. Abhishek Dadhich29 August 2010 at 22:50

    Thanks a lot!!!! I wasted so many hours to fix an issue with connectivity and when I was just giving up found this link.... The steps you mentioned worked like a charm!!! Thanks again!!!!

    ReplyDelete
  20. Anonymous22 September 2010 at 12:33

    Thanks for your help, great!

    ReplyDelete
  21. Anonymous10 October 2010 at 14:39

    Dear God, this was the only thing on the net that helped after several days of looking. Had no idea that I needed to configure the tcp/ip port. Thank you.

    ReplyDelete
  22. Darshan Modani1 February 2011 at 16:43

    Thanks man.....Appreciate your post...

    ReplyDelete
  23. Anonymous8 May 2011 at 01:47

    Thank you so much, I am your fan now :)

    ReplyDelete
  24. Rox17 June 2011 at 19:49

    Thanks.....the only one tutorial for configuring sql server connection.
    GREAT JOB !!!!

    ReplyDelete
  25. Anonymous26 July 2011 at 17:02

    This is great! I wish I found this article yesterday - would save me a day of fruitless work!

    ReplyDelete
  26. Anonymous19 October 2011 at 00:49

    thank you, ms should be burning in hell for making it this complicated.

    ReplyDelete
  27. Anonymous28 February 2012 at 03:50

    I didn't know about the IPALL node. Nice explanation. Thank You.

    ReplyDelete
  28. Paulino G Reis11 May 2012 at 03:07

    after days searching in the net, without sucess, finally i found this, thanks a lot

    ReplyDelete
  29. Tamas12 July 2012 at 22:46

    Thank you! It helped me solve the problem;)

    ReplyDelete
  30. Unknown11 January 2013 at 03:43

    Thank you, save my day...

    ReplyDelete
  31. Anonymous8 March 2013 at 02:39

    Kinda works... just tell me that the database I'm trying to connect to doesn't exist... now I see why people don't use Java

    ReplyDelete

Subscribe to: Post Comments (Atom)