Atomikos Ms sql Spring Boot TCP/IP connection failure

Question

I am currently using Atomikos and trying to connect to Ms SQL server. I am getting the following error:

2021-09-21 13:48:21.816  INFO 25608 --- [           main] c.a.d.xa.XATransactionalResource         : strideXADataSource: refreshed XAResource
2021-09-21 13:48:36.458  WARN 25608 --- [           main] c.a.jdbc.AtomikosXAConnectionFactory     : XAConnectionFactory: failed to create pooled connection - DBMS down or unreachable?


com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host jdbc:sqlserver://127.0.0.1;databaseName=sample;, port 1433 has failed. Error: "jdbc:sqlserver://127.0.0.1;databaseName=sample;. 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) ~[mssql-jdbc-9.4.0.jre8.jar:na]
        at com.microsoft.sqlserver.jdbc.SQLServerException.ConvertConnectExceptionToSQLServerException(SQLServerException.java:288) ~[mssql-jdbc-9.4.0.jre8.jar:na]
        at com.microsoft.sqlserver.jdbc.SocketFinder.findSocket(IOBuffer.java:2466) ~[mssql-jdbc-9.4.0.jre8.jar:na]
        at com.microsoft.sqlserver.jdbc.TDSChannel.open(IOBuffer.java:676) ~[mssql-jdbc-9.4.0.jre8.jar:na]
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:2957) ~[mssql-jdbc-9.4.0.jre8.jar:na]
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:2628) ~[mssql-jdbc-9.4.0.jre8.jar:na]
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:2471) ~[mssql-jdbc-9.4.0.jre8.jar:na]
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1470) ~[mssql-jdbc-9.4.0.jre8.jar:na]
        at com.microsoft.sqlserver.jdbc.SQLServerDataSource.getConnectionInternal(SQLServerDataSource.java:1317) ~[mssql-jdbc-9.4.0.jre8.jar:na]
        at com.microsoft.sqlserver.jdbc.SQLServerPooledConnection.createNewConnection(SQLServerPooledConnection.java:94) ~[mssql-jdbc-9.4.0.jre8.jar:na]
        at com.microsoft.sqlserver.jdbc.SQLServerPooledConnection.<init>(SQLServerPooledConnection.java:69) ~[mssql-jdbc-9.4.0.jre8.jar:na]
        at com.microsoft.sqlserver.jdbc.SQLServerXAConnection.<init>(SQLServerXAConnection.java:41) ~[mssql-jdbc-9.4.0.jre8.jar:na]
        at com.microsoft.sqlserver.jdbc.SQLServerXADataSource.getXAConnection(SQLServerXADataSource.java:60) ~[mssql-jdbc-9.4.0.jre8.jar:na]
        at com.microsoft.sqlserver.jdbc.SQLServerXADataSource.getXAConnection(SQLServerXADataSource.java:92) ~[mssql-jdbc-9.4.0.jre8.jar:na]
        at com.atomikos.jdbc.AtomikosXAConnectionFactory.createPooledConnection(AtomikosXAConnectionFactory.java:43) ~[transactions-jdbc-4.0.6.jar:na]
        at com.atomikos.datasource.pool.ConnectionPool.createPooledConnection(ConnectionPool.java:98) [transactions-jta-4.0.6.jar:na]
        at com.atomikos.datasource.pool.ConnectionPool.addConnectionsIfMinPoolSizeNotReached(ConnectionPool.java:86) [transactions-jta-4.0.6.jar:na]
        at com.atomikos.datasource.pool.ConnectionPool.init(ConnectionPool.java:60) [transactions-jta-4.0.6.jar:na]
        at com.atomikos.datasource.pool.ConnectionPool.<init>(ConnectionPool.java:49) [transactions-jta-4.0.6.jar:na]
        at com.atomikos.datasource.pool.ConnectionPoolWithConcurrentValidation.<init>(ConnectionPoolWithConcurrentValidation.java:23) [transactions-jta-4.0.6.jar:na]
        at com.atomikos.jdbc.AbstractDataSourceBean.init(AbstractDataSourceBean.java:294) [transactions-jdbc-4.0.6.jar:na]
        at org.springframework.boot.jta.atomikos.AtomikosDataSourceBean.afterPropertiesSet(AtomikosDataSourceBean.java:49) [spring-boot-2.5.0.jar:2.5.0]
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1845) [spring-beans-5.3.7.jar:5.3.7]
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1782) [spring-beans-5.3.7.jar:5.3.7]
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:602) [spring-beans-5.3.7.jar:5.3.7]
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:524) [spring-beans-5.3.7.jar:5.3.7]
        at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:335) [spring-beans-5.3.7.jar:5.3.7]
        at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234) ~[spring-beans-5.3.7.jar:5.3.7]
        at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:333) [spring-beans-5.3.7.jar:5.3.7]
        at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208) [spring-beans-5.3.7.jar:5.3.7]
        at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:276) ~[spring-beans-5.3.7.jar:5.3.7]
        at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1380) ~[spring-beans-5.3.7.jar:5.3.7]
        at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1300) ~[spring-beans-5.3.7.jar:5.3.7]
        at org.springframework.beans.factory.support.ConstructorResolver.resolveAutowiredArgument(ConstructorResolver.java:887) ~[spring-beans-5.3.7.jar:5.3.7]
        at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:791) ~[spring-beans-5.3.7.jar:5.3.7]
        at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:541) ~[spring-beans-5.3.7.jar:5.3.7]
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.instantiateUsingFactoryMethod(AbstractAutowireCapableBeanFactory.java:1334) [spring-beans-5.3.7.
jar:5.3.7]
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1177) [spring-beans-5.3.7.jar:5.3.7]
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:564) [spring-beans-5.3.7.jar:5.3.7]
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:524) [spring-beans-5.3.7.jar:5.3.7]
        at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:335) [spring-beans-5.3.7.jar:5.3.7]
        at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234) ~[spring-beans-5.3.7.jar:5.3.7]
        at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:333) [spring-beans-5.3.7.jar:5.3.7]
        at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208) [spring-beans-5.3.7.jar:5.3.7]
        at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:322) [spring-beans-5.3.7.jar:5.3.7]
        at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208) [spring-beans-5.3.7.jar:5.3.7]
        at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1154) ~[spring-context-5.3.7.jar:5.3.7]
        at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:908) ~[spring-context-5.3.7.jar:5.3.7]
        at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:583) ~[spring-context-5.3.7.jar:5.3.7]
        at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:145) ~[spring-boot-2.5.0.jar:2.5.0]
        at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:758) ~[spring-boot-2.5.0.jar:2.5.0]
        at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:438) ~[spring-boot-2.5.0.jar:2.5.0]
        at org.springframework.boot.SpringApplication.run(SpringApplication.java:337) ~[spring-boot-2.5.0.jar:2.5.0]
        at org.springframework.boot.SpringApplication.run(SpringApplication.java:1336) ~[spring-boot-2.5.0.jar:2.5.0]
        at org.springframework.boot.SpringApplication.run(SpringApplication.java:1325) ~[spring-boot-2.5.0.jar:2.5.0]
        at santam.apps.fusion.BrokergatewayServiceApplication.main(BrokergatewayServiceApplication.java:16) ~[classes/:na]

