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

[gillzer84]

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.

[Junee]

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)

[DBXC]

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

[DBXC]

*password*

[rdc14]

Thanks, this worked for me.