Hot questions for Using GlassFish in sql server

Question:

I gave up! Tried everything and failed. Every time I try to deploy that .ear file (as in title of this post), I get that error:

Caused by: java.security.spec.InvalidKeySpecException: Unknown KeySpec type: java.security.spec.ECPublicKeySpec

I include a stack trace below:

[#|2015-08-11T14:47:30.185+0100|SEVERE|glassfish3.1.2|javax.enterprise.system.tools.admin.org.glassfish.deployment.admin|_ThreadID=23;_ThreadName=Thread-2;|Exception while preparing the app : Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Error in allocating a connection. Cause: Connection could not be allocated because: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "Server key". ClientConnectionId:a3f21831-fbc0-491c-9691-2b28500d7d2b
Error Code: 0
Local Exception Stack: 
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Error in allocating a connection. Cause: Connection could not be allocated because: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "Server key". ClientConnectionId:a3f21831-fbc0-491c-9691-2b28500d7d2b
Error Code: 0
    at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:309)
    at org.eclipse.persistence.sessions.JNDIConnector.connect(JNDIConnector.java:135)
    at org.eclipse.persistence.sessions.DatasourceLogin.connectToDatasource(DatasourceLogin.java:162)
    at org.eclipse.persistence.internal.databaseaccess.DatasourceAccessor.connectInternal(DatasourceAccessor.java:330)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.connectInternal(DatabaseAccessor.java:293)
    at org.eclipse.persistence.internal.databaseaccess.DatasourceAccessor.connect(DatasourceAccessor.java:418)
    at org.eclipse.persistence.sessions.server.ConnectionPool.buildConnection(ConnectionPool.java:216)
    at org.eclipse.persistence.sessions.server.ExternalConnectionPool.startUp(ExternalConnectionPool.java:146)
    at org.eclipse.persistence.sessions.server.ServerSession.connect(ServerSession.java:484)
    at org.eclipse.persistence.internal.sessions.DatabaseSessionImpl.login(DatabaseSessionImpl.java:633)
    at org.eclipse.persistence.internal.jpa.EntityManagerFactoryProvider.login(EntityManagerFactoryProvider.java:208)
    at org.eclipse.persistence.internal.jpa.EntityManagerSetupImpl.deploy(EntityManagerSetupImpl.java:488)
    at org.eclipse.persistence.internal.jpa.EntityManagerFactoryDelegate.getDatabaseSession(EntityManagerFactoryDelegate.java:188)
    at org.eclipse.persistence.internal.jpa.EntityManagerFactoryDelegate.createEntityManagerImpl(EntityManagerFactoryDelegate.java:277)
    at org.eclipse.persistence.internal.jpa.EntityManagerFactoryImpl.createEntityManagerImpl(EntityManagerFactoryImpl.java:294)
    at org.eclipse.persistence.internal.jpa.EntityManagerFactoryImpl.createEntityManager(EntityManagerFactoryImpl.java:272)
    at org.glassfish.persistence.jpa.PersistenceUnitLoader.loadPU(PersistenceUnitLoader.java:211)
    at org.glassfish.persistence.jpa.PersistenceUnitLoader.<init>(PersistenceUnitLoader.java:120)
    at org.glassfish.persistence.jpa.JPADeployer$1.visitPUD(JPADeployer.java:224)
    at org.glassfish.persistence.jpa.JPADeployer$PersistenceUnitDescriptorIterator.iteratePUDs(JPADeployer.java:495)
    at org.glassfish.persistence.jpa.JPADeployer.createEMFs(JPADeployer.java:233)
    at org.glassfish.persistence.jpa.JPADeployer.prepare(JPADeployer.java:168)
    at com.sun.enterprise.v3.server.ApplicationLifecycle.prepareModule(ApplicationLifecycle.java:871)
    at com.sun.enterprise.v3.server.ApplicationLifecycle.deploy(ApplicationLifecycle.java:410)
    at com.sun.enterprise.v3.server.ApplicationLifecycle.deploy(ApplicationLifecycle.java:240)
    at org.glassfish.deployment.admin.DeployCommand.execute(DeployCommand.java:389)
    at com.sun.enterprise.v3.admin.CommandRunnerImpl$1.execute(CommandRunnerImpl.java:348)
    at com.sun.enterprise.v3.admin.CommandRunnerImpl.doCommand(CommandRunnerImpl.java:363)
    at com.sun.enterprise.v3.admin.CommandRunnerImpl.doCommand(CommandRunnerImpl.java:1085)
    at com.sun.enterprise.v3.admin.CommandRunnerImpl.access$1200(CommandRunnerImpl.java:95)
    at com.sun.enterprise.v3.admin.CommandRunnerImpl$ExecutionContext.execute(CommandRunnerImpl.java:1291)
    at com.sun.enterprise.v3.admin.CommandRunnerImpl$ExecutionContext.execute(CommandRunnerImpl.java:1259)
    at org.glassfish.admin.rest.ResourceUtil.runCommand(ResourceUtil.java:214)
    at org.glassfish.admin.rest.ResourceUtil.runCommand(ResourceUtil.java:207)
    at org.glassfish.admin.rest.resources.TemplateListOfResource.createResource(TemplateListOfResource.java:148)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at com.sun.jersey.spi.container.JavaMethodInvokerFactory$1.invoke(JavaMethodInvokerFactory.java:60)
    at com.sun.jersey.server.impl.model.method.dispatch.AbstractResourceMethodDispatchProvider$ResponseOutInvoker._dispatch(AbstractResourceMethodDispatchProvider.java:205)
    at com.sun.jersey.server.impl.model.method.dispatch.ResourceJavaMethodDispatcher.dispatch(ResourceJavaMethodDispatcher.java:75)
    at com.sun.jersey.server.impl.uri.rules.HttpMethodRule.accept(HttpMethodRule.java:288)
    at com.sun.jersey.server.impl.uri.rules.SubLocatorRule.accept(SubLocatorRule.java:134)
    at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
    at com.sun.jersey.server.impl.uri.rules.SubLocatorRule.accept(SubLocatorRule.java:134)
    at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
    at com.sun.jersey.server.impl.uri.rules.ResourceClassRule.accept(ResourceClassRule.java:108)
    at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
    at com.sun.jersey.server.impl.uri.rules.RootResourceClassesRule.accept(RootResourceClassesRule.java:84)
    at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1469)
    at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1400)
    at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1349)
    at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1339)
    at com.sun.jersey.server.impl.container.grizzly.GrizzlyContainer._service(GrizzlyContainer.java:182)
    at com.sun.jersey.server.impl.container.grizzly.GrizzlyContainer.service(GrizzlyContainer.java:147)
    at org.glassfish.admin.rest.adapter.RestAdapter.service(RestAdapter.java:148)
    at com.sun.grizzly.tcp.http11.GrizzlyAdapter.service(GrizzlyAdapter.java:179)
    at com.sun.enterprise.v3.server.HK2Dispatcher.dispath(HK2Dispatcher.java:117)
    at com.sun.enterprise.v3.services.impl.ContainerMapper$Hk2DispatcherCallable.call(ContainerMapper.java:354)
    at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:195)
    at com.sun.grizzly.http.ProcessorTask.invokeAdapter(ProcessorTask.java:860)
    at com.sun.grizzly.http.ProcessorTask.doProcess(ProcessorTask.java:757)
    at com.sun.grizzly.http.ProcessorTask.process(ProcessorTask.java:1056)
    at com.sun.grizzly.http.DefaultProtocolFilter.execute(DefaultProtocolFilter.java:229)
    at com.sun.grizzly.DefaultProtocolChain.executeProtocolFilter(DefaultProtocolChain.java:137)
    at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:104)
    at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:90)
    at com.sun.grizzly.http.HttpProtocolChain.execute(HttpProtocolChain.java:79)
    at com.sun.grizzly.ProtocolChainContextTask.doCall(ProtocolChainContextTask.java:54)
    at com.sun.grizzly.SelectionKeyContextTask.call(SelectionKeyContextTask.java:59)
    at com.sun.grizzly.ContextTask.run(ContextTask.java:71)
    at com.sun.grizzly.util.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:532)
    at com.sun.grizzly.util.AbstractThreadPool$Worker.run(AbstractThreadPool.java:513)
    at java.lang.Thread.run(Thread.java:662)
