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
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 IPA3 (127.0.0.1) 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 (1433).
- Press OK to apply your settings and close the dialog.
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
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.
After all these steps, you should be able to connect to your SQL Server Express database using JDBC.
The long version.
This entry is designed to help explain the reasons behind the steps in the short version of this post. Lets walk through the steps and the problems and try and explain whats happening
Checking to make sure that Express is running via SQLCMD
The default install for SQL Express installs Express as a named instance(called SQLExpress), with no network listening and with Windows Authentication support only.
To connect to a named instance of SQL Server the convention is to use a servername of the format <servername>\<instancename>, its also possible to shortcut the machinename to either “.” or “(local)”(these shortcuts work with all protocols, localhost will also work but only with TCP/IP in older clients,however SQL Native Access supports resolution of LocalHost for Named Pipes and Shared Memory).
No user id and password has been specified because of the default windows authentication, if the install had been changed to specify mixed mode authentication and a password had been given then this could have been used.
Because SQL Server Express does not listen on the network by default connections are made using a local protocol, in the case of the VS 2005 SKUs this is the shared memory protocol. The interface to this has changed in SQL Server 2005 such that older clients can no longer use it. Hence for older clients to work a different local protocol or a network protocol must be used, these are not enabled by default and must be manually enabled.
SQL Browser Service
In general when a client connects remotely to a SQL Server named instance the client does not know the port that the instance is listening on(you can work around this by configuring SQL Server named instances to listen on a specific port and then specifying that port in the connection string in the format <servername>,<port number>)
So when an application attempts to connect to a named instance in the form <servername>\<instancename> the client connection needs to be directed to the correct port. In SQL Server 2000 one of the running services had a built in listener that received the named instance connection requests and redirected them appropriately.
In SQL Server 2005 this functionality has been moved to a dedicated listener service, that service is SQLBrowser.
SQLBrowser also performs another service, that of SQL Server Discovery, its common in many UIs from Microsoft and others to have the ability to browse for instances of SQL Server running locally or remotely, again in SQL Server 2000 this was handled by one of the running instances of SQL Server, in SQL Server 2005 this is handled by the SQLBrowser Service.
Thus the SQLBrowser Service must be started to be able to discover any instance on the machine, and also to connect to named instances through protocols other than shared memory. If network protocols are not enabled via the setup switch then the browser service is not set to autostart.
SQL Native Client
SQL Server native connectivity is defined as connectivity through OLE DB(ADO uses OLE DB under the covers) or ODBC means in SQL Server 2005 (dblib is not an included as a data access technology in SQL Server 2005).
In previous releases of SQL Server(SQL7 and SQL2000) an update to MDAC was preferred for client apps, this means the installation of an entire MDAC update for SQL Server connectivity. In SQL Server 2005 we no longer require an MDAC update as we have refactored out the SQL Server specific connectivity components for OLE DB and ODBC.
As such in SQL Server 2005 no MDAC update is required to connect(although MDAC 2.8 is preferred, any MDAC from Windows 2000 SP3 upwards is supported), but a new component is required. This component is referred to as SQL Native Access.
These components include support for OLE DB and ODBC accessed through a single .dll called: SQLNCLI.dll, this file and its support files are redistributable.
SQL Native Client is NOT required for managed data access via .Net APIs.
Product Unit Manager
SQL Server Tools
Connection URL JDBC MICROSOFT: jdbc:sqlserver://localhost\SQLEXPRESS;databaseName=teste
Connection URL JDBC JTDS : jdbc:jtds:sqlserver://localhost:1433/TESTE;instance=SQLEXPRESS