Jdbc Connection to Mssql Server in Windows Authentication Mode

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

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

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.

Java to SQL Server using Windows Authentication

To connect using Windows Auth with app-provided credentials, you must configure the connection to use NTLM. The current connection string is for SQL Auth.

Is there a way to connect to SQL server through windows authentication without using Integrated Security=true option from java

The same option is for .NET. The answer is NO.

Spring Boot connection to SQL Server in Windows Authentication mode

I found the solution and it was only to use

integratedSecurity = false; 

instead of

integratedSecurity = true; 

so in this case connection string will be:

database.name= DatabaseName spring.datasource.url=jdbc:sqlserver://1.2.3.4:11111;DatabaseName=${database.name};**integratedSecurity = false**;.

Why to use integratedSecurity = false?
The reason is here:

Integrated Security When false, User ID and Password are specified in the connection. When true, the current Windows account credentials are used for authentication.

Using windows authentication for SQL Server JDBC with pyspark

As shown here you can set the integratedSecurity=true to connect to SQL Server via jdbc and Windows Authentication.

Then Spark configuration it should look as next:

mssql_df = spark.read.format("jdbc") \
    .option("url", "jdbc:sqlserver://localhost:1433;databaseName=DATABASE-NAME;integratedSecurity=true") \
    .option("dbtable", "database-table-name") \
    .option("driver", "com.microsoft.sqlserver.jdbc.SQLServerDriver") \
    .load()

UPDATE:

As discussed in the comments user should place sqljdbc_auth.dll in the same folder where mssql-jdbc-7.4.1.jre12.jar lives or just set spark.driver.extraClassPath for both jars seperated by : as shown below:

.config("spark.driver.extraClassPath","/path.to/mssql-jdbc-6.4.0.jre8.jar:/path/to/sqljdbc_auth.dll")

sqljdbc_auth.dll is part of the Microsoft JDBC Driver 6.0 for SQL Server and you can download it from here. Alternatively you can just install JDBC driver on your system and specify the path where the dll is stored.

---

Related Topics