How to connect to MSSQL Server with windows authentication and Force Encryption set to true

user_3128652 0
Viewed 1278

I was connecting to Sql Server 2008 & 2008+ via Java program with

  • Java 8
  • Sql jdbc microsoft driver 4.1
  • Connection string: DriverManager.getConnection("jdbc:sqlserver://<Ip>;instance=MSSQLSERVER;domain=<domain>;IntegratedSecurity=true;ssl=request;", "administrator", "password");

I was able to connect successfully. However when I enabled Force encryption to true in the sql server via sql server configuration manager.

enter image description here I started getting following error.

com.microsoft.sqlserver.jdbc.SQLServerException: An existing connection was forcibly closed by the remote host ClientConnectionId:xxxx

FYI: I have already tried adding below parameters in connection string.

  • ssl=request
  • ssl=require
  • encrypt=true
  • trustServerCertificate=true
  • Also I have tried upgrading the driver to 4.2 & mssql driver 7.0
  • Tried jtds driver as well
  • Point to be noted: I am able to connect to instance via ssms

EDIT 1

- Another important point - It's happening only for windows authentication (enabled via IntegratedSecurity=true; in connection string). So this case is happening only when Force encryption is set to true and we try to connect in windows authentication mode.

jdbc sql-server sql java
3 Answers

@Vivien @MarkRotteveel Thanks for your answers which gave me directions.

It turned out that sql server with version less then 11.0 were having troubles and various improvements were done as part of 11.0 version of sql server which rectified errors of windows authentication + encrypted connections connectivity.

enter image description here

So Sql server 2012 and above will support windows authentication with TLSv1.2, windows authentication along with encrypted connections seamlessly. Now I am using

  • Java 8
  • Sql server driver 4.2
  • Windows 10
  • No extra parameters in connection string.

Just for reference: Found on Microsoft website. enter image description here

user_31286520

It's unclear from your original question wether you have already done this, but if not, try setting both at the same time: encrypt=true;trustServerCertificate=true

Also, what do the SQL Server logs say?

user_53602320

I think Mark Rotteveel has right.

You need to check your version of both Java and SQL Server 2008.
TLS 1.2 has been enable by default in Java 8 since the u122, and appear in SQL Server 2008 only in the service pack 4 (version 10.0.6547.0, select @@VERSION; for check it) If you don't meet the requierment, you have to choose between update the server or downgrade your jdk. Perhaps their is an option in the JDK to retrieve the behavior prior to the update 122, but if it's the case, I don't know it.

And, for information, the mssql-jdbc driver has stop the support of SQL Server 2008 after the version 6.2.

user_38640900
Related