Error when get connection from SQLServerDataSource

Question

I have a local SQL Server instance. If I try to connect with Management Studio (SQL Server authentication, with username and password) everything works fine.

However, when I try to create a connection from a Spring Boot application I get some errors.

This is the code for the Datasource (dbUsername and dbpassword are correct)

@Bean
public DataSource getDataSource() throws SQLServerException {
    SQLServerDataSource ds = new SQLServerDataSource();
    ds.setUser(dbUsername);
    ds.setPassword(dbPassword);
    ds.setServerName("localhost");
    ds.setPortNumber(1433);
    ds.setDatabaseName("mydb");
    return ds;
}

When I try retrieve a connection with

Connection connection = dataSource.getConnection();

I get this error:

com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user '"sa"'.

If I add ds.setIntegratedSecurity(true); I have a different error:

com.microsoft.sqlserver.jdbc.SQLServerException: This driver is not configured for integrated authentication.

This is the JDBC driver that I'm using:

<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <version>8.4.1.jre8</version>
</dependency>

Any suggestions?

Why are you even using SQLServerDataSource? In a web application it is better to use a data source that provides connection pooling. Spring Boot out of the box uses HikariCP for that, where you can define the datasource entirely in the config, without having to define a bean like this. — Mark Rotteveel
– Mark Rotteveel, Commented Jan 22, 2021 at 11:23
Hi @MarkRotteveel thank you for your comment; could you give me a link where I can find an example? Thanks a lot — S-Wing
– S-Wing, Commented Jan 22, 2021 at 11:26
You could start with Spring Boot Features: Working with SQL Databases — Mark Rotteveel
– Mark Rotteveel, Commented Jan 22, 2021 at 11:40

Itamar Podjarski · Accepted Answer · 2021-01-22 16:35:47Z

1

Personally, I encourage you to connect your DB by setting the configuration in the application.yaml file. It is in src/resources/application.yaml

Create an application.yaml file (if it does not exist already).
Copy this content:

spring:
  datasource:
    URL: jdbc:sqlserver://localhost:1433/mydb?reconnect=true
    username: username
    password: password

Replace the username and password with yours.
Delete the bean you've created.
Good luck!

edited Jan 22, 2021 at 16:35

answered Jan 22, 2021 at 11:27

Itamar Podjarski

3973 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mark Rotteveel Over a year ago

Given the OP is using SQL Server, they would also need to replace the url.

Mark Rotteveel Over a year ago

URLs for SQL server start with jdbc:sqlserver: and are governed by the syntax documented on Building the Connection URL

Itamar Podjarski Over a year ago

@MarkRotteveel Thanks again! I looked at the docs of MSSQL :)

S-Wing Over a year ago

I solve using the application.properties file as suggested; I add these 3 entries spring.datasource.url, spring.datasource.username,spring.datasource.password and spring created a dataSource bean for me.

Collectives™ on Stack Overflow

Error when get connection from SQLServerDataSource

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related