title: Database connection is closed in spring boot app when connecting to Azure MSSQL DB if we kept idle for sometime
body: <p>we have a multitenant architecture(same application connecting to different db's in azure based upon headers) spring boot application which interacts with azure mssql db for fetching records and deployed in azure app service.</p>
  
  <p>It is working fine when we access the records through endpoint urls of spring boot rest service but if we kept idle for sometime it is throwing the below exception.</p>
  
  <pre><code>Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset by peer: socket write error
      at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:2392) ~[mssql-jdbc-6.1.0.jre7.jar!/:na]
      at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:2376) ~[mssql-jdbc-6.1.0.jre7.jar!/:na]
      at com.microsoft.sqlserver.jdbc.TDSChannel.write(IOBuffer.java:1916) ~[mssql-jdbc-6.1.0.jre7.jar!/:na]
      at com.microsoft.sqlserver.jdbc.TDSWriter.flush(IOBuffer.java:4301) ~[mssql-jdbc-6.1.0.jre7.jar!/:na]
      at com.microsoft.sqlserver.jdbc.TDSWriter.writePacket(IOBuffer.java:4202) ~[mssql-jdbc-6.1.0.jre7.jar!/:na]
      at com.microsoft.sqlserver.jdbc.TDSWriter.endMessage(IOBuffer.java:3206) ~[mssql-jdbc-6.1.0.jre7.jar!/:na]
      at com.microsoft.sqlserver.jdbc.TDSCommand.startResponse(IOBuffer.java:7959) ~[mssql-jdbc-6.1.0.jre7.jar!/:na]
      at com.microsoft.sqlserver.jdbc.TDSCommand.startResponse(IOBuffer.java:7945) ~[mssql-jdbc-6.1.0.jre7.jar!/:na]
      at com.microsoft.sqlserver.jdbc.SQLServerConnection$1ConnectionCommand.doExecute(SQLServerConnection.java:2488) ~[mssql-jdbc-6.1.0.jre7.jar!/:na]
      at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7535) ~[mssql-jdbc-6.1.0.jre7.jar!/:na]
      at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2438) ~[mssql-jdbc-6.1.0.jre7.jar!/:na]
      at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectionCommand(SQLServerConnection.java:2493) ~[mssql-jdbc-6.1.0.jre7.jar!/:na]
      at com.microsoft.sqlserver.jdbc.SQLServerConnection.setAutoCommit(SQLServerConnection.java:2633) ~[mssql-jdbc-6.1.0.jre7.jar!/:na]
      at sun.reflect.GeneratedMethodAccessor209.invoke(Unknown Source) ~[na:na]
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_144]
      at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_144]
      at org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:126) ~[tomcat-jdbc-8.5.14.jar!/:na]
      at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108) ~[tomcat-jdbc-8.5.14.jar!/:na]
      at org.apache.tomcat.jdbc.pool.interceptor.AbstractCreateStatementInterceptor.invoke(AbstractCreateStatementInterceptor.java:79) ~[tomcat-jdbc-8.5.14.jar!/:na]
      at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108) ~[tomcat-jdbc-8.5.14.jar!/:na]
      at org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:81) ~[tomcat-jdbc-8.5.14.jar!/:na]
      at com.sun.proxy.$Proxy95.setAutoCommit(Unknown Source) ~[na:na]
      at org.hibernate.resource.jdbc.internal.AbstractLogicalConnectionImplementor.begin(AbstractLogicalConnectionImplementor.java:66) ~[hibernate-core-5.2.9.Final.jar!/:5.2.9.Final]
      ... 62 common frames omitted
  
  
  Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
      at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:206) ~[mssql-jdbc-6.1.0.jre7.jar!/:na]
      at com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(SQLServerConnection.java:724) ~[mssql-jdbc-6.1.0.jre7.jar!/:na]
      at com.microsoft.sqlserver.jdbc.SQLServerConnection.prepareStatement(SQLServerConnection.java:2946) ~[mssql-jdbc-6.1.0.jre7.jar!/:na]
      at com.microsoft.sqlserver.jdbc.SQLServerConnection.prepareStatement(SQLServerConnection.java:2585) ~[mssql-jdbc-6.1.0.jre7.jar!/:na]
      at sun.reflect.GeneratedMethodAccessor189.invoke(Unknown Source) ~[na:na]
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_144]
      at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_144]
      at org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:126) ~[tomcat-jdbc-8.5.14.jar!/:na]
      at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108) ~[tomcat-jdbc-8.5.14.jar!/:na]
      at org.apache.tomcat.jdbc.pool.interceptor.AbstractCreateStatementInterceptor.invoke(AbstractCreateStatementInterceptor.java:75) ~[tomcat-jdbc-8.5.14.jar!/:na]
      at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108) ~[tomcat-jdbc-8.5.14.jar!/:na]
      at org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:81) ~[tomcat-jdbc-8.5.14.jar!/:na]
      at com.sun.proxy.$Proxy95.prepareStatement(Unknown Source) ~[na:na]
      at org.springframework.jdbc.core.JdbcTemplate$SimplePreparedStatementCreator.createPreparedStatement(JdbcTemplate.java:1521) ~[spring-jdbc-4.3.8.RELEASE.jar!/:4.3.8.RELEASE]
      at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:627) ~[spring-jdbc-4.3.8.RELEASE.jar!/:4.3.8.RELEASE]
      ... 28 common frames omitted
  </code></pre>
  
  <p><strong>Datasource configurations</strong></p>
  
  <pre><code>@ConfigurationProperties(prefix = "spring.datasource")
  public class MultiTenantConfiguration {
  
      @Autowired
      private Environment environment;
  
      Logger logger = Logger.getLogger("com.microsoft.sqlserver.jdbc");  
  
  
      @Bean
      @Primary
      public DataSource multiTenantDataSource() {
          logger.setLevel(Level.FINE);
          Map&lt;Object,Object&gt; resolvedDataSources = new HashMap&lt;Object,Object&gt;();
  
          String[] profiles = environment.getActiveProfiles();
          Pattern p = Pattern.compile("([a-z]*)\\-([a-z]{3}+)");
          OperationsKeyVault opv = new OperationsKeyVault();
          String keyVaultName = System.getenv("KEYVAULT_NAME");
          for (String profile : profiles) {
  
              Matcher m = p.matcher(profile);
              if (!m.matches()) {
                  continue;
              }
  
              DataSourceBuilder dataSourceBuilder = new DataSourceBuilder(this.getClass().getClassLoader());
  
              String tenantId = m.group(2);
  
              dataSourceBuilder
              .driverClassName(opv.GetSecret(keyVaultName, "mlsi-datasource-driver-class-name", "application/json"))
              .url(opv.GetSecret(keyVaultName, "mlsi-"+ tenantId + "-jdbc-url", "application/json"))
              .username(opv.GetSecret(keyVaultName, "mlsi-"+ tenantId + "-jdbc-username", "application/json"))
              .password(opv.GetSecret(keyVaultName, "mlsi-"+ tenantId + "-jdbc-password", "application/json"));
  
              resolvedDataSources.put(tenantId, dataSourceBuilder.build());           
          }
  
          MultiTenantDataSource dataSource = new MultiTenantDataSource();
          dataSource.setDefaultTargetDataSource(resolvedDataSources.get("fth"));
          dataSource.setTargetDataSources(resolvedDataSources);
          dataSource.afterPropertiesSet();
  
          return dataSource;
      }
  }
  </code></pre>
  
  <p><strong>Repository configuration</strong></p>
  
  <pre><code>@Configuration
  @EnableConfigurationProperties(JpaProperties.class)
  @EnableJpaRepositories(basePackages = "com.example.mls.feature.repository", entityManagerFactoryRef = "featureEntityManagerFactory")
  @EnableTransactionManagement
  @Import(MultiTenantConfiguration.class)
  @ComponentScan("com.example.mls.feature")
  public class FeatureConfiguration {
  
      @Autowired
      private JpaProperties jpaProperties;
  
      @Autowired
      @Qualifier(value="multiTenantDataSource")
      private DataSource multiTenantDataSource;
  
      @Bean(name="featureEntityManagerFactory")
      @Primary
      public EntityManagerFactory featureEntityManagerFactory() {
  
          LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
          factory.setJpaVendorAdapter(new HibernateJpaVendorAdapter());
          factory.setJpaPropertyMap(jpaProperties.getProperties());
          factory.setPackagesToScan("com.example.mls.feature.model");
          factory.setDataSource(multiTenantDataSource);
          factory.afterPropertiesSet();
  
          return factory.getObject();
      }
  
      @Bean
      public RepositoryRestConfigurer repositoryRestConfigurer() {
          return new RepositoryRestConfigurerAdapter() {
              @Override
              public void configureRepositoryRestConfiguration(RepositoryRestConfiguration config) {
                  config.exposeIdsFor(BaseUuidEntity.class, Recipient.class, NotificationRecipient.class);
              }
          };
      }
  }
  </code></pre>
  
  <p>Since azure mssql database have 4mins connection timeout which cannot be altered, we also tried several properties below for autoreconnect</p>
  
  <pre><code>spring.datasource.core.validation-query==SELECT 1
  spring.datasource.core.validation-interval=10000
  spring.datasource.core.test-on-borrow=true
  spring.datasource.core.test-while-idle=true
  spring.datasource.core.time-between-eviction-runs-millis=100000
  spring.datasource.core.max-age=120000
  </code></pre>
  
  <p>We also tried both hikari and tomcat connection pools</p>
  
  <p>So please help with this issue</p>
  
  <p>Thanks.</p>