-
19 October 2020
- Cloud Computing
Azure SQL authentication with AD
When using Azure SQL instance it may be tempting to just create database user and use simple sql authentication – but that’s not always possible, especially when dealing with highly restricted data.
In such cases, we are forced to use AD authentication, and to make things even less friendly we often have to get a technical user for scheduled processing and such.
It’s not overly complicated, but the process might not be very straightforward. Visit our Data Engineering Consultancy to find solutions suited for your company
Solution
First we need to create app registration in Azure Active Directory. This will be our substitute of “technical user”. To do that, just go into Azure Active Directory in Azure Services:
Than choose App Registrations in left hand menu:
Click New Registration in upper part of the screen:
Fill in the name of the app, check Single Tenant, and finally click “Register” button.
When app is created we will need some of its properties for further setup:
We will also need a secret that will be used to authenticate our app. To create new secret, let’s go into our app, and choose “Certificates & Secrets”:
Next, click “New client secret” and after it is created, store it in safe place, key vault potentially.
Once we obtain above info, we may start setting up connection to our Azure SQL instance.
At this point we have several ways to do that. We might try to use our app, give it permissions for Azure SQL instances, create external user on SQL server, but this is often impossible due to administrative restrictions in Azure environment.
Let’s try more universal way, that is almost as simple as latter – generate access token, and use it for logging in.
To generate token we will use MSAL library for python (ADAL is soon to be left out).
We need to invoke instance of ConfidentialClientApplication object from msal library.
Class constructor needs some parameters:
client_id – this is our Service Principal ID (Application ID)
This block contains unexpected or invalid content.ResolveConvert to HTMLauthority – this is a URI consisting of a api link: https://login.microsoftonline.com/ and Tenant Id (Directory Id of our app)
client_credential – earlier created app client secret
Having all info gathered, we instantiate the app:
app = msal.ConfidentialClientApplication( client_id=ServicePrincipalId, authority=authority, client_credential=ServicePrincipalPwd,)
And use it’s acquire_token_silent method:
result = app.acquire_token_silent(scopes=[scope], account=None)This block contains unexpected or invalid content.ResolveConvert to HTMLwhere scope is: https://database.windows.net//.default (with double “//” – not a mistake)
Silent method might not work, in such case go for another one:
if not result: result = app.acquire_token_for_client(scope)
Than check if we received token in response, and save it:
if "access_token" in result: sqlAzureAccessToken = result["access_token"]
We are almost ready to connect with JDBC. Just make a URI:
jdbcUrl = "jdbc:sqlserver://ourserver.database.windows.net:1433;database=ourdatabase;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;authentication=ActiveDirectoryPassword"
and connection properties:
connectionProperties = { "accessToken" : sqlAzureAccessToken, "driver" : "com.microsoft.sqlserver.jdbc.SQLServerDriver", "hostNameInCertificate" : "*.database.windows.net", "encrypt" : "true", "ServerCertificate" : "false", "driver" : "com.microsoft.sqlserver.jdbc.SQLServerDriver"}
And we’re good to go:
jdbc_db = spark.read .format("com.microsoft.sqlserver.jdbc.spark") \ .option("url", "jdbc:sqlserver://ourserver.database.windows.net") \ .option("dbtable", "(SELECT * FROM mytablespace.mytable) a") \ .option("databaseName","ourdatabase")\ .option("accessToken", sqlAzureAccessToken) \ .option("encrypt", "true") \ .option("hostNameInCertificate", "*.database.windows.net") \ .load()Visit our blog for more in-depth Data Engineering articles:
