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.
- Expand SQL Server 2005 Network Configuration node.
- In the right pane, select Protocols for SQLEXPRESS. The right pane should now show Protocols and Status columns.
- 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 …
- Select Properties from the TCP/IP context menu. The TCP/IP Properties dialog should open.
- Select the IP Addresses tab.
- In the IPAll node …
- 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.
- Set the desired port number in the TCP Port field.
- 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.
- Start Microsoft SQL Server Management Studio Express (SSMSE) and connect to your database server.
- In Object Explorer pane, select Properties from your database's context menu. The Server Properties dialog should open.
- Select Security page.
- Select SQL Server and Windows Authentication Mode check box.
- Press OK button to close the dialog.
- In Object Explorer pane, expand Security / Logins node.
- Select existing user sa. Note that there is a red downward arrow next to that user's image.
- View sa's properties. The Login Properties dialog should open.
- Select Status page.
- Ensure that the Login: Enabled radio button is selected.
- Select General page.
- Enter a password for this user.
- Press OK button to close the dialog.
- 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.
