JDBC Connection to MS SQL Server Issues ( Port, connection failed, Port 1433, 1434, etc)

Resources:
http://blogs.msdn.com/b/jdbcteam/

Download Microsoft JDBC Driver 4.0 for SQL Server 

http://msdn.microsoft.com/en-us/sqlserver/aa937724.aspx 

  * You will find 2 .jar files in the download!

Difference between sqljdbc.jar and sqljdbc4.jar. (Find more at http://msdn.microsoft.com/en-us/library/ms378422.aspx)

JAR	Description
sqljdbc.jar	sqljdbc.jar   support for JDBC 3.0., Java Runtime Environment (JRE) of version 5.0.
sqljdbc4.jar	support for JDBC 4.0 API. , Java Runtime Environment (JRE) of version 6.0 or 7.0. Using sqljdbc4.jar on JRE 5.0 will throw an exception.

 Connection URL:  (REF: http://msdn.microsoft.com/en-us/library/ms378428.aspx )

jdbc:sqlserver://localhost:1433;databaseName=AdventureWorks;integratedSecurity=true; (OR)

jdbc:sqlserver://localhost:1433;databaseName=AdventureWorks;user=uuu; password=pppp;

 ** The above reference link provides connection URLs for different types for sql server connections like Remote , localhost, integrated windows security, using port , etc. But missing one thing about 'SQLEXPRESS '.

JDBC Connection to SQLEXPRESS : ( Follow the picture below) Finding a quick Port number.

1. SQL SERVER  Configuration Manager - > Under 'SQL Server Network Configuration' - > Click 'Protocols for SQLEXPRESS' ( Or what ever your SQLEXPRESS server name is)
2. On RHS If TCP/IP is 'Disabled' - > Enable it.
3. Double click on TCP/IP -> TCP/IP properties window opens
4. Click 'IP Address tab' - Scroll all the way down.
5. Under IPALL -> find TCP Dynamic Ports:  Copy the port no.
6. Use this number in the connection URL - test your connection. 

 

Errors & FIX:
  
Error:
cannot establish  a connection to PPPP-PC\SQLEXPRESS
jre version 1.7 is not supported by this driver import com.microsoft.sqlserver.jdbc.*;

FIX: 

Include the right .jar file and remove the other depending on the JRE you are using. (See the beginning of this article)  . You are referencing to sqljdbc.jar instead of sqljdbc4.jar.

Error:

java.lang.UnsupportedOperationException: Java Runtime Environment (JRE) version 1.6 is not supported by this driver. Use the sqljdbc4.jar class library, which provides support for JDBC 4.0.

FIX:

 You are referencing to sqljdbc.jar instead of sqljdbc4.jar.

Include the right .jar file and remove the other depending on the JRE you are using. (See the beginning of this article)  .

Error:

Unable to add connection.Cannot establish connection to jdbc:sqlserver:\\PPPP-PC\SQLEXPRESS;databasename=assign;using com.microsoft.sqlserverdriver(The connection to the host-PPPPP-PC\SQLEXPRESS instance name sqlexpress failed.Error:"java.net.sockettimeoutexception:Peektimeout".Verify the server and the instance name and check that no firewall is blocking UDP traffic to port 1434. For SQL Server 2005 or later,verify the SQL Server Browser is not running on hos

FIX:

Set the right port in the JDBC connection URL. Refer the image above for JDBC connection to SQLEXPRESS or Refer the link provided for other servers.

Error:

Login failed for user sa:... Connection ID: XKLJF0980SF

FIX: 

You need to have a Login name in the main SQLEXPRESS / SQL SERVER -> Security section. Or create another login.

If you forgot the passoword for SA on your desktop (We all do..) , you can reset by going to 'Properties' window. But this might mess your all other applications that are using SA.( But you might be able to find the pwd. in those application's connection strings also.)

---------

Pl. add more errors and FIXes below . Also feel free to copy this content and post it any where you want it might save hours for the beginners.

Thanks.