SQL Server and JDBC

This describes how I set up JDBC to interact with a Sql Server Express 2005 database. Sql Server Express 2005 was installed on a Windows XP machine. The database was being accessed using JDBC on a remote Trusted Solaris machine running Java 1.2. The following instructions should apply to more configurations than just that though.


- Installing Sql Server Express 2005 -

1. Install Sql Server Express.

Download it from http://www.microsoft.com/sql/downloads/trial-software.mspx#EXC. During install, selecting all the default options is fine. The only thing you would want to change is using Mixed Mode authentication instead of Windows Authentication. If you didn't select that option, I'll tell you how to do it later, so don't worry about it.


2. Allow TCP/IP Connectivity

Configure TCP/IP connectivity for use with IBM Director Server. By default, SQL Server Express Edition does not have TCP/IP connectivity enabled after installation. This means you won't be able to remotely access the database. In addition, the TCP port must be set for use with IBM Director Server. Complete the following steps:

1. Click Start > All Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Configuration Manager.
2. In the left pane of the SQL Server Configuration Manager window, expand SQL Server 2005 Network Configuration and then click Protocols for SQLEXPRESS.
3. In the right pane of the SQL Server Configuration Manager window, right-click TCP/IP and click Enable. Click OK to acknowledge the warning message.
4. Right-click TCP/IP again and click Properties.
5. In the TCP/IP Properties window, click the IP Addresses tab, and then expand IPALL.
6. Under IPALL, clear the TCP Dynamic Ports field and then type a port number in the TCP Port field. Usually, the port number should be set to 1433.
7. In the TCP/IP Properties window, click OK, and then click OK to acknowledge the warning message.
8. In the left pane of the SQL Server Configuration Manager window, click SQL Server 2005 Services.
9. In the right pane of the SQL Server Configuration Manager window, right-click SQL Server (SQLEXPRESS) and click Restart.


3. Install SQL Server Management Studio

Download SQL Server Management Studio Express from https://www.microsoft.com/downloads/details.aspx?familyid=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en. If you didn't allow Mixed mode Authentication for the Sql Server Express earlier, complete the following steps:

1. In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.
2. On the Security page, under Server authentication, select "SQL Server and Windows Authentication mode."
3. In the SQL Server Management Studio dialog box, click OK, to acknowledge the need to restart SQL Server.
4. In Object Explorer, right-click your server, and then click Restart. If SQL Server Agent is running, it must also be restarted.
5. In Object Explorer, expand Security, expand Logins, right-click sa, and then click Properties.
6. On the General page, you may have to create and confirm a password for the sa login.
7. On the Status page, in the Login section, click Enabled, and then click OK.


4. Create Your Database

- Create the database using SQL Server Management Studio. Add a user to the database by going into [database_name]/security/users.
- Give the user permissions for remote access.
a) Right Click [database_name], select properties.
b) Go into properties.
c) Select the user and grant all permissions.


- JDBC Access -

Microsoft provides a JDBC Driver for SQL Server 2005, which can be downloaded from http://msdn2.microsoft.com/en-us/data/aa937724.aspx. There's a "sqljdbc.jar" in the pack that you'll need to put on your classpath. Then you can use the following Driver and Url:

Driver: com.microsoft.sqlserver.jdbc.SQLServerDriver
Url: jdbc:sqlserver://[host]:[port, default 1433];"databaseName=[database_name]"

However, I needed to use Java 1.2 for my application and Microsoft's JDBC driver does not support it. It doesn't support 1.3 either. I found another great free driver at http://sourceforge.net/project/shownotes.php?release_id=369359&group_id=33291 which fit my needs: free, supports java 1.2, and easy to use. There's a jtds.jar in the pack that you'll need to put on your classpath. Then you can use the following Driver and Url.

Driver: net.sourceforge.jtds.jdbc.Driver
Url: jdbc:jtds:sqlserver://[host]:[port, default 1433]/[database_name]

I'm assuming you know how to use JDBC already, so this guide ends here.