spring SQL Server Connection issues

Refresh

April 2019

Views

335 time

1

I have migrated an existing ORM project to use SQL Server instead of MYSQL. I am using Java 8 and sqljdbc6.version - 6.2.0.jre8. I'm using maven and the reference is in the pom. I have connected to SQL Server in the past using 4.1 and I have the sqljdbc_auth.dll copied in locally. The application works without issue connecting to MySQL.

I have a persistance.xml in the ORM with this config

    <properties>
        <property name="javax.persistence.jdbc.driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
        <property name="javax.persistence.jdbc.user" value=""/>
        <property name="javax.persistence.jdbc.password" value=""/>
        <property name="javax.persistence.jdbc.url" value="jdbc:sqlserver://domain_server:1433;databaseName=dbname;integratedSecurity=true;"/>
        <property name="hibernate.dialect" value="org.hibernate.dialect.SQLServer2008Dialect"/>
        <property name="hibernate.max_fetch_depth" value="3"/>
        <property name="hibernate.current_session_context_class" value="thread"/>
        <property name="hibernate.enable_lazy_load_no_trans" value="true"/>
        <property name="hibernate.show_sql" value="true"/>
        <property name="hibernate.cache.use_second_level_cache" value="true"/>
        <property name="hibernate.cache.region.factory_class" value="org.hibernate.cache.ehcache.EhCacheRegionFactory"/>
        <property name="hibernate.cache.use_query_cache" value="true"/>
        <property name="hibernate.generate_statistics" value="true"/>
        <property name="hibernate.hbm2ddl.auto" value="none"/>
    </properties>

The unit tests are not covering 100% of the tables mapped but the connection is working without a problem and the 60%/70% of tables accessed in the tests are all retrieving data without an issue (the db activity is read_only although this is not set in the config). The database is on a local domain server running Windows2012R2 and SQL Server 2016 (compatibility level SQL Server 2008 (100)).

So far so good.

The ORM is mainly used by 1 other application which uses spring-data-jpa version 2.0.0.M1. And access the db through the ORM using repositories

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
import com.app.orm.repo.Fund;

import java.util.List;

@Repository("RepoAccountRepository")
public interface AccountRepository extends JpaRepository<Account, String{
    List<Account> findByIdentBetween(long from, long to);
}

The connectivity config is in a db.xml file (which I have cut down & 'hard coded' in an attempt to get it working)

 <beans:bean id="repoDataSource" class="org.apache.commons.dbcp2.BasicDataSource">
        <beans:property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
        <beans:property name="url" value="jdbc:sqlserver://domanserver:1433;databaseName=dbname;integratedSecurity=true;"/>
        <beans:property name="defaultAutoCommit" value="false"/>
 </beans:bean>

<beans:bean id="repoEntityManagerFactory"
                class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
        <beans:qualifier value="repo"/>
        <beans:property name="dataSource" ref="repoDataSource"/>
        <beans:property name="persistenceUnitName" value="repo"/>
        <beans:property name="packagesToScan" value="com.app.orm.repo"/>
        <beans:property name="jpaVendorAdapter">
            <beans:bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
                <beans:property name="showSql" value="false"/>
                <beans:property name="generateDdl" value="false"/>
                <beans:property name="database" value="SQL_SERVER"/>
            </beans:bean>
        </beans:property>
        <beans:property name="jpaPropertyMap">
            <beans:map>
                <entry key="hibernate.dialect" value="org.hibernate.dialect.SQLServer2008Dialect"/>
                <entry key="hibernate.hbm2ddl.auto" value="none"/>
                <entry key="hibernate.cache.use_second_level_cache" value="false"/>
            </beans:map>
        </beans:property>
    </beans:bean>

    <beans:bean id="repoTransactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
        <beans:property name="entityManagerFactory" ref="repoEntityManagerFactory"/>
    </beans:bean>

    <beans:bean class="org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor"/>

    <jpa:repositories base-package="com.app.repo"
                      entity-manager-factory-ref="repoEntityManagerFactory"
                      transaction-manager-ref="repoTransactionManager"/>

So the until tests which connect to the database do connect & return data without an issue.

When I run the application though I get

2018-01-25 08:37:02,640 [threadPoolTaskExecutor-9] ERROR SqlExceptionHelper:129 - Cannot create PoolableConnectionFactory (The TCP/IP connection to the host dbname, port 1433 has failed. Error: "null. 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.".)

I have googled this a bit and so far I have done 2 things resulting from advice offered to others (mainly on this site)

1) Started the SQL Server Browser (which was stopped)

enter image description here

2) Checked that TCP/IP is enabled and running on 1433 with no dynamic ports

enter image description here

enter image description here

enter image description here

Neither of these has made any difference. Tests from both apps still connect to db and return data.

I'm not really sure where to look now! Can anyone offer any guidance ? Sorry I should add that as I the tests work from my dev machine (where the app is failing) and as I can telnet to the server on port 1433 I'm assuming it's something to do with SQL server config rather than the firewalls)

0 answers