Caused by: java.sql.SQLException: Error in allocating a connection. Cause: Connection could not be allocated because: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "Server key". ClientConnectionId:a3f21831-fbc0-491c-9691-2b28500d7d2b
    at com.sun.gjc.spi.base.DataSource.getConnection(DataSource.java:120)
    at org.eclipse.persistence.sessions.JNDIConnector.connect(JNDIConnector.java:123)
    ... 73 more
Caused by: javax.resource.spi.ResourceAllocationException: Error in allocating a connection. Cause: Connection could not be allocated because: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "Server key". ClientConnectionId:a3f21831-fbc0-491c-9691-2b28500d7d2b
    at com.sun.enterprise.connectors.ConnectionManagerImpl.internalGetConnection(ConnectionManagerImpl.java:307)
    at com.sun.enterprise.connectors.ConnectionManagerImpl.allocateConnection(ConnectionManagerImpl.java:190)
    at com.sun.enterprise.connectors.ConnectionManagerImpl.allocateConnection(ConnectionManagerImpl.java:165)
    at com.sun.enterprise.connectors.ConnectionManagerImpl.allocateConnection(ConnectionManagerImpl.java:160)
    at com.sun.gjc.spi.base.DataSource.getConnection(DataSource.java:113)
    ... 74 more
Caused by: com.sun.appserv.connectors.internal.api.PoolingException: Connection could not be allocated because: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "Server key". ClientConnectionId:a3f21831-fbc0-491c-9691-2b28500d7d2b
    at com.sun.enterprise.resource.pool.datastructure.RWLockDataStructure.addResource(RWLockDataStructure.java:103)
    at com.sun.enterprise.resource.pool.ConnectionPool.addResource(ConnectionPool.java:282)
    at com.sun.enterprise.resource.pool.ConnectionPool.createResourceAndAddToPool(ConnectionPool.java:1512)
    at com.sun.enterprise.resource.pool.ConnectionPool.createResources(ConnectionPool.java:944)
    at com.sun.enterprise.resource.pool.ConnectionPool.initPool(ConnectionPool.java:230)
    at com.sun.enterprise.resource.pool.ConnectionPool.internalGetResource(ConnectionPool.java:511)
    at com.sun.enterprise.resource.pool.ConnectionPool.getResource(ConnectionPool.java:381)
    at com.sun.enterprise.resource.pool.PoolManagerImpl.getResourceFromPool(PoolManagerImpl.java:245)
    at com.sun.enterprise.resource.pool.PoolManagerImpl.getResource(PoolManagerImpl.java:170)
    at com.sun.enterprise.connectors.ConnectionManagerImpl.getResource(ConnectionManagerImpl.java:338)
    at com.sun.enterprise.connectors.ConnectionManagerImpl.internalGetConnection(ConnectionManagerImpl.java:301)
    ... 78 more
