I want to connect to SQL server data base using windows authentication. I am able to connect with SQL server authentication, but the password is getting changed very frequently and my application is failing
i have following dependencies for JDBCTemplate and ms-sql driver in pom.xml
my parent is spring-boot-starter-parent of version 2.7.18 and i use java 11
<dependency>
<artifactid>org.springboot</artifactid>
<groupid>spring-jdbc</groupid>
</dependency>
<dependency>
<artifactid>com.microsoft.sqlserver</artifactid>
<groupid>mssql-jdbc</groupid>
</dependency>
In My application.yml file i have like this
spring:
datasource:
url: jdbc:sqlserver://hostName:portNumber;databaseName=dbName;authenticationScheme=NTLM;integratedSecurity=true;trustServerCertfication=true;
username:username
password:password
in my database configuration
@Value("${spring.datasource.url}")
String url;
@Value("${spring.datasource.username}")
String username;
@Value("${spring.datasource.password}")
String password;
@Bean
public Datasource dataSource(){
DriverManagerDataSource dataSource=new DriverManagerDataSource();
dataSource.setDriverClassName("com.microsoft.sqlserver.jdbc.SqlServerDriver");
dataSource.setUrl(url);
dataSource.setUserName(username);
dataSource.setPassword(password);
return dataSource;
}
@Bean
public JDBCTemplate jdbcTemplate(DataSource dataSource){
return new JDBCTemplate(dataSource);
}
using the above the code is not working the authentication is failing with Error Failed to obtain JDBC connection .
nested Exception is com.microsoft.sqlserver.jdbc.SQLServerException : Login failed.
the login is from untrusted domain and cannot be used with Windows Authentication.
when i use SQLserver authentication by changing my YML file to below the code is working as expected but failing when the password changes.
spring:
datasource:
url: jdbc:sqlserver://hostName:portNumber;databaseName=dbName;trustServerCertfication=true;
username:username
password:password
we have other application where we don’t use JDBCTemplate and gets connection by using
public Connection getConnection(){
try{
class.forName("com.microsoft.sqlserver.jdbc.SqlServerDriver");
Connection=DriverManeger.getConnection("jdbc:sqlserver://hostName:portNumber;databaseName=dbName;authenticationScheme=NTLM;integratedSecurity=true;trustServerCertfication=true;", username,password);
}
catch(Exception e){
log.error("Exception occurred : {}",e.getMessage());
}
}
with this connection is successful and able to query the result set. But we are using JDBCTemplate and want to use windows Authentication.