Conenct mssql using windows authentication from spring boot

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).