Caused by: com.sun.appserv.connectors.internal.api.PoolingException: Connection could not be allocated because: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "Server key". ClientConnectionId:a3f21831-fbc0-491c-9691-2b28500d7d2b
    at com.sun.enterprise.resource.pool.ConnectionPool.createSingleResource(ConnectionPool.java:924)
    at com.sun.enterprise.resource.pool.ConnectionPool.createResource(ConnectionPool.java:1189)
    at com.sun.enterprise.resource.pool.datastructure.RWLockDataStructure.addResource(RWLockDataStructure.java:98)
    ... 88 more
Caused by: com.sun.appserv.connectors.internal.api.PoolingException: Connection could not be allocated because: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "Server key". ClientConnectionId:a3f21831-fbc0-491c-9691-2b28500d7d2b
    at com.sun.enterprise.resource.allocator.LocalTxConnectorAllocator.createResource(LocalTxConnectorAllocator.java:110)
    at com.sun.enterprise.resource.pool.ConnectionPool.createSingleResource(ConnectionPool.java:907)
    ... 90 more
Caused by: javax.resource.spi.ResourceAllocationException: Connection could not be allocated because: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "Server key". ClientConnectionId:a3f21831-fbc0-491c-9691-2b28500d7d2b
    at com.sun.gjc.spi.DSManagedConnectionFactory.createManagedConnection(DSManagedConnectionFactory.java:129)
    at com.sun.enterprise.resource.allocator.LocalTxConnectorAllocator.createResource(LocalTxConnectorAllocator.java:87)
    ... 91 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "Server key". ClientConnectionId:a3f21831-fbc0-491c-9691-2b28500d7d2b
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1667)
    at com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:1668)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1323)
    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.SQLServerDataSource.getConnectionInternal(SQLServerDataSource.java:621)
    at com.microsoft.sqlserver.jdbc.SQLServerDataSource.getConnection(SQLServerDataSource.java:57)
    at com.sun.gjc.spi.DSManagedConnectionFactory.createManagedConnection(DSManagedConnectionFactory.java:115)
    ... 92 more
Caused by: javax.net.ssl.SSLException: Server key
    at com.sun.net.ssl.internal.ssl.Handshaker.throwSSLException(Handshaker.java:927)
    at com.sun.net.ssl.internal.ssl.ClientHandshaker.processMessage(ClientHandshaker.java:202)
    at com.sun.net.ssl.internal.ssl.Handshaker.processLoop(Handshaker.java:593)
    at com.sun.net.ssl.internal.ssl.Handshaker.process_record(Handshaker.java:529)
    at com.sun.net.ssl.internal.ssl.SSLSocketImpl.readRecord(SSLSocketImpl.java:943)
    at com.sun.net.ssl.internal.ssl.SSLSocketImpl.performInitialHandshake(SSLSocketImpl.java:1188)
    at com.sun.net.ssl.internal.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1215)
    at com.sun.net.ssl.internal.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1199)
    at com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:1618)
    ... 98 more
Caused by: java.security.spec.InvalidKeySpecException: Unknown KeySpec type: java.security.spec.ECPublicKeySpec
    at org.bouncycastle.jce.provider.asymmetric.ec.KeyFactory.engineGeneratePublic(Unknown Source)
    at java.security.KeyFactory.generatePublic(KeyFactory.java:304)
    at com.sun.net.ssl.internal.ssl.HandshakeMessage$ECDH_ServerKeyExchange.<init>(HandshakeMessage.java:922)
    at com.sun.net.ssl.internal.ssl.ClientHandshaker.processMessage(ClientHandshaker.java:198)
    ... 105 more

I tried many, way too many things, the last attempt was: to generate self-signed certificate in MS SQL server (that is the database server I am trying to use through the agency of JDBC as a data store for that java application packaged in .ear file), added to trusted certificates in ms sql server. I was trying not to use ssl, but it looks like it needs it any way since stack trace reads:

Cause: Connection could not be allocated because: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "Server key". ClientConnectionId:a3f21831-fbc0-491c-9691-2b28500d7d2b

Then I have exported that certificate to a directory on c drive (.cer file) and imported it into trusted store in JDBC. I set

-Dcom.sun.enterprise.security.httpsOutboundKeyAlias

to point to the alias for the imported certificate in JDBC trust store, which is by the way under domain/domain_name/config directory in glassfish server.

And ...

... And I still get the same exception as above described. I am trying to FIGHT with this error for more than 2 weeks by now and I had lost any hope, unless, you guys know/have any idea what to do?

EDIT: I would also add, that I can establish JDBC connection from Eclipse for instance and when I run this simple app from Eclipse, it works fine:

