Java JDBC Intellij and SQLServer Experss-CodePudding

For some reason my SQL Server database can not connect to my Intellij Java app. this is the first time I am doing this, so I am not understanding what am I doing wrong.

sql server sc

The code that I am using to connect the database is this.

import java.sql.* ;

public class StudentDatabase {

    static Connection con ;
    static String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; //com.microsoft.sqlserver.jdbc.SQLServerDriver
    static String url = "jdbc:sqlserver://frensis\\sqlexpress;databaseName=StudentDatabase";
    static String username = "Frensis\\Grid_Computers";
    static String userpass = "";
    
    public static Connection getConnection() {
        try {
            if (con == null) {
                Class.forName(driver);
                con = DriverManager.getConnection(url, username, userpass);
    
            }
        } catch (ClassNotFoundException ex)
        {
            ex.printStackTrace();
        }
        catch (SQLException ex)
        {
            ex.printStackTrace();
            System.out.println("Error Connecting to Database");
        }
        return con;
    }
}

And the error that I get is this.

"C:\Program Files\Java\jdk-17.0.2\bin\java.exe" "-javaagent:C:\Program Files\JetBrains\IntelliJ IDEA Community Edition 2021.2.3\lib\idea_rt.jar=55405:C:\Program Files\JetBrains\IntelliJ IDEA Community Edition 2021.2.3\bin" -Dfile.encoding=UTF-8 -classpath C:\Users\Grid_Computers\IdeaProjects\BilancStudentApp\out\production\BilancStudentApp;C:\Users\Grid_Computers\OneDrive\Desktop\sqljdbc_10.2\enu\mssql-jdbc-10.2.0.jre8.jar;C:\Users\Grid_Computers\OneDrive\Desktop\sqljdbc_10.2\enu\mssql-jdbc-10.2.0.jre11.jar;C:\Users\Grid_Computers\OneDrive\Desktop\sqljdbc_10.2\enu\mssql-jdbc-10.2.0.jre17.jar StudentApp`
`com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host frensis, port 1433 has failed. Error: "Connect timed out. 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:237)`
`at com.microsoft.sqlserver.jdbc.SQLServerException.ConvertConnectExceptionToSQLServerException(SQLServerException.java:288)`
`at com.microsoft.sqlserver.jdbc.SocketFinder.findSocket(IOBuffer.java:2720)`
`at com.microsoft.sqlserver.jdbc.TDSChannel.open(IOBuffer.java:761)`
`at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:3180)`
`at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:2833)`
`at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:2671)`
`at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1640)`
`at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:936)`
`at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:681)`
`at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:229)`
`at StudentDatabase.getConnection(StudentDatabase.java:15)`
`at StudentApp.main(StudentApp.java:7)`
`Error Connecting to Database`

`Process finished with exit code 0

I also added to my external library 3 JDBC.

enter image description here

Can you please guide me on how to fix this problem or maybe how to change my code so I can successfully connect it?

After a few changes, I get a new error related to the SSL:

    "C:\Program Files\Java\jdk-17.0.2\bin\java.exe" "-javaagent:C:\Program Files\JetBrains\IntelliJ IDEA Community Edition 2021.2.3\lib\idea_rt.jar=52938:C:\Program Files\JetBrains\IntelliJ IDEA Community Edition 2021.2.3\bin" -Dfile.encoding=UTF-8 -classpath C:\Users\Grid_Computers\IdeaProjects\BilancStudentApp\out\production\BilancStudentApp;C:\Users\Grid_Computers\OneDrive\Desktop\sqljdbc_10.2\enu\mssql-jdbc-10.2.0.jre8.jar;C:\Users\Grid_Computers\OneDrive\Desktop\sqljdbc_10.2\enu\mssql-jdbc-10.2.0.jre11.jar;C:\Users\Grid_Computers\OneDrive\Desktop\sqljdbc_10.2\enu\mssql-jdbc-10.2.0.jre17.jar StudentApp
    com.microsoft.sqlserver.jdbc.SQLServerException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target". ClientConnectionId:58113aa8-cf71-4e9c-8d63-8820be9c1ab8
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:3680)
        at com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:2113)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:3204)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:2833)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:2671)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1640)
        at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:936)
        at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:681)
        at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:229)
        at StudentDatabase.getConnection(StudentDatabase.java:15)
        at StudentApp.main(StudentApp.java:7)
    Caused by: javax.net.ssl.SSLHandshakeException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
        at java.base/sun.security.ssl.Alert.createSSLException(Alert.java:131)
        at java.base/sun.security.ssl.TransportContext.fatal(TransportContext.java:371)
        at java.base/sun.security.ssl.TransportContext.fatal(TransportContext.java:314)
        at java.base/sun.security.ssl.TransportContext.fatal(TransportContext.java:309)
        at java.base/sun.security.ssl.CertificateMessage$T12CertificateConsumer.checkServerCerts(CertificateMessage.java:654)
        at java.base/sun.security.ssl.CertificateMessage$T12CertificateConsumer.onCertificate(CertificateMessage.java:473)
        at java.base/sun.security.ssl.CertificateMessage$T12CertificateConsumer.consume(CertificateMessage.java:369)
        at java.base/sun.security.ssl.SSLHandshake.consume(SSLHandshake.java:396)
        at java.base/sun.security.ssl.HandshakeContext.dispatch(HandshakeContext.java:480)
        at java.base/sun.security.ssl.HandshakeContext.dispatch(HandshakeContext.java:458)
        at java.base/sun.security.ssl.TransportContext.dispatch(TransportContext.java:201)
        at java.base/sun.security.ssl.SSLTransport.decode(SSLTransport.java:172)
        at java.base/sun.security.ssl.SSLSocketImpl.decode(SSLSocketImpl.java:1500)
        at java.base/sun.security.ssl.SSLSocketImpl.readHandshakeRecord(SSLSocketImpl.java:1415)
        at java.base/sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:450)
        at java.base/sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:421)
        at com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:2021)
        ... 9 more
    Caused by: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
        at java.base/sun.security.validator.PKIXValidator.doBuild(PKIXValidator.java:439)
        at java.base/sun.security.validator.PKIXValidator.engineValidate(PKIXValidator.java:306)
        at java.base/sun.security.validator.Validator.validate(Validator.java:264)
        at java.base/sun.security.ssl.X509TrustManagerImpl.checkTrusted(X509TrustManagerImpl.java:242)
        at java.base/sun.security.ssl.X509TrustManagerImpl.checkServerTrusted(X509TrustManagerImpl.java:113)
        at com.microsoft.sqlserver.jdbc.TDSChannel$HostNameOverrideX509TrustManager.checkServerTrusted(IOBuffer.java:1702)
        at java.base/sun.security.ssl.AbstractTrustManagerWrapper.checkServerTrusted(SSLContextImpl.java:1441)
        at java.base/sun.security.ssl.CertificateMessage$T12CertificateConsumer.checkServerCerts(CertificateMessage.java:638)
        ... 21 more
    Caused by: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
        at java.base/sun.security.provider.certpath.SunCertPathBuilder.build(SunCertPathBuilder.java:141)
        at java.base/sun.security.provider.certpath.SunCertPathBuilder.engineBuild(SunCertPathBuilder.java:126)
        at java.base/java.security.cert.CertPathBuilder.build(CertPathBuilder.java:297)
        at java.base/sun.security.validator.PKIXValidator.doBuild(PKIXValidator.java:434)
        ... 28 more
    Error Connecting to Database
    
    Process finished with exit code 0

CodePudding user response：

Double check the JDBC url and if it is ok make sure that the MS SQL Server allows JDBC connections in the Configuration Manager, in particular:

TCP/IP protocol enabled
IPs / ports enabled (including localhost / 127.0.0.1 if you are running your Java application and SQL Server in the same machine for development)

You can give a look to this guide with steps and screenshots.

CodePudding user response：

If you are running behind a corporate firewall then you might need to add the cert-file of the firewall to java trusted key-store.

Let's say we are running behind the Zscaler, below is the command to import cert files to java key-store

keytool -import -trustcacerts -alias <certAlias> -file <certFile> -keystore <trustStoreFile>

Example (For MAC machines):

keytool -import -trustcacerts -alias zscalerrootca -file tescoroot.der -keystore /Library/Java/JavaVirtualMachines/openjdk-8.jdk/Contents/Home/jre/lib/security/cacerts