topic Re: An example how to connect to SQL Server data using windows authentication in Data Engineering

An example how to connect to SQL Server data using windows authentication

gillzer84 — Wed, 27 Apr 2022 15:12:51 GMT

We use SQL Server to store data. I would like to connect to SQL to pull manipulate and sometimes push data back. I've seen some examples online of connecting but I cannot successfully re-create.

Re: An example how to connect to SQL Server data using windows authentication

Junee — Wed, 29 Jun 2022 12:50:14 GMT

You can use jTDS library from maven, add this to your cluster. Once installed, you can write the below code to connect to your Database.

Code in Scala will be:

import java.util.Properties
 
val driverClass = "net.sourceforge.jtds.jdbc.Driver"
val serverAdd = ""
val databaseName = ""
val domain = "" // use domain of your active directory
val database_port = ""
 
val jdbcUsername = "" 
val jdbcPassword = ""
 
val conStr = "jdbc:jtds:sqlserver://"+ serverAdd + ":" + database_port +"/" + databaseName + ";useNTLMv2=true;domain=" + domain  + ";"
 
val connectionProperties = new Properties()
connectionProperties.put("user",s"${jdbcUsername}")
connectionProperties.put("password",s"${jdbcPassword}")
connectionProperties.setProperty("Driver", driverClass)
 
 
val dataFrame = spark.read.jdbc(url = conStr,  
                                table = "(select * from table_name) a",
                                properties = connectionProperties)

Re: An example how to connect to SQL Server data using windows authentication

DBXC — Wed, 26 Apr 2023 14:52:03 GMT

Would someone provide example with IntegratedSecurity instead of user name and passworld?

Re: An example how to connect to SQL Server data using windows authentication

DBXC — Wed, 26 Apr 2023 14:52:33 GMT

*password*

Re: An example how to connect to SQL Server data using windows authentication

rdc14 — Mon, 09 Dec 2024 05:11:20 GMT

Thanks, this worked for me.