package sqlserver;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Test1 {

    public static void main(String[] args) {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        } catch (ClassNotFoundException e1) {
            System.out.println("Failed to register the driver.");
        }

        String connectionUrl = "jdbc:sqlserver://localhost:3306;" +
                   "databaseName=target;integratedSecurity=true;";
        try {
            Connection con = DriverManager.getConnection(connectionUrl);
            Statement stmt = con.createStatement();
            String s = "use target";
            String sql = "SELECT * FROM dbo.Colori";
            ResultSet rs = stmt.executeQuery(sql);
            while(rs.next()){
                int id  = rs.getInt("Id");
                String desc = rs.getString("Descrizione");
                System.out.println(id + " : " + desc);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Problem occurs, only, when I try to deploy application to Glassfish, as if magically it was completely cut out from SQL Server for good.

I am using SQL Server, Java EE 6 Persistence Framework, Eclipse Link, sqljdbc4 and Glassfish 3.1.2.2.


Answer:

So here there goes an answer, all it took was basically to delete glassfish installation and install it again. I have also exported self signed ssl certificate (which I have previously generated for SQL Server, using MSSQL utility -> mmc console) to a directory on C drive: C:\MSSQL_exports, from there I have imported it to {glassfish_installation_directory}\glassfish3\glassfish\domains\domain_name\config into cacerts.jks and keystore.jks. I have also imported above mentioned certificate (.cer file) into cacerts file of my jre installation in the directory: C:\Program Files\Java\jre6\lib\security.

As explained certificate was created as a .cer file using mmc utility which comes with SQL Server and Mcrosoft SQL Server Management Studio installation. That certificate was also installed into SQL Server as SSL certificate, using SQL Server Configuration Manager (Server Network Configuration > Protocols for SQLEXPRESS > Properties > Certificate tab, force encryption - NOT SET).

All the certificate imports were done using keytool utility with

-alias certy6

That matters only in a context of the last thing I did, which was to set jvm property in domain.xml file (which can be found here {glassfish_installation_directory}\glassfish3\glassfish\domains\domain_name\config) as follows:

-Dcom.sun.enterprise.security.httpsOutboundKeyAlias=certy6

Btw... I have also set eclipselink.ddl-generation property to update when setting up EclipseLink (I was using that too!) configuration for my Java application which I was trying to deploy to Glassfish.

Here is a link, how to do it: http://www.eclipse.org/eclipselink/documentation/2.4/jpa/extensions/persistenceproperties_ref.htm

I have also corrected few other things which looked wrong in EclipseLink configuration, in particular type of database was set to mysql, so I changed it to sqlserver. So if you use it too (EclipseLink that is), just make sure your EclipseLink configurations are correct and perhaps test them (as I did) through an agency of some testing app from withing Eclipse (I mean IDE here!).

Restart SQL Server and Glassfish, and apparently now it works. Hope, this can help someone one day to solve his or her problems with a little bit less of a hassle than that which I had to go through and despite having moderate number of views, I could not get any response to my problem. It was also very hard to find any useful documentation on the internet related to this, so maybe now I will live this for the benefit of others.

Please vote up the answer, if it helps you.

Question:

I'm trying to auto-create tables from Java in database on Microsoft SQL Server 2012. I'm using JPA and there is my persistence.xml :

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
<persistence-unit name="PU" transaction-type="JTA">
    <provider>org.hibernate.ejb.HibernatePersistence</provider>
    <jta-data-source>DS</jta-data-source>
    <class>msg.Message</class>
    <class>msg.Response</class>
    <properties>
        <property name="hibernate.connection.driver_class" value="com.microsoft.jdbc.sqlserver.SQLServerDriver"/>
        <property name="hibernate.dialect" value="org.hibernate.dialect.SQLServerDialect"/>
        <property name="hibernate.show_sql" value="true"/>
        <property name="hibernate.hbm2ddl.auto" value="create"/>
    </properties>
</persistence-unit>

JDBC Connection Pool on glassfish ping with succeed (additional properties

URL: jdbc:sqlserver://127.0.0.1;databaseName=test;instanceName=SQLEXPRESS; User: user Password: password PortNumber: 1433 )

header of Message.java

@Entity
@Table(name = "message", schema = "xxx_msg")
public class Message implements Serializable {

@Id
@Basic(optional = false)
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "id")
private Integer id;
@JoinColumn(name = "sender_id", referencedColumnName = "id")
@ManyToOne
private Person senderId;

There are no errors on build or deploy, database is up but no tables are created


Answer:

Fixed

I had to create schemas with Microsoft SQL Server Management Studio first (my database -> new Query -> CREATE SCHEMA xxx_msg )

Question:

So I'm starting a web application and I want to use JSP pages (I've used them before) to access dynamically to the database and retrieve data from different tables.

I have by now a basic DatabaseHelper class, an App class that only tests the DB helper connection and a simple query, and a jsp file. The problem I'm facing is that the Database connection works great if run from the App.java class, but if I run it from the JSP file it will throw a SQLException saying it didn't find a suitable driver. (I'll leave code and error message below)

I've tried different options I've read here in StackOverflow and other pages: I put the driver in the server's classpath, in the WEB-INF/lib, in the project's build path as external JAR (this one works for the Java app class mentioned before)...

And here is the environment:

  • Database: Microsoft SQL Server 2017

  • Driver: sqljdbc42.jar

  • Server: Glassfish 4.0

  • IDE: Eclipse Oxygen.3

So any hint on how can I solve this problem and use the Database helper from the jsp file will be happily welcome.

Thanks!

Error message:

[2018-03-28T12:39:48.883+0200] [glassfish 4.0] [SEVERE] [] [pl.mais.db.DBHelper] [tid: _ThreadID=21 _ThreadName=http-listener-1(3)] [timeMillis: 1522233588883] [levelValue: 1000] [[

java.sql.SQLException: No suitable driver found for jdbc:sqlserver://localhost;databaseName=campus_db;user=******;password=*****
    at java.sql.DriverManager.getConnection(DriverManager.java:689)
    at java.sql.DriverManager.getConnection(DriverManager.java:270)
    at pl.mais.db.DBHelper.open(DBHelper.java:41)
    at org.apache.jsp.index_jsp._jspService(index_jsp.java:58)
    at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:111)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
    at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:411)
    at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:473)
    at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:377)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
    at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1682)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:318)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:160)
    at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:734)
    at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:673)
    at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:99)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:174)
    at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:357)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:260)
    at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:188)
    at org.glassfish.grizzly.http.server.HttpHandler.runService(HttpHandler.java:191)
    at org.glassfish.grizzly.http.server.HttpHandler.doHandle(HttpHandler.java:168)
    at org.glassfish.grizzly.http.server.HttpServerFilter.handleRead(HttpServerFilter.java:189)
    at org.glassfish.grizzly.filterchain.ExecutorResolver$9.execute(ExecutorResolver.java:119)
    at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeFilter(DefaultFilterChain.java:288)
    at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeChainPart(DefaultFilterChain.java:206)
    at org.glassfish.grizzly.filterchain.DefaultFilterChain.execute(DefaultFilterChain.java:136)
    at org.glassfish.grizzly.filterchain.DefaultFilterChain.process(DefaultFilterChain.java:114)
    at org.glassfish.grizzly.ProcessorExecutor.execute(ProcessorExecutor.java:77)
    at org.glassfish.grizzly.nio.transport.TCPNIOTransport.fireIOEvent(TCPNIOTransport.java:838)
    at org.glassfish.grizzly.strategies.AbstractIOStrategy.fireIOEvent(AbstractIOStrategy.java:113)
    at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.run0(WorkerThreadIOStrategy.java:115)
    at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.access$100(WorkerThreadIOStrategy.java:55)
    at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy$WorkerThreadRunnable.run(WorkerThreadIOStrategy.java:135)
    at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:564)
    at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.run(AbstractThreadPool.java:544)
    at java.lang.Thread.run(Thread.java:748)
]]

App.java

package pl.mais.general;

import pl.mais.db.DBHelper;

public class App {
    public static void main (String[] args) {
        DBHelper db = new DBHelper();
        db.open();
        db.testSelectFaculties();
        db.close();
    }
}

DBHelper.java

package pl.mais.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * 
 * @author sergi
 *
 */
public class DBHelper {
    // JDBC driver name and database URL

    private final String JDBC_DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";  
    private final String DB_URL = "jdbc:sqlserver://localhost;databaseName=campus_db;";

    //  Database credentials
    private static final String DB_USER = "*****";
    private static final String DB_PASS = "*****";

    private Connection conn = null;
    private Statement stmt = null;

    public DBHelper() {
        try {
            Class.forName(JDBC_DRIVER);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void open() {
        try {
            String connectionUrl = DB_URL + "user=" + DB_USER + ";password=" + DB_PASS;
            //System.out.println("Connecting to database...");
            conn = DriverManager.getConnection(connectionUrl);
            //System.out.println("Creating statement...");
            stmt = conn.createStatement();
        } catch (SQLException ex) {
            Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void close() {
        try {
            stmt.close();
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public String[] testSelectFaculties() {
        try {  
            // Create and execute an SQL statement that returns some data.  
            String SQL = "SELECT * FROM faculties";  
            stmt = conn.createStatement();  
            ResultSet rs = stmt.executeQuery(SQL);  
            ArrayList<String> results = new ArrayList<String>();
            // Iterate through the data in the result set and display it.  
            while (rs.next()) {  
               results.add(rs.getString(1) + " - " + rs.getString(2)); 
               System.out.println(rs.getString(1) + " - " + rs.getString(2));
            }  

            return (String[])results.toArray();
         }  

         catch (Exception e) {  
            e.printStackTrace();  

         }  
        return null;
    }
}

index.jsp

<%@page import="pl.mais.db.DBHelper"%>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>

<body>
<%
    DBHelper db = new DBHelper();
    db.open();
    String[] faculties = db.testSelectFaculties();
    db.close();
    for (int i = 0; i < faculties.length; i++) {
        %>
        <h2>
        <%=
            faculties[i]
        %>
        </h2>
        <%
    }
%>

</body>
</html>

Answer:

Making the JDBC Driver JAR Files Accessible:

To integrate the JDBC driver into a GlassFish Server domain, copy the JAR files into the domain-dir/lib directory, then restart the server. This makes classes accessible to all applications or modules deployed on servers that share the same configuration.

Source: Oracle documentation.

Question:

I am running my java web application on glassfish server v4.1 in netbeans v8.0.2, using sqljdbc42 and this connection code-

    Connection connection;
    String url = "jdbc:sqlserver://localhost:1433;databaseName=Lista;integratedSecurity=true";
    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    connection = DriverManager.getConnection(url);

When i open my ide and run my project for the first time the glassfish server starts automatically since my project's server is glassfish, and i can perform any crud operation with my Ms-Sql server2012.

But the problem starts when i make changes in any file keeping the server on and run the project again and try to perform any crud operation i get this following error-

com.microsoft.sqlserver.jdbc.SQLServerException: This driver is not configured for integrated authentication

But if i stop the server before changing any file and start after changing and run the project then it works without any error.

Stopping and re-starting the server again and again taking so many times from my total project time! Any help or suggestion will be appreciated.

Thank You.


Answer:

I solved my problem!

The error (described in my question above) i was getting because the glassfish server had not any access on sqljdbc42.jar file. so i have placed the sqljdbc42.jar file into my glassfish server in this location-

glassfish-4.1\glassfish\domains\domain1\lib.

And it's now working perfectly.

Thank you everyone!

Question:

I'm trying to connect to a failover cluster using the glassfish connection pooling. My resource type is XADataSource, classname JtdsDataSource. My additional properties are:

  • User
  • DatabaseName
  • Password
  • PortNumber
  • serverName

All of these work for connecting normally but I have been unable to find the correct property name for connecting to the mirror for this database. On the basis of this page https://msdn.microsoft.com/en-us/library/ms175484.aspx I've tried FailoverPartner and Failover_Partner but during testing where the primary database was brought down while the server was running glassfish was still trying to connect to the primary.

With nothing else to go on regarding this particular scenario, I ask what specific property name is used to specify my mirror database in Glassfish.

Thanks in advance.


Answer:

Setting of database mirroring has little to do with Glassfish in particular, it must be supported by the JDBC driver.

It seems that you are using JTDS driver to connect to MS SQL. Unfortunately, it does not support database mirroring - some guys in this SO question discuss possibilities to create a wrapper around this driver, if you cannot afford to switch the driver.

You may be better off with the official driver from Microsoft. It should support failoverPartner parameter in connection string.