Spring Boot Could not connect to Azure SQL Server

Question

I am using springboot to connect to Azure server and for more than a week I have been battling my head to resolve this but I could not find solution. I have search the internet but I could not find a useful information. I have copied sqljdbc_auth.dll in various locations such as system32, Jre/bin folder, the working directory as suggested by various post but I still do not get a connection to the azure SQL Server.

Below is the latest error.

com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'dbadmin'. ClientConnectionId:797a74b3-d7fa-4018-9cb7-cb6d3e209a8a at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217) ~[sqljdbc4-4.0.jar:na] at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(tdsparser.java:279) ~[sqljdbc4-4.0.jar:na] at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:99) ~[sqljdbc4-4.0.jar:na] at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:4346) ~[sqljdbc4-4.0.jar:na] at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:3160) ~[sqljdbc4-4.0.jar:na] at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$100(SQLServerConnection.java:43) ~[sqljdbc4-4.0.jar:na] at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:3123) ~[sqljdbc4-4.0.jar:na] at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7505) ~[sqljdbc4-4.0.jar:na] at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2445) ~[sqljdbc4-4.0.jar:na] at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1981) ~[sqljdbc4-4.0.jar:na] at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:1628) ~[sqljdbc4-4.0.jar:na] at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1459) ~[sqljdbc4-4.0.jar:na] at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:773) ~[sqljdbc4-4.0.jar:na] at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1168) ~[sqljdbc4-4.0.jar:na] at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:112) ~[HikariCP-2.6.3.jar:na] at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:118) ~[HikariCP-2.6.3.jar:na] at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:358) ~[HikariCP-2.6.3.jar:na] at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:201) ~[HikariCP-2.6.3.jar:na] at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:443) [HikariCP-2.6.3.jar:na] at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:514) [HikariCP-2.6.3.jar:na] at com.zaxxer.hikari.pool.HikariPool.(HikariPool.java:111) [HikariCP-2.6.3.jar:na] at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:97) [HikariCP-2.6.3.jar:na] at org.springframework.jdbc.datasource.DataSourceUtils.fetchConnection(DataSourceUtils.java:151) [spring-jdbc-5.0.0.RC3.jar:5.0.0.RC3] at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:115) [spring-jdbc-5.0.0.RC3.jar:5.0.0.RC3] at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:78) [spring-jdbc-5.0.0.RC3.jar:5.0.0.RC3] at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:319) [spring-jdbc-5.0.0.RC3.jar:5.0.0.RC3] at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:357) [spring-jdbc-5.0.0.RC3.jar:5.0.0.RC3] at org.springframework.boot.autoconfigure.orm.jpa.DatabaseLookup.getDatabase(DatabaseLookup.java:72) [spring-boot-autoconfigure-2.0.0.M3.jar:2.0.0.M3] at org.springframework.boot.autoconfigure.orm.jpa.JpaProperties.determineDatabase(JpaProperties.java:139) [spring-boot-autoconfigure-2.0.0.M3.jar:2.0.0.M3] at org.springframework.boot.autoconfigure.orm.jpa.JpaBaseConfiguration.jpaVendorAdapter(JpaBaseConfiguration.java:106) [spring-boot-autoconfigure-2.0.0.M3.jar:2.0.0.M3] at org.springframework.boot.autoconfigure.orm.jpa.HibernateJpaAutoConfiguration$$EnhancerBySpringCGLIB$$444efd7e.CGLIB$jpaVendorAdapter$4() [spring-boot-autoconfigure-2.0.0.M3.jar:2.0.0.M3] at org.springframework.boot.autoconfigure.orm.jpa.HibernateJpaAutoConfiguration$$EnhancerBySpringCGLIB$$444efd7e$$FastClassBySpringCGLIB$$399b383b.invoke() [spring-boot-autoconfigure-2.0.0.M3.jar:2.0.0.M3] at org.springframework.cglib.proxy.MethodProxy.invokeSuper(MethodProxy.java:228) [spring-core-5.0.0.RC3.jar:5.0.0.RC3] at org.springframework.context.annotation.ConfigurationClassEnhancer$BeanMethodInterceptor.intercept(ConfigurationClassEnhancer.java:360) [spring-context-5.0.0.RC3.jar:5.0.0.RC3] at org.springframework.boot.autoconfigure.orm.jpa.HibernateJpaAutoConfiguration$$EnhancerBySpringCGLIB$$444efd7e.jpaVendorAdapter() [spring-boot-autoconfigure-2.0.0.M3.jar:2.0.0.M3] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_121] at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) ~[na:1.8.0_121] at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) ~[na:1.8.0_121] at java.lang.reflect.Method.invoke(Unknown Source) ~[na:1.8.0_121] at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:155) [spring-beans-5.0.0.RC3.jar:5.0.0.RC3] at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:575) [spring-beans-5.0.0.RC3.jar:5.0.0.RC3] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.instantiateUsingFactoryMethod(AbstractAutowireCapableBeanFactory.java:1261) [spring-beans-5.0.0.RC3.jar:5.0.0.RC3] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1109) [spring-beans-5.0.0.RC3.jar:5.0.0.RC3] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:543) [spring-beans-5.0.0.RC3.jar:5.0.0.RC3] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:499) [spring-beans-5.0.0.RC3.jar:5.0.0.RC3] at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:312) [spring-beans-5.0.0.RC3.jar:5.0.0.RC3] at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234) ~[spring-beans-5.0.0.RC3.jar:5.0.0.RC3] at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:310) [spring-beans-5.0.0.RC3.jar:5.0.0.RC3] at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:205) [spring-beans-5.0.0.RC3.jar:5.0.0.RC3] at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:255) ~[spring-beans-5.0.0.RC3.jar:5.0.0.RC3] at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1131) ~[spring-beans-5.0.0.RC3.jar:5.0.0.RC3] at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1058) ~[spring-beans-5.0.0.RC3.jar:5.0.0.RC3] at org.springframework.beans.factory.support.ConstructorResolver.resolveAutowiredArgument(ConstructorResolver.java:812) [spring-beans-5.0.0.RC3.jar:5.0.0.RC3] at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:718) [spring-beans-5.0.0.RC3.jar:5.0.0.RC3] at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:458) [spring-beans-5.0.0.RC3.jar:5.0.0.RC3] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.instantiateUsingFactoryMethod(AbstractAutowireCapableBeanFactory.java:1261) [spring-beans-5.0.0.RC3.jar:5.0.0.RC3] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1109) [spring-beans-5.0.0.RC3.jar:5.0.0.RC3] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:543) [spring-beans-5.0.0.RC3.jar:5.0.0.RC3] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:499) [spring-beans-5.0.0.RC3.jar:5.0.0.RC3] at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:312) [spring-beans-5.0.0.RC3.jar:5.0.0.RC3] at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234) ~[spring-beans-5.0.0.RC3.jar:5.0.0.RC3] at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:310) [spring-beans-5.0.0.RC3.jar:5.0.0.RC3] at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:205) [spring-beans-5.0.0.RC3.jar:5.0.0.RC3] at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:255) ~[spring-beans-5.0.0.RC3.jar:5.0.0.RC3] at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1131) ~[spring-beans-5.0.0.RC3.jar:5.0.0.RC3] at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1058) ~[spring-beans-5.0.0.RC3.jar:5.0.0.RC3] at org.springframework.beans.factory.support.ConstructorResolver.resolveAutowiredArgument(ConstructorResolver.java:812) [spring-beans-5.0.0.RC3.jar:5.0.0.RC3] at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:718) [spring-beans-5.0.0.RC3.jar:5.0.0.RC3] at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:458) [spring-beans-5.0.0.RC3.jar:5.0.0.RC3] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.instantiateUsingFactoryMethod(AbstractAutowireCapableBeanFactory.java:1261) [spring-beans-5.0.0.RC3.jar:5.0.0.RC3] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1109) [spring-beans-5.0.0.RC3.jar:5.0.0.RC3] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:543) [spring-beans-5.0.0.RC3.jar:5.0.0.RC3] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:499) [spring-beans-5.0.0.RC3.jar:5.0.0.RC3] at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:312) [spring-beans-5.0.0.RC3.jar:5.0.0.RC3] at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234) ~[spring-beans-5.0.0.RC3.jar:5.0.0.RC3] at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:310) [spring-beans-5.0.0.RC3.jar:5.0.0.RC3] at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:200) [spring-beans-5.0.0.RC3.jar:5.0.0.RC3] at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1083) ~[spring-context-5.0.0.RC3.jar:5.0.0.RC3] at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:858) ~[spring-context-5.0.0.RC3.jar:5.0.0.RC3] at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:549) ~[spring-context-5.0.0.RC3.jar:5.0.0.RC3] at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:122) ~[spring-boot-2.0.0.M3.jar:2.0.0.M3] at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:750) ~[spring-boot-2.0.0.M3.jar:2.0.0.M3] at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:386) ~[spring-boot-2.0.0.M3.jar:2.0.0.M3] at org.springframework.boot.SpringApplication.run(SpringApplication.java:327) ~[spring-boot-2.0.0.M3.jar:2.0.0.M3] at org.springframework.boot.SpringApplication.run(SpringApplication.java:1245) ~[spring-boot-2.0.0.M3.jar:2.0.0.M3] at org.springframework.boot.SpringApplication.run(SpringApplication.java:1233) ~[spring-boot-2.0.0.M3.jar:2.0.0.M3] at za.aithenticate.admin.AiAdminApplication.main(AiAdminApplication.java:14) ~[classes/:na]

2017-09-18 09:27:15.699 WARN 29076 --- [ main] o.s.b.a.orm.jpa.DatabaseLookup : Unable to determine jdbc url from datasource

org.springframework.jdbc.support.MetaDataAccessException: Could not get Connection for extracting meta data; nested exception is org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection;

And this is the configuration in my Application.properties file

spring.profiles.active=Admin Console Production spring.datasource.url=jdbc:sqlserver://aiadmin.database.windows.net:1433;database=aiAdmin;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;integratedSecurity=false;loginTimeout=30; spring.datasource.username=aiadmin spring.datasource.password=*************** spring.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver spring.jpa.show-sql=true spring.jpa.hibernate.dialect=org.hibernate.dialect.SQLServer2012Dialect spring.jpa.hibernate.ddl-auto = update spring.thymeleaf.cache=false

Answer 1

I suggest that you try to append @aiadmin which is your server name after your username. As described here: https://learn.microsoft.com/en-us/azure/mysql/howto-create-users, the username used to connect to Azure database need be appended with that, even you didn't when you create the user account.

Answer 2

Check in Azure sql server firewall settings and open the firewall for testing for all ips from 0.0.0.0 to 254.254.254.254 and save it and add client ip also and again click save and then test