SQL Server jdbc connection

Question

I am running this code snippet on localhost in windows authentication but getting following error but I have alreadily added sqljdbc4 jar in my class path and while running from eclipse also I have added jar in build path

import java.io.*;
import java.sql.*;
import java.util.GregorianCalendar;
import java.util.Date;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;

class  Cms_truncate
{
    public static void main(String[] args) 
    {
         Calendar cal = new GregorianCalendar();

         //String name="cmscim";
                 Connection conn = null;

         String url = "jdbc:sqlserver://localhost\SQLEXPRESS;databasename=yatin";
         String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
         String userName = ""; 
         String password = "";
         Statement stmt;
         try
         {

         Class.forName(driver);//.newInstance();
         conn = DriverManager.getConnection(url,userName,password);
         String query = "truncate table cim";
         stmt = conn.createStatement();
         int flag = stmt.executeUpdate(query);
         System.out.println("flag = "+flag); 
         conn.close();
        System.out.println("");
         } catch (Exception e) {
         e.printStackTrace();
         }

    }
}

The Error:

com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host localhost, port 1433 has failed. Error: "Connection refused: connect. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
    at com.microsoft.sqlserver.jdbc.SQLServerException.ConvertConnectExceptionToSQLServerException(SQLServerException.java:241)
    at com.microsoft.sqlserver.jdbc.SocketFinder.findSocket(IOBuffer.java:2243)
    at com.microsoft.sqlserver.jdbc.TDSChannel.open(IOBuffer.java:491)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1309)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:991)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:827)
    at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1012)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at Cms_truncate.main(Cms_truncate.java:28)

help me please.

Answer 1

This error does not say "authentication error", it says "connection error" due to a "connection refused". This means you need to specify the correct port number. You'll need to review your SQL Server configuration and update your connection string.

according to MSDN docs, the connection string should look like this:

jdbc:sqlserver://localhost:<insert_proper_port_here>\SQLEXPRESS;databasename=yatin

You are not supplying a username or password, which you might need to do once you get the port number figured out. See the referenced documentation for further details.

Answer 2

First of all whenever you are using SQL instance name in URL you have to specify //[serverName[\\instanceName][:portNumber]]databaseName

for E.g: jdbc:sqlserver://localhost\\SQLEXPRESS:1433;databasename=yatin

Once this is done next step:-

1. Go to all Program- > search for SQL Server Configuration Manager.Open it
2. Expand SQL server network configuration in left pane
3. Click on protocols for instance name in most cases it would be protocols for SQLEXPRESS
4. Click on TCP IP. TCP IP properties window will get opened
5. Select enabled property and listen all property as "YES"
6. Navigate to IP address tab and check if ip all section TC Dynamic port is 1433 if not then set it

7. Then Apply and ok

8. Now go to SQL server services section under SQL Server Configuration Manager in left pane. click on it

9. You will see database engine is running one with instance name in most of the case SQLEXPRESS,right click on it and stop it and then start it again.

Now rerun the code