SSL Connection to mssql from a Java Program

Question

How can I achieve SSL connection to mssql server through a java program for testing purpose using self-signed certificate? Currently I have tried connection using connection string as follows:

String dbUrl = "jdbc:sqlserver://" + server + ":" + port
        + ";databaseName=" + database + ";user="+   username
                    + ";password=" + password
                    + ";encrypt="+true
                    + ";integratedSecurity="+true
                    + ";trustServerCertificate="+false
                    + ";hostNameInCertificate=?"
                    + ";trustStore=?;trustStorePassword=?";

I am now confused what should be placed at "?" position in the connection string above.

Also I am able to connect with mssql from management studio by following approach:

• To Create a Self Signed Certificate:
• Go to Control Panel
• Administrative tools
• IIS Manager
• Server Certificates

• Then on server certificate, on right Panel, Click Create self-signed certificate and Give Friendly Name. > OK. Now certificate is ready.

• Open MMC, by typing mmc in cmd

• Add/Remove Snap in
• Choose Certificate
• Add
• Next, Choose Local Computer, Finish,OK
• Expand Certificates, Personal, Certificates, we can see recently added certificate.
• Select certificate, Right Click, All Tasks, Manage Private Keys

• Add MSSQL User, add give read permission.

• Now open SQL Server Configuration Manager

• SQL server network configuration, properties
• Set Force Encryption to Yes,

• In certificate Tab, Choose the Certificate recently added

• Restart SQL Server Services.

• Try encryption connection to database and connect.

But how to connect it with java program? What should be kept in trustStore and trustStorePassword fields?

Working Environment: Windows 8, and mssql server 2012

Rexford

Answer 1

I'm sure you've solved the issue by now!, but in case of anyone else with the same issue it's addressed here: https://msdn.microsoft.com/en-us/library/bb879949(v=sql.110).aspx

When the encrypt property is set to true and the trustServerCertificate property is set to true, the Microsoft JDBC Driver for SQL Server will not validate the SQL Server SSL certificate. This is usually required for allowing connections in test environments, such as where the SQL Server instance has only a self signed certificate.

String connectionUrl = 
"jdbc:sqlserver://localhost:1433;" +
"databaseName=AdventureWorks;integratedSecurity=true;" +
"encrypt=true;trustServerCertificate=true";

Fauteuil

integratedSecurity will work if you configured current windows user to SQL Server. It will pass current windows user credentials and only works on Windows. – Tawnatawney

can you clarify more what does integratedSecurity mean? – Anguilla

@SarahTammam if integratedSecurity is set as true, it will use your logged user in windows to connect to the database – Fleeman

Answer 2

In the Official documentation, it says to use this:

String connectionUrl =
    "jdbc:sqlserver://localhost:1433;" +
     "databaseName=AdventureWorks;integratedSecurity=true;" +
     "encrypt=true;trustServerCertificate=true";

But it didn't work for me, I had to remove integratedSecurity=true for it work. Final connection URL was:

spring.datasource.url=jdbc:sqlserver://888.000.999.666;database=mydatabase;encrypt=true;trustServerCertificate=true

Fatherless

Answer 3

Solution with ;encrypt=true;trustServerCertificate=true suffix is worked! ✅

---

URL Example; jdbc:sqlserver://localhost:1433;databaseName=default;encrypt=true;trustServerCertificate=true

application.yml File Example;

spring:
  datasource:
    url: jdbc:sqlserver://localhost:1433;databaseName=default;encrypt=true;trustServerCertificate=true
    username: default
    password: default
    driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
  jpa:
    properties:
      hibernate:
        dialect: org.hibernate.dialect.SQLServerDialect
        format_sql: true
    hibernate:
      naming:
        physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

Sopor