Search code examples
javaspring-bootazureazure-sql-databasejdbctemplate

Connect Spring Boot JDBCTemplate to Azure SQL, Issues with Connection String and TCP/IP

I cannot seem to connect my Java Spring Boot Project to Azure SQL with JDBCTemplate. I keep getting errors, both through Autowired, with connection string, and Option 2) Setting Data Source manually. How can I resolve this issue? However, both options work with localhost sql database on my computer.

Error: The TCP/IP connection to the host tcp:food-server.database.windows.net, port 1433 has failed. Error: "tcp:food-server.database.windows.net. 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."

Option 1) @Autowired with connectionString in application.properties file, not working

spring.datasource.url= jdbc:sqlserver://tcp:food-server.database.windows.net,1433;encrypt=true;trustServerCertificate=true;databaseName=Test
spring.datasource.username=sauser
spring.datasource.password=password12345*

Option 2) Configuration Beans with DataSource, currently not working either

@Configuration
public class ServerConfiguration {
 
    @Bean
    public DataSource getDataSource() {
        SQLServerDataSource ds = new SQLServerDataSource();
        ds.setUser("sauser");
        ds.setPassword("password12345*");
        ds.setServerName("tcp:food-server.database.windows.net");
        ds.setPortNumber(1433);
        ds.setTrustServerCertificate(true);
        ds.setDatabaseName("test");
        return ds;
    }

    @Bean
    public NamedParameterJdbcTemplate namedParameterJdbcTemplate() {
        return new NamedParameterJdbcTemplate(getDataSource());
    }

Azure IP Security Settings:

I also made sure to allow my IP Address in Azure SQL Portal, database, Set Firewall

https://learn.microsoft.com/en-us/azure/azure-sql/database/firewall-create-server-level-portal-quickstart?view=azuresql

enter image description here

Solution

  • The TCP/IP connection to the host tcp:food-server.database.windows.net, port 1433 has failed. Error: "tcp:food-server.database.windows.net. 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."

    As @tgdavies commented, the URL you have provided is not in the correct format, which may be the reason you are getting this error. "tcp: in your host name should be removed" in your URL.You can provide the URL in the following format:

    spring.datasource.url= jdbc:sqlserver://<serverName>.database.windows.net,1433;encrypt=true;trustServerCertificate=true;databaseName=<databaseName>

    By doing this, you should be able to connect to the database successfully. For more information, you can refer to the MS Document on how to prepare a configuration file to connect to Azure SQL database.