2021-09-21 13:48:36.525  INFO 25608 --- [           main] o.hibernate.jpa.internal.util.LogHelper  : HHH000204: Processing PersistenceUnitInfo [name: default]
2021-09-21 13:48:36.570  INFO 25608 --- [           main] org.hibernate.Version                    : HHH000412: Hibernate ORM core version 5.4.31.Final
2021-09-21 13:48:36.684  INFO 25608 --- [           main] o.hibernate.annotations.common.Version   : HCANN000001: Hibernate Commons Annotations {5.1.2.Final}
2021-09-21 13:48:37.012  INFO 25608 --- [     Atomikos:1] c.a.d.xa.XATransactionalResource         : strideXADataSource: refreshed XAResource
2021-09-21 13:48:51.650  INFO 25608 --- [     Atomikos:1] c.a.d.xa.XATransactionalResource         : strideXADataSource: refreshed XAResource
2021-09-21 13:48:51.980  WARN 25608 --- [           main] c.a.jdbc.AtomikosXAConnectionFactory     : XAConnectionFactory: failed to create pooled connection - DBMS down or unreachable?


com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host jdbc:sqlserver://127.0.0.1;databaseName=sample;, port 1433 has failed. Error: "jdbc:sqlserver://127.0.0.1;databaseName=sample;. 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) ~[mssql-jdbc-9.4.0.jre8.jar:na]
        at com.microsoft.sqlserver.jdbc.SQLServerException.ConvertConnectExceptionToSQLServerException(SQLServerException.java:288) ~[mssql-jdbc-9.4.0.jre8.jar:na]

