SQL Server Express ist evil.

Authentication Mode (SQL Server Express):

If you select Windows Authentication, Setup creates an sa account that is disabled by default. To use Mixed Mode Authentication and activate the sa account after Setup completes, see the How to: Change Server Authentication Mode and ALTER LOGIN (Transact-SQL) topics in SQL Server 2005 Books Online.


But wait, there's more.
http://msdn2.microsoft.com/en-us/library/ms188670.aspx

If Windows Authentication mode is selected during installation, the sa login is disabled. If you later change authentication mode to SQL Server and Windows Authentication mode, the sa login remains disabled. To enable the sa login, use the ALTER LOGIN statement.
...
To enable the sa login by using Transact-SQL

1. Execute the following statements to enable the sa password and assign a password.

ALTER LOGIN sa ENABLE ;
GO
ALTER LOGIN sa WITH PASSWORD = '<password>' ;
GO


This is probably the best sum of what's got to go on to get JDBC working with SQL Server Express Edition 2005 when you use a named instance (say SQLEXPRESS) rather than a default on port 1433.

Sql Express is going to be a good match for the Sql Server 2005 JDBC provider, it is free, it is relatively easy to set up and deploy and it has all the power of Sql Server 2005 for low end applications.

There are a few things to keep in mind when using Sql Express with the JDBC driver:

· You need to enable mixed mode security when you install the server so that you can connect using user name and password.
· Make sure that the sqlbrowser is enabled. Sql Server configuration Manager ->SQL Server 2005 Services ->SQL Server Browser ->Start
· The JDBC provider only works with the TCP/IP protocol which is disabled by default on Sql Express. You need to enable the TCP/IP Protocol from the SQL Server Configuration Manager that ships with SqlExpress and re-start the server. Look under SQL Server 2005 Network Configuration -> Protocols for SQLEXPRESS-> TCP/IP->Enable.


Note from the code he gives that your connection string for, say, SQuirreL SQL, would be...

jdbc:sqlserver://localhost\SQLEXPRESS

You won't need a port because the SQL Browser will provide it for you, I think. It works, anyhow.

Labels: , ,