Shohanur Rahman's blog

Conenct mssql using windows authentication from spring boot

Shohanur Rahman's photo
Shohanur Rahman
·

2 min read

1. Add Dependencies

In your pom.xml (for Maven), add the following dependencies:

<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <version>11.2.0.jre8</version> // optional for spring boot 
</dependency>

2. Configure application.properties

Add the following properties in src/main/resources/application.properties:

tspring.datasource.url=jdbc:sqlserver://localhost:1433;databaseName=YourDatabaseName;integratedSecurity=true;
spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.username=your-username
spring.datasource.password=your-password
spring.jpa.hibernate.ddl-auto=update
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.SQLServerDialect

  • integratedSecurity=true enables Windows Authentication.

  • databaseName should be replaced with your actual database name.

  • You can leave spring.datasource.username and spring.datasource.password empty if using the current Windows account.

3. Download and Configure the DLL for Windows Authentication

We need dll because,

  • The DLL acts as a bridge between the Java application and the Windows OS.

  • It authenticates the user using the Windows security context.

  • It's required only for Windows Authentication. If you were using SQL Server Authentication (username and password), you wouldn't need the DLL.

The MSSQL JDBC driver requires a native DLL to support Windows Authentication.

  1. Download the correct version of mssql-jdbc_auth.dll from Microsoft's GitHub Releases corresponding to your JDBC driver version and OS architecture (32-bit or 64-bit).

    ie: https://github.com/microsoft/mssql-jdbc/releases/download/v12.8.1//mssql-jdbc_auth.zip

  2. Place the DLL in a directory in your system’s PATH or in your project directory.

  3. Add the JVM option to tell the driver where to find the DLL:

    • In IDE (like IntelliJ or Eclipse), set the VM options:

        -Djava.library.path="path/to/dll"

    • If running via command line, use:

        java -Djava.library.path="path/to/dll" -jar your-app.jar

4. Example Entity and Repository

Entity Class

@Entity
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String username;
    private String email;

    // Getters and Setters
}

Repository Interface

public interface UserRepository extends JpaRepository<User, Long> {
    List<User> findByUsername(String username);
}

5. Test the Connection

Create a simple REST controller to test the connection.

@RestController
@RequestMapping("/users")
public class UserController {

    private final UserRepository userRepository;

    public UserController(UserRepository userRepository) {
        this.userRepository = userRepository;
    }

    @GetMapping
    public List<User> getAllUsers() {
        return userRepository.findAll();
    }
}

  • Run your Spring Boot application.

  • Access http://localhost:8080/users to see the list of users from the database.

6. Troubleshooting Tips

  1. ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver:

    • Ensure the dependency is correctly added and Maven/Gradle is refreshed.

  2. SQL Server login errors:

    • Make sure SQL Server is configured to allow Windows Authentication.

  3. DLL Errors:

    • Verify the mssql-jdbc_auth.dll version and architecture match your Java runtime (32-bit vs 64-bit).
MSSQLSpringboot