The Configurations is as follows :

spring.datasource.stride.url=jdbc:sqlserver://127.0.0.1;databaseName=sample;
spring.datasource.stride.username=sampleUser
spring.datasource.stride.password=sampleUser
spring.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.jpa.show-sql=true

AtomikosDataSource config :

public AtomikosDataSourceBean getDataSource(String userName, String password, String server, String resourceName) {
        AtomikosDataSourceBean dataSource = new AtomikosDataSourceBean();
        dataSource.setUniqueResourceName(resourceName);
        dataSource.setXaDataSourceClassName("com.microsoft.sqlserver.jdbc.SQLServerXADataSource");
        Properties xaProperties = new Properties();
        xaProperties.setProperty("user", userName);
        xaProperties.setProperty("password", password);
        xaProperties.setProperty ( "serverName" , server );


        dataSource.setXaProperties(xaProperties);
        dataSource.setMaxPoolSize(10);


        return dataSource;
    }

I followed some possibilities of the TCP/IP connections not enabled. But they are enabled.

I am not sure if there is any setting/ configuration I am missing.

Yes, I did check and 1433 is open. I am guessing its something to do with Atomikos trying to connect or something wrong with properties — Dev, Sep 21 '21 at 12:21
Were the protocol settings already in that state? If you change the enabled protocols, or the configured addresses and ports, you need to restart the SQL Server service for the changes to take effect. — AlwaysLearning, Sep 21 '21 at 12:39
I'll note that in your last screen shot both ::1 (IPv6) and 127.0.0.1 (IPv4) are set to Enabled=No. You'll need to enable them and restart the SQL Server service. — AlwaysLearning, Sep 21 '21 at 12:41
The error complains that the server isn't accessible. Have you tried connecting with a DBA tool like SSMS, Azure Data Studio, DBeaver? If you can't connect that way, you won't be able to connect from any other application. — Panagiotis Kanavos, Sep 21 '21 at 12:47
You're trying to connect to a server running on the local machine. Is this correct? In that case the firewall isn't involved. If the server is running (is it?) you should be able to connect using a DB tool — Panagiotis Kanavos, Sep 21 '21 at 12:49
@AlwaysLearning Yes, I did restart multiple times. Also I tried to enable IPV6 and IPV4 and still not working — Dev, Sep 21 '21 at 12:55
@PanagiotisKanavos Yes, I have Intellij DataGrip and I am able to connect with it to the server — Dev, Sep 21 '21 at 12:56
It is odd that agent is not running. Usually when the agent is not running, that is an indication that you are using the express edition. But you are attempting to connect to the default instance on your local machine. — SMor, Sep 21 '21 at 13:03
@SMor I am running the free developer standard edition. (Not the express edition). — Dev, Sep 21 '21 at 13:08
@Dev did you find any solution to the issue above? I am facing the exact same situation — thanili, Nov 15 '21 at 21:46

Atomikos Ms sql Spring Boot TCP/IP connection failure

0 Answers0