<jupyter_start><jupyter_text>Using MsSQLSparkConnector with Integrated AD Auth

This sample shows how to use the MsSQLSparkConnector with integrated AD Auth when using use principal and keytab instead of username/password.  

 PreReq
-------
- SQL Server 2019 big data cluster is deployed with AD
- Have access to AD controller to create keytabs that we will use in this sample. 
- Download [AdultCensusIncome.csv]( https://amldockerdatasets.azureedge.net/AdultCensusIncome.csv ) to your local machine.  Upload this file to hdfs folder named *spark_data*. 
- The sample uses a SQL database  *spark_mssql_db* to create/update tables. Refer **data-virtualization/mssql_spark_connector_user_creation.ipynb** on steps to create this database anduser.
     
  Creating KeyTab file
The following section shows how to generate principal and keytab. This assumes you have a SS19 Big Data Cluster installed with Windows AD contoller for domain AZDATA.LOCAL. One of the users is <EMAIL> and the user is part of Domain Admin group.

  Create KeyTab file using ktpass
1. Login to the Windows AD controller with testusera1 credentials.
2. Open command prompt in Administrator mode.
3. Use ktpass to create a key tab. Refer [here](https://docs.microsoft.com/en-us/windows-server/administration/windows-commands/ktpass) for documentation on using ktpass. 

```sh
ktpass -out testusera1.keytab -mapUser <EMAIL> -pass  -mapOp set +DumpSalt -crypto AES256-SHA1 -ptype KRB5_NT_PRINCIPAL -princ <EMAIL>
```

Note that principal name in ktpass is case sensitive. The command above generates a keytab file named testusera1.keytab. Transfer this file to hdfs folder in Big Data Cluster. In this sample we transfer the file to /user/testusera1/testusera1.keytab

 Create KeyTab file using kinit

If you are on a linux machine kinit can be used as follows to create keytab. Note that you linux machine shoud be connected to the domain controler.

``` sh
ktutil
ktutil : add_entry -password -p <EMAIL> -k 1 -e arcfour-hmac-md5
Password for <EMAIL>usera1@myDomain:
ktutil : add_entry -password -p <EMAIL> -k 1 -e des-cbc-md4
ktutil : wkt testusera1.keytab 
```

``` sh
 Check if keytab generated properly. Any error implies that keytab is not generated right.
kinit -kt testusera1.keytab  <EMAIL>
```

Load Keytab to HDFS for use, you can do it from ADS, or use hadoop commandline inside a BDC container:

```sh
hadoop fs -mkdir -p /user/testusera1/
hadoop fs -copyFromLocal -f testusera1.keytab  /user/testusera1/testusera1.keytab
```



   Configure  Spark applicaion to point to the key tab file
Here we configure spark to use the keytab file once the keytab is created and uploaded to HDFS (/user/testusera1/testusera1.keytab). 
Note the usage of "spark.files" : "/user/testusera1/testusera1.keytab". As a result of this configuration Spark driver distributes the file to all executors. 

Run the cell below to start the spark application.<jupyter_code>%%configure -f
{"conf": {
    "spark.files" : "/user/testusera1/testusera1.keytab",
    "spark.executor.memory": "4g",
    "spark.driver.memory": "4g",
    "spark.executor.cores": 2,
    "spark.driver.cores": 1,
    "spark.executor.instances": 4
        }
}<jupyter_output><empty_output><jupyter_text>Read CSV into a data frame
In this step we read the CSV into a data frame. This dataframe would then be written to SQL table using MSSQL Spark Connector<jupyter_code>#spark = SparkSession.builder.getOrCreate()
sc.setLogLevel("INFO")

#Read a file and then write it to the SQL table
datafile = "/spark_data/AdultCensusIncome.csv"
df = spark.read.format('csv').options(header='true', inferSchema='true', ignoreLeadingWhiteSpace='true', ignoreTrailingWhiteSpace='true').load(datafile)
df.show(5)


#Process this data. Very simple data cleanup steps. Replacing "-" with "_" in column names
columns_new = [col.replace("-", "_") for col in df.columns]
df = df.toDF(*columns_new)
df.show(5)<jupyter_output>Starting Spark application<jupyter_text>(Part 1) Write and READ to/from SQL Table ( using Integrated Auth)
- Write dataframe to SQL table to Master instance
- Read SQL Table to Spark dataframe

In both scenarions here we use integrated auth with principal\keytab file rather than username\password of the user.<jupyter_code>#Write from Spark to SQL table using MSSQL Spark Connector
print("MSSQL Spark Connector write(overwrite) start ")

servername = "jdbc:sqlserver://mssql.azdata.local:31433"
dbname = "spark_mssql_db"
security_spec = ";integratedSecurity=true;authenticationScheme=JavaKerberos;"
url = servername + ";" + "databaseName=" + dbname + security_spec

dbtable = "AdultCensus_test"
principal = "<EMAIL>"
keytab = "/user/testusera1/testusera1.keytab" 

try:
  df.write \
    .format("com.microsoft.sqlserver.jdbc.spark") \
    .mode("overwrite") \
    .option("url", url) \
    .option("dbtable", dbtable) \
    .option("principal", principal) \
    .option("keytab", keytab) \
    .save()
except ValueError as error :
    print("MSSQL Spark Connector write(overwrite) failed", error)

print("MSSQL Connector write(overwrite) done  ")
#Read from SQL table using MSSQ Connector
print("MSSQL Spark Connector read start ")
jdbcDF = spark.read \
        .format("com.microsoft.sqlserver.jdbc.spark") \
        .option("url", url) \
        .option("dbtable", dbtable) \
        .option("url", url) \
        .option("dbtable", dbtable) \
        .option("principal", principal) \
        .option("keytab", keytab).load()

jdbcDF.show(5)

print("MSSQL Spark Connector read done")<jupyter_output><empty_output><jupyter_text>(PART 2) Write and READ to/from Data Pools ( using Integrated Auth)
- Write dataframe to SQL external table in Data Pools in Big Data Cluste
- Read SQL external Table to Spark dataframe


User creation as follows
```

```<jupyter_code>#Write from Spark to datapools using Spark Connector
print("MSSQL Spark Connector write(overwrite) start ")

servername = "jdbc:sqlserver://mssql.azdata.local:31433"
dbname = "spark_mssql_db"
security_spec = ";integratedSecurity=true;authenticationScheme=JavaKerberos;"
url = servername + ";" + "databaseName=" + dbname + security_spec

datapool_table = "AdultCensus_DataPoolTable"
principal = "<EMAIL>"
keytab = "/user/testuser/testusera1.keytab" 

datasource_name = "connector_ds"

try:
  df.write \
    .format("com.microsoft.sqlserver.jdbc.spark") \
    .mode("overwrite") \
    .option("url", url) \
    .option("dbtable", datapool_table) \
    .option("principal", principal) \
    .option("keytab", keytab) \
    .option("dataPoolDataSource",datasource_name) \
    .save()
except ValueError as error :
    print("MSSQL Spark Connector write(overwrite) failed", error)

print("MSSQL Connector write(overwrite) done  ")
#Read from SQL table using MSSQ Connector
print("MSSQL Spark Connector read data pool external table start ")
jdbcDF = spark.read \
        .format("com.microsoft.sqlserver.jdbc.spark") \
        .option("url", url) \
        .option("dbtable", datapool_table) \
        .option("url", url) \
        .option("dbtable", dbtable) \
        .option("principal", principal) \
        .option("keytab", keytab).load()

jdbcDF.show(5)

print("MSSQL Connector read from data pool external table succeeded")<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup