Connecting to SQL Server 2008 Express

[Matt]

It is possible to use Microsofts free JDBC Driver for SQL Server to connect
to SQL Server 2008 Express?

Thanks.

Matt

[Matt]

Correction:

IS IT possible to use ....... ?

Thanks.

Matt

[Mugunthan Mugundan]

Yes, you can use the jdbc driver to connect to 2008 express.

[Matt]

Thanks.
Can you point to any documentation that tells how.
Specifically how to ensure that Express is bound and listening on the port
that I think it is. I currently have a simple test.java program that uses the
free driver to connect to SQL Server and it works, but it won't connect to
SQL Server Express.
I've configured SQL Server Express to use port 1533 and specified that port
in my test program. The Windows firewall is turned off so no issue there. And
yet I get a connect failure.

Thanks for any insight you can provide.

Matt

[Adam Saxton [MSFT]]

This isn't really specific to the JDBC Driver as it is a general
connectivity issue. The first thing to ask, is what is the error you are
getting? If I recall correctly, SQL Express by default only uses Windows
Authentication and not SQL Auth. The JDBC Driver will use SQL Auth by
default unless you have configured our Integrated Authentication component
that goes with the driver.

If you are really getting a TCP connectivity type error, there are several
things you can do to validate that you can connect to your instance Via
TCP. By Default, your instance may not have TCP enabled from a protocol
perspective. It may just have Named Pipes enabled or only Shared Memory.
You can verify this through the SQL Server Configuration manager. Within
that, go to SQL Server Network Configuration, and go to the "Protocols for
<instance name>" that matches the SQL Express instance. In there verify
that TCP/IP is enabled. You can also check the SQL ERRORLOG for the SQL
Express instance and verify that it is indeed listening on TCP and that
port.

This is an example from my SQL 2008 server

2009-10-01 10:53:37.77 Server Server is listening on [ 'any' <ipv6>
1433].
2009-10-01 10:53:37.77 Server Server is listening on [ 'any' <ipv4>
1433].
2009-10-01 10:53:37.77 Server Server local connection provider is
ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
2009-10-01 10:53:37.77 Server Server local connection provider is
ready to accept connection on [ \\.\pipe\sql\query ].
2009-10-01 10:53:37.77 Server Server is listening on [ ::1 <ipv6>
1434].
2009-10-01 10:53:37.77 Server Server is listening on [ 127.0.0.1
<ipv4> 1434].

From this, i can determine that i'm listing on TCP 1433, Named Pipes and
using my Dedicated Admin port on TCP 1434.

After that, I'd start looking at the network itself. I know you mentioned
you disabled your firewall, so that's a good start. You can use what we
call a UDL file to test connectivity outside of your Java Code. To use a
UDL, do the following:

1. Create a new text document on your desktop
2. Rename it to "test.udl"
3. Double click on it and it will open a Data Link properties window
4. On the Provider tab, Choose the "Microsoft OleDb Provider for SQL
Server" and then click on Next
5. For the Server, put in the Server,Port syntax or the Named Instance
name.
6. Set the authentication to the username and password you are using in
your Java code that maps to a SQL Auth login (NOTE: This will not be a
Windows Account)
7. Click on Test Connection

That should get you started.

Thanks,
Adam