def register(request):
registered = False
global user
global contractid
if request.method == "POST":
user_form = VoterForm(data=request.POST)
profile_form = VoterinfoForm(data=request.POST)
if user_form.is_valid() and profile_form.is_valid():
user = user_form.save(commit=False)
usercopy = user
username = user_form.cleaned_data['username']
password = user_form.cleaned_data['password']
user.set_password(user.password)
user.save()
profile = profile_form.save(commit=False)
profile.user = user
profile.save()
registered = True
context = {
"user_form": user_form,
"profile_form": profile_form,
"registered": registered,
"user": user,
}
auth_context = AuthenticationContext(AUTHORITY)
token = auth_context.acquire_token_with_client_credentials(
RESOURCE, CLIENT_APP_Id, CLIENT_SECRET)
post_data = {
"workflowFunctionID":
73,
"workflowActionParameters": [{
"name":
"Description",
"value":
str(profile_form.cleaned_data['constituency']),
"workflowFunctionParameterId":
249
}, {
"name":
"Name",
"value":
user_form.cleaned_data['username'],
"workflowFunctionParameterId":
250
}, {
"name":
"DOB",
"value":
str(profile_form.cleaned_data['dob']),
"workflowFunctionParameterId":
251
}, {
"name":
"Aadhar",
"value":
str(profile_form.cleaned_data['aadhar']),
"workflowFunctionParameterId":
252
}, {
"name":
"VoterId",
"value":
str(profile_form.cleaned_data['voterid']),
"workflowFunctionParameterId":
253
}, {
"name":
"Pin",
"value":
str(profile_form.cleaned_data['pin']),
"workflowFunctionParameterId":
254
}]
}
endpoint = 'https://safevote-3wpvhp-api.azurewebsites.net/api/v2/contracts'
# endpoint = 'https://voteapp-iyby7e-api.azurewebsites.net/api/v2/applications/15'
url = endpoint
headers = {
'Content-Type': 'application/json',
'Authorization': 'Bearer ' + token['accessToken']
}
PARAMS = {
'workflowId': 16,
'contractCodeId': 16,
'connectionId': 1
}
response = requests.post(url,
headers=headers,
params=PARAMS,
data=json.dumps(post_data))
if response.status_code == 200: # SUCCESS
contractid = response.json()
user1 = VoterInfo.objects.get(user=usercopy)
user1.contractid = contractid
user1.save()
else:
context = {
"user_form": user_form,
"profile_form": profile_form,
"registered": registered,
}
return render(request, 'register.html', context)
else:
user_form = VoterForm()
profile_form = VoterinfoForm()
context = {
"user_form": user_form,
"profile_form": profile_form,
"registered": registered,
}
return render(request, 'register.html', context)
endpoint = "http://a7b13c94-3a5f-4a67-8efc-1262bec99d4f.southeastasia.azurecontainer.io/score"
headers = { 'Content-Type':'application/json' }
predictions = requests.post(endpoint, input_json , headers = headers)
predicted_classes = predictions.json()
requests.
response = requests.post(,
headers=aad_token,
json={"ExperimentName": "My_Pipeline",
"ParameterAssignments": {"pipeline_arg": 20}})
from azureml.core.authentication import ServicePrincipalAuthentication
sp = ServicePrincipalAuthentication(tenant_id="e95e330a-6410-4dd1-a705-575cba48e853", # tenantID
service_principal_id="306d65da-1788-4088-939e-b1c40bea9c0c", # clientId
service_principal_password="******")
from adal import AuthenticationContext
client_id = "306d65da-1788-4088-939e-b1c40bea9c0c"
client_secret = "RzHm3X-Xv_Ubc~q-R89rdLZHQGK_VH.1v7"
resource_url = "https://login.microsoftonline.com"
tenant_id = "e95e330a-6410-4dd1-a705-575cba48e853"
authority = "{}/{}".format(resource_url, tenant_id)
auth_context = AuthenticationContext(authority)
token_response = auth_context.acquire_token_with_client_credentials("https://management.azure.com/", client_id, client_secret)
print(token_response)
import requests
from adal import AuthenticationContext
auth_context = AuthenticationContext(
"https://login.microsoftonline.com/<tenant_id>/")
SESSION = requests.Session()
# client id and secret from application (has to be assigned to the subscription)
token_response = auth_context.acquire_token_with_client_credentials(
client_id="<client_id>",
client_secret="<client_secret>",
resource="https://management.azure.com/",
)
# add bearer token to the requests session
SESSION.headers.update(
{"Authorization": "Bearer " + token_response["accessToken"]})
# endpoints for policies
# definition
sub_policy_endpoint = "https://management.azure.com/subscriptions/<subscription_id>/providers/Microsoft.Authorization/policyDefinitions?api-version=2019-09-01"
# assignment
mgmt_policy_endpoint = "https://management.azure.com/providers/Microsoft.Management/managementgroups/mgmt-test/providers/Microsoft.Authorization/policyAssignments?%24filter=atScope()&api-version=2019-09-01"
# summarize
summarize_endpoint = "https://management.azure.com/subscriptions/<subscription_id>/providers/Microsoft.PolicyInsights/policyStates/latest/summarize?api-version=2018-04-04"
# trigger policy
trigger_endpoint = "https://management.azure.com/subscriptions/<subscription_id>/providers/Microsoft.PolicyInsights/policyStates/latest/triggerEvaluation?api-version=2018-07-01-preview"
def vote(request):
curruser = VoterInfo.objects.get(user=request.user)
cands = CandidateInfo.objects.all()
haserror = False
if curruser.hasVoted == False:
verified = False
auth_context = AuthenticationContext(AUTHORITY)
token = auth_context.acquire_token_with_client_credentials(
RESOURCE, CLIENT_APP_Id, CLIENT_SECRET)
endpoint = 'https://safevote-3wpvhp-api.azurewebsites.net/api/v2/' + str(
curruser.contractid) + '/actions'
url = endpoint
headers = {'Authorization': 'Bearer ' + token['accessToken']}
response = requests.get(url, headers=headers)
# print(token['accessToken'])
# print(response.text)
# data=response.json()
# print(data)
if True:
verified = True
curruser.is_Verified = True
if request.method == 'POST':
candidateid = request.POST.get('candidateid')
print(candidateid)
pin = request.POST.get('pin')
print(pin)
post_data = {
"workflowFunctionID":
77,
"workflowActionParameters": [{
"name":
"Candidate",
"value":
str(candidateid),
"workflowFunctionParameterId":
247
}, {
"name":
"PIN",
"value":
str(pin),
"workflowFunctionParameterId":
248
}]
}
endpoint = 'https://safevote-3wpvhp-api.azurewebsites.net/api/v2/contracts/' + str(
curruser.contractid) + '/actions'
url = endpoint
headers = {
'Content-Type': 'application/json',
'Authorization': 'Bearer ' + token['accessToken']
}
response = requests.post(url,
headers=headers,
data=json.dumps(post_data))
if response.status_code == 200:
curruser.hasVoted = True # SUCCESS
curruser.save()
currcand = CandidateInfo.objects.get(name=candidateid)
currcand.vote_count += 1
currcand.save()
return render(request, 'about.html', {
'cands': cands,
'curruser': curruser
})
#vote count display page redirection
else:
print(response.status_code)
print(curruser.contractid)
haserror = True
return render(
request, 'vote.html', {
'verified': verified,
'haserror': haserror,
'cands': cands,
'curruser': curruser
})
else:
return render(
request, 'vote.html', {
'verified': verified,
'haserror': haserror,
'cands': cands,
'curruser': curruser
}) #redirect to voting page
else:
verified = False
return render(
request, 'vote.html', {
'verified': verified,
'haserror': haserror,
'cands': cands,
'curruser': curruser
}) #redirect to voting page
else:
return render(request, 'about.html', {
'cands': cands,
'curruser': curruser
}) #vote count display page redirection
class _MyAadHelper(object):
def __init__(self,
kusto_cluster,
client_id=None,
client_secret=None,
username=None,
password=None,
authority=None):
self.adal_context = AuthenticationContext(
'https://login.windows.net/{0}'.format(authority
or 'microsoft.com'))
self.kusto_cluster = kusto_cluster
self.client_id = client_id or "db662dc1-0cfe-4e1c-a843-19a68e65be58"
self.client_secret = client_secret
self.username = username
self.password = password
def acquire_token(self):
""" A method to acquire tokens from AAD. """
# print("my_aad_helper_acquire_token")
token_response = self.adal_context.acquire_token(
self.kusto_cluster, self.username, self.client_id)
if token_response is not None:
expiration_date = dateutil.parser.parse(
token_response['expiresOn'])
if expiration_date > datetime.utcnow() + timedelta(minutes=5):
return token_response['accessToken']
if self.client_secret is not None and self.client_id is not None:
token_response = self.adal_context.acquire_token_with_client_credentials(
self.kusto_cluster, self.client_id, self.client_secret)
elif self.username is not None and self.password is not None:
token_response = self.adal_context.acquire_token_with_username_password(
self.kusto_cluster, self.username, self.password,
self.client_id)
else:
code = self.adal_context.acquire_user_code(self.kusto_cluster,
self.client_id)
url = code['verification_url']
device_code = code["user_code"].strip()
html_str = """<!DOCTYPE html>
<html><body>
<!-- h1 id="user_code_p"><b>""" + device_code + """</b><br></h1-->
<input id="kqlMagicCodeAuthInput" type="text" readonly style="font-weight: bold; border: none;" size = '""" + str(
len(device_code)) + """' value='""" + device_code + """'>
<button id='kqlMagicCodeAuth_button', onclick="this.style.visibility='hidden';kqlMagicCodeAuthFunction()">Copy code to clipboard and authenticate</button>
<script>
var kqlMagicUserCodeAuthWindow = null
function kqlMagicCodeAuthFunction() {
/* Get the text field */
var copyText = document.getElementById("kqlMagicCodeAuthInput");
/* Select the text field */
copyText.select();
/* Copy the text inside the text field */
document.execCommand("copy");
/* Alert the copied text */
// alert("Copied the text: " + copyText.value);
var w = screen.width / 2;
var h = screen.height / 2;
params = 'width='+w+',height='+h
kqlMagicUserCodeAuthWindow = window.open('""" + url + """', 'kqlMagicUserCodeAuthWindow', params);
}
</script>
</body></html>"""
Display.show(html_str)
# webbrowser.open(code['verification_url'])
try:
token_response = self.adal_context.acquire_token_with_device_code(
self.kusto_cluster, code, self.client_id)
finally:
html_str = """<!DOCTYPE html>
<html><body><script>
// close authentication window
if (kqlMagicUserCodeAuthWindow && kqlMagicUserCodeAuthWindow.opener != null && !kqlMagicUserCodeAuthWindow.closed) {
kqlMagicUserCodeAuthWindow.close()
}
// clear output cell
Jupyter.notebook.clear_output(Jupyter.notebook.get_selected_index())
// move to next cell
</script></body></html>"""
Display.show(html_str)
return token_response['accessToken']
from adal import AuthenticationContext
import requests
from pprint import pprint
import json
AUTHORITY = 'https://login.microsoftonline.com/chiragvmj2012gmail.onmicrosoft.com'
WORKBENCH_API_URL = 'https://safevote-3wpvhp.azurewebsites.net'
RESOURCE = '9569b296-589d-488b-a582-246d957827de'
CLIENT_APP_Id = 'b4d34ae7-b24d-4aab-87a5-a72c23d60dec'
CLIENT_SECRET = '.M-m0UajsaeD*uCc6@WlLigK5oVMPbj8'
auth_context = AuthenticationContext(AUTHORITY)
if __name__ == '__main__':
try:
# Acquiring the token
token = auth_context.acquire_token_with_client_credentials(
RESOURCE, CLIENT_APP_Id, CLIENT_SECRET)
# print(token['accessToken'])
endpoint = 'https://safevote-3wpvhp-api.azurewebsites.net/api/v2/36/actions'
url = endpoint
headers = {'Authorization': 'Bearer ' + token['accessToken']}
response = requests.get(url, headers=headers)
mydata = json.loads(response.text)
print(mydata["nextLink"])
except Exception as error:
print(error)
class _AadHelper:
authentication_method = None
auth_context = None
msi_auth_context = None
username = None
kusto_uri = None
authority_uri = None
client_id = None
password = None
thumbprint = None
certificate = None
msi_params = None
token_provider = None
code_callback = None
def __init__(self,
kcsb: "KustoConnectionStringBuilder",
cb: Callable = None):
self.kusto_uri = "{0.scheme}://{0.hostname}".format(
urlparse(kcsb.data_source))
self.username = None
if all([kcsb.aad_user_id, kcsb.password]):
self.authentication_method = AuthenticationMethod.aad_username_password
self.client_id = "db662dc1-0cfe-4e1c-a843-19a68e65be58"
self.username = kcsb.aad_user_id
self.password = kcsb.password
elif all([kcsb.application_client_id, kcsb.application_key]):
self.authentication_method = AuthenticationMethod.aad_application_key
self.client_id = kcsb.application_client_id
self.client_secret = kcsb.application_key
elif all([
kcsb.application_client_id, kcsb.application_certificate,
kcsb.application_certificate_thumbprint
]):
self.authentication_method = AuthenticationMethod.aad_application_certificate
self.client_id = kcsb.application_client_id
self.certificate = kcsb.application_certificate
self.thumbprint = kcsb.application_certificate_thumbprint
elif kcsb.msi_authentication:
self.authentication_method = AuthenticationMethod.aad_msi
self.msi_params = kcsb.msi_parameters
return
elif any([kcsb.user_token, kcsb.application_token]):
self.token = kcsb.user_token or kcsb.application_token
self.authentication_method = AuthenticationMethod.aad_token
return
elif kcsb.az_cli:
self.authentication_method = AuthenticationMethod.az_cli_profile
return
elif kcsb.token_provider:
self.authentication_method = AuthenticationMethod.token_provider
self.token_provider = kcsb.token_provider
else:
self.authentication_method = AuthenticationMethod.aad_device_login
self.client_id = "db662dc1-0cfe-4e1c-a843-19a68e65be58"
self.code_callback = cb
authority = kcsb.authority_id or "common"
aad_authority_uri = os.environ.get("AadAuthorityUri", CLOUD_LOGIN_URL)
self.authority_uri = aad_authority_uri + authority if aad_authority_uri.endswith(
"/") else aad_authority_uri + "/" + authority
def acquire_authorization_header(self):
"""Acquire tokens from AAD."""
try:
return self._acquire_authorization_header()
except (AdalError, KustoClientError) as error:
if self.authentication_method is AuthenticationMethod.aad_username_password:
kwargs = {
"username": self.username,
"client_id": self.client_id
}
elif self.authentication_method is AuthenticationMethod.aad_application_key:
kwargs = {"client_id": self.client_id}
elif self.authentication_method is AuthenticationMethod.aad_device_login:
kwargs = {"client_id": self.client_id}
elif self.authentication_method is AuthenticationMethod.aad_application_certificate:
kwargs = {
"client_id": self.client_id,
"thumbprint": self.thumbprint
}
elif self.authentication_method is AuthenticationMethod.aad_msi:
kwargs = self.msi_params
elif self.authentication_method is AuthenticationMethod.token_provider:
kwargs = {}
else:
raise error
kwargs["resource"] = self.kusto_uri
if self.authentication_method is AuthenticationMethod.aad_msi:
kwargs["authority"] = AuthenticationMethod.aad_msi.value
elif self.authentication_method is AuthenticationMethod.token_provider:
kwargs["authority"] = AuthenticationMethod.token_provider.value
elif self.auth_context is not None:
kwargs["authority"] = self.auth_context.authority.url
raise KustoAuthenticationError(self.authentication_method.value,
error, **kwargs)
def _acquire_authorization_header(self) -> str:
# Token was provided by caller
if self.authentication_method is AuthenticationMethod.aad_token:
return _get_header("Bearer", self.token)
if self.authentication_method is AuthenticationMethod.token_provider:
caller_token = self.token_provider()
if not isinstance(caller_token, str):
raise KustoClientError(
"Token provider returned something that is not a string ["
+ str(type(caller_token)) + "]")
return _get_header("Bearer", caller_token)
# Obtain token from MSI endpoint
if self.authentication_method == AuthenticationMethod.aad_msi:
token = self.get_token_from_msi()
return _get_header_from_dict(token)
refresh_token = None
if self.authentication_method == AuthenticationMethod.az_cli_profile:
stored_token = _get_azure_cli_auth_token()
if (TokenResponseFields.REFRESH_TOKEN in stored_token
and TokenResponseFields._CLIENT_ID in stored_token
and TokenResponseFields._AUTHORITY in stored_token):
self.client_id = stored_token[TokenResponseFields._CLIENT_ID]
self.username = stored_token[TokenResponseFields.USER_ID]
self.authority_uri = stored_token[
TokenResponseFields._AUTHORITY]
refresh_token = stored_token[TokenResponseFields.REFRESH_TOKEN]
if self.auth_context is None:
self.auth_context = AuthenticationContext(self.authority_uri)
if refresh_token is not None:
token = self.auth_context.acquire_token_with_refresh_token(
refresh_token, self.client_id, self.kusto_uri)
else:
token = self.auth_context.acquire_token(self.kusto_uri,
self.username,
self.client_id)
if token is not None:
expiration_date = dateutil.parser.parse(
token[TokenResponseFields.EXPIRES_ON])
if expiration_date > datetime.now() + timedelta(minutes=1):
return _get_header_from_dict(token)
if TokenResponseFields.REFRESH_TOKEN in token:
token = self.auth_context.acquire_token_with_refresh_token(
token[TokenResponseFields.REFRESH_TOKEN], self.client_id,
self.kusto_uri)
if token is not None:
return _get_header_from_dict(token)
# obtain token from AAD
if self.authentication_method is AuthenticationMethod.aad_username_password:
token = self.auth_context.acquire_token_with_username_password(
self.kusto_uri, self.username, self.password, self.client_id)
elif self.authentication_method is AuthenticationMethod.aad_application_key:
token = self.auth_context.acquire_token_with_client_credentials(
self.kusto_uri, self.client_id, self.client_secret)
elif self.authentication_method is AuthenticationMethod.aad_device_login:
code = self.auth_context.acquire_user_code(self.kusto_uri,
self.client_id)
if self.code_callback is not None:
if asyncio.iscoroutinefunction(self.code_callback):
asyncio.run(self.code_callback(code['user_code']))
else:
self.code_callback(code['user_code'])
print(code[OAuth2DeviceCodeResponseParameters.MESSAGE])
token = self.auth_context.acquire_token_with_device_code(
self.kusto_uri, code, self.client_id)
elif self.authentication_method is AuthenticationMethod.aad_application_certificate:
token = self.auth_context.acquire_token_with_client_certificate(
self.kusto_uri, self.client_id, self.certificate,
self.thumbprint)
else:
raise KustoClientError(
"Please choose authentication method from azure.kusto.data.security.AuthenticationMethod"
)
return _get_header_from_dict(token)
def get_token_from_msi(self) -> dict:
try:
if self.msi_auth_context is None:
# Create the MSI Authentication object, the first token is acquired implicitly
self.msi_auth_context = MSIAuthentication(**self.msi_params)
else:
# Acquire a fresh token
self.msi_auth_context.set_token()
except Exception as e:
raise KustoClientError("Failed to obtain MSI context for [" +
str(self.msi_params) + "]\n" + str(e))
return self.msi_auth_context.token
class _AadHelper(object):
def __init__(self, kcsb):
authority = kcsb.authority_id or "common"
self._kusto_cluster = "{0.scheme}://{0.hostname}".format(
urlparse(kcsb.data_source))
self._adal_context = AuthenticationContext(
"https://login.microsoftonline.com/{0}".format(authority))
self._username = None
if all([kcsb.aad_user_id, kcsb.password]):
self._authentication_method = AuthenticationMethod.aad_username_password
self._client_id = "db662dc1-0cfe-4e1c-a843-19a68e65be58"
self._username = kcsb.aad_user_id
self._password = kcsb.password
elif all([kcsb.application_client_id, kcsb.application_key]):
self._authentication_method = AuthenticationMethod.aad_application_key
self._client_id = kcsb.application_client_id
self._client_secret = kcsb.application_key
elif all([
kcsb.application_client_id, kcsb.application_certificate,
kcsb.application_certificate_thumbprint
]):
self._authentication_method = AuthenticationMethod.aad_application_certificate
self._client_id = kcsb.application_client_id
self._certificate = kcsb.application_certificate
self._thumbprint = kcsb.application_certificate_thumbprint
else:
self._authentication_method = AuthenticationMethod.aad_device_login
self._client_id = "db662dc1-0cfe-4e1c-a843-19a68e65be58"
def acquire_authorization_header(self):
"""Acquire tokens from AAD."""
try:
return self._acquire_authorization_header()
except AdalError as error:
if self._authentication_method is AuthenticationMethod.aad_username_password:
kwargs = {
"username": self._username,
"client_id": self._client_id
}
elif self._authentication_method is AuthenticationMethod.aad_application_key:
kwargs = {"client_id": self._client_id}
elif self._authentication_method is AuthenticationMethod.aad_device_login:
kwargs = {"client_id": self._client_id}
elif self._authentication_method is AuthenticationMethod.aad_application_certificate:
kwargs = {
"client_id": self._client_id,
"thumbprint": self._thumbprint
}
else:
raise error
kwargs["resource"] = self._kusto_cluster
kwargs["authority"] = self._adal_context.authority.url
raise KustoAuthenticationError(self._authentication_method.value,
error, **kwargs)
def _acquire_authorization_header(self):
token = self._adal_context.acquire_token(self._kusto_cluster,
self._username,
self._client_id)
if token is not None:
expiration_date = dateutil.parser.parse(
token[TokenResponseFields.EXPIRES_ON])
if expiration_date > datetime.now() + timedelta(minutes=1):
return _get_header(token)
if TokenResponseFields.REFRESH_TOKEN in token:
token = self._adal_context.acquire_token_with_refresh_token(
token[TokenResponseFields.REFRESH_TOKEN], self._client_id,
self._kusto_cluster)
if token is not None:
return _get_header(token)
if self._authentication_method is AuthenticationMethod.aad_username_password:
token = self._adal_context.acquire_token_with_username_password(
self._kusto_cluster, self._username, self._password,
self._client_id)
elif self._authentication_method is AuthenticationMethod.aad_application_key:
token = self._adal_context.acquire_token_with_client_credentials(
self._kusto_cluster, self._client_id, self._client_secret)
elif self._authentication_method is AuthenticationMethod.aad_device_login:
code = self._adal_context.acquire_user_code(
self._kusto_cluster, self._client_id)
print(code[OAuth2DeviceCodeResponseParameters.MESSAGE])
webbrowser.open(
code[OAuth2DeviceCodeResponseParameters.VERIFICATION_URL])
token = self._adal_context.acquire_token_with_device_code(
self._kusto_cluster, code, self._client_id)
elif self._authentication_method is AuthenticationMethod.aad_application_certificate:
token = self._adal_context.acquire_token_with_client_certificate(
self._kusto_cluster, self._client_id, self._certificate,
self._thumbprint)
else:
raise KustoClientError(
"Please choose authentication method from azure.kusto.data.security.AuthenticationMethod"
)
return _get_header(token)
def __call__(self, request):
context = AuthenticationContext(self.authority)
response = context.acquire_token_with_client_credentials(self.resource_url, self.client_id, self.client_secret)
request.headers['Authorization'] = 'Bearer %s' % response.get('accessToken')
return request
def cleanup_database(spark_args):
client_secret = SERVICE_PRINCIPAL_SECRET
database = args.jdbc_database
jdbcHost = args.jdbc_host
jdbcPort = args.jdbc_port
jdbc_username_key_name = spark_args.jdbc_username_key_name
jdbc_password_key_name = spark_args.jdbc_password_key_name
use_msi_azure_sql_auth = spark_args.use_msi_azure_sql_auth
application_id = spark_args.application_id
directory_id = spark_args.directory_id
connectionProperties = {
'databaseName': database,
'url': 'watercooler-sql-wt.database.windows.net',
'hostNameInCertificate': '*.database.windows.net',
'encrypt': 'true',
'Driver': 'com.microsoft.sqlserver.jdbc.SQLServerDriver',
'ServerCertificate': 'false',
'trustServerCertificate': 'false',
'loginTimeout': '30'
}
if use_msi_azure_sql_auth:
sts_url = "https://login.microsoftonline.com/" + directory_id
auth_context = AuthenticationContext(sts_url)
token_obj = auth_context.acquire_token_with_client_credentials(
"https://database.windows.net/", application_id, client_secret)
access_token = token_obj['accessToken']
connectionProperties['accessToken'] = access_token
else:
service_principal_credential = ClientSecretCredential(
tenant_id=spark_args.directory_id,
client_id=spark_args.application_id,
client_secret=SERVICE_PRINCIPAL_SECRET)
secret_client = SecretClient(vault_url=spark_args.key_vault_url,
credential=service_principal_credential)
connectionProperties["user"] = secret_client.get_secret(
name=jdbc_username_key_name).value
connectionProperties["password"] = secret_client.get_secret(
name=jdbc_password_key_name).value
connection = jaydebeapi.connect(
"com.microsoft.sqlserver.jdbc.SQLServerDriver",
f"jdbc:sqlserver://{jdbcHost}:{jdbcPort};databaseName={database};",
connectionProperties)
cursor = connection.cursor()
truncate_groups_per_day = "truncate table groups_per_day;"
truncate_groups_per_week = "truncate table groups_per_week;"
truncate_members_group_personal_meetings = "truncate table members_group_personal_meetings;"
members_to_group_participation = "truncate table members_to_group_participation;"
cursor.execute(truncate_groups_per_day)
logger.info("Truncated groups_per_day table.")
cursor.execute(truncate_groups_per_week)
logger.info("Truncated groups_per_week table.")
cursor.execute(truncate_members_group_personal_meetings)
logger.info("Truncated members_group_personal_meetings table.")
cursor.execute(members_to_group_participation)
logger.info("Truncated members_to_group_participation table.")
cursor.close()
connection.close()
def export_data_to_azure_sql(spark_args):
application_id = spark_args.application_id
directory_id = spark_args.directory_id
adb_secret_scope = spark_args.adb_secret_scope_name
adb_sp_client_key_secret_name = spark_args.adb_sp_client_key_secret_name
employee_profile = spark_args.employee_profile_file_name
groups_per_day_file = spark_args.groups_per_day_file_name
groups_per_week = spark_args.groups_per_weeks_file_name
members_group_personal_meetings = spark_args.members_group_personal_meetings_file_name
members_to_group_participation = spark_args.members_to_group_participation_file_name
database = spark_args.jdbc_database
jdbc_host = spark_args.jdbc_host
jdbc_port = spark_args.jdbc_port
use_msi_azure_sql_auth = spark_args.use_msi_azure_sql_auth
key_vault_url = spark_args.key_vault_url
jdbc_username_key_name = spark_args.jdbc_username_key_name
jdbc_password_key_name = spark_args.jdbc_password_key_name
jdbc_username = spark_args.jdbc_user # this parameter should be used only for running the application locally
jdbc_password = spark_args.jdbc_password # this parameter should be used only for running the application locally
export_batch_size = int(spark_args.export_batch_size)
base_folder = spark_args.csv_input_data_path
full_base_path = retrieve_latest_run(base_folder)
logger.info(f"*******#####Result {full_base_path}")
employee_profile_file = os.path.join(full_base_path, employee_profile)
groups_per_day_file = os.path.join(full_base_path, groups_per_day_file)
groups_per_week_file = os.path.join(full_base_path, groups_per_week)
members_group_personal_meetings_file = os.path.join(
full_base_path, members_group_personal_meetings)
members_to_group_participation_file = os.path.join(
full_base_path, members_to_group_participation)
client_secret = SERVICE_PRINCIPAL_SECRET if SERVICE_PRINCIPAL_SECRET is not None else \
dbutils.secrets.get(scope=adb_secret_scope, key=adb_sp_client_key_secret_name)
if application_id is None:
ValueError("Missing application_id parameter!")
if directory_id is None:
ValueError("Missing directory_id parameter!")
if adb_secret_scope is None:
ValueError("Missing adb_secret_scope_name parameter!")
if adb_sp_client_key_secret_name is None:
ValueError("Missing adb_sp_client_key_secret_name parameter!")
if database is None:
ValueError("Missing database parameter!")
if jdbc_host is None:
ValueError("Missing jdbc_host parameter!")
if jdbc_port is None:
ValueError("Missing jdbc_port parameter!")
if use_msi_azure_sql_auth is None:
ValueError("Missing use_msi_azure_sql_auth parameter!")
if key_vault_url is None:
ValueError("Missing key_vault_url parameter!")
if jdbc_username_key_name is None:
ValueError("Missing jdbc_username_key_name parameter!")
if jdbc_password_key_name is None:
ValueError("Missing jdbc_password_key_name parameter!")
if jdbc_username is None:
ValueError("Missing jdbc_username parameter!")
if jdbc_password is None:
ValueError("Missing jdbc_password parameter!")
if export_batch_size is None:
ValueError("Missing export_batch_size parameter!")
connectionProperties = {
'databaseName': database,
'url': 'watercooler-sql-wt.database.windows.net',
'hostNameInCertificate': '*.database.windows.net',
'encrypt': 'true',
'Driver': 'com.microsoft.sqlserver.jdbc.SQLServerDriver',
'ServerCertificate': 'false',
'trustServerCertificate': 'false',
'loginTimeout': '30'
}
if use_msi_azure_sql_auth:
sts_url = "https://login.microsoftonline.com/" + directory_id
auth_context = AuthenticationContext(sts_url)
token_obj = auth_context.acquire_token_with_client_credentials(
"https://database.windows.net/", application_id, client_secret)
access_token = token_obj['accessToken']
connectionProperties['accessToken'] = access_token
else:
service_principal_credential = ClientSecretCredential(
tenant_id=spark_args.directory_id,
client_id=spark_args.application_id,
client_secret=SERVICE_PRINCIPAL_SECRET)
secret_client = SecretClient(vault_url=spark_args.key_vault_url,
credential=service_principal_credential)
if jdbc_username_key_name and jdbc_password_key_name:
connectionProperties["user"] = secret_client.get_secret(
name=jdbc_username_key_name).value
connectionProperties["password"] = secret_client.get_secret(
name=jdbc_password_key_name).value
else:
connectionProperties["user"] = jdbc_username
connectionProperties["password"] = jdbc_password
connectionProperties["encrypt"] = "false"
connection = jaydebeapi.connect(
"com.microsoft.sqlserver.jdbc.SQLServerDriver",
f"jdbc:sqlserver://{jdbc_host}:{jdbc_port};databaseName={database};",
connectionProperties)
cursor = connection.cursor()
# logger.info(f"*******#####Processing {employee_profile_file}")
# write_employee_profile_to_az_sql(employee_profile_file, export_batch_size, cursor)
logger.info("*******#####Processing {groups_per_day_file}")
write_groups_per_day_to_az_sql(groups_per_day_file, export_batch_size,
cursor)
logger.info(f"*******#####Processing {groups_per_week_file}")
write_groups_per_week_to_az_sql(groups_per_week_file, export_batch_size,
cursor)
logger.info(
f"*******#####Processing {members_group_personal_meetings_file}")
write_members_group_personal_meetings_to_az_sql(
members_group_personal_meetings_file, export_batch_size, cursor)
logger.info(
f"*******#####Processing {members_to_group_participation_file}")
write_members_to_group_participation_to_az_sql(
members_to_group_participation_file, export_batch_size, cursor)
cursor.close()
connection.close()
class MicrosoftAppCredentials(Authentication):
"""
MicrosoftAppCredentials auth implementation and cache.
"""
schema = "Bearer"
trustedHostNames = {
"state.botframework.com": datetime.max,
"api.botframework.com": datetime.max,
"token.botframework.com": datetime.max,
"state.botframework.azure.us": datetime.max,
"api.botframework.azure.us": datetime.max,
"token.botframework.azure.us": datetime.max,
}
cache = {}
def __init__(
self,
app_id: str,
password: str,
channel_auth_tenant: str = None,
oauth_scope: str = None,
):
"""
Initializes a new instance of MicrosoftAppCredentials class
:param app_id: The Microsoft app ID.
:param app_password: The Microsoft app password.
:param channel_auth_tenant: Optional. The oauth token tenant.
"""
# The configuration property for the Microsoft app ID.
self.microsoft_app_id = app_id
# The configuration property for the Microsoft app Password.
self.microsoft_app_password = password
tenant = (channel_auth_tenant if channel_auth_tenant else
AuthenticationConstants.DEFAULT_CHANNEL_AUTH_TENANT)
self.oauth_endpoint = (
AuthenticationConstants.TO_CHANNEL_FROM_BOT_LOGIN_URL_PREFIX +
tenant)
self.oauth_scope = (
oauth_scope
or AuthenticationConstants.TO_BOT_FROM_CHANNEL_TOKEN_ISSUER)
self.token_cache_key = app_id + self.oauth_scope + "-cache" if app_id else None
self.authentication_context = AuthenticationContext(
self.oauth_endpoint)
# pylint: disable=arguments-differ
def signed_session(self,
session: requests.Session = None) -> requests.Session:
"""
Gets the signed session.
:returns: Signed requests.Session object
"""
if not session:
session = requests.Session()
# If there is no microsoft_app_id and no self.microsoft_app_password, then there shouldn't
# be an "Authorization" header on the outgoing activity.
if not self.microsoft_app_id and not self.microsoft_app_password:
session.headers.pop("Authorization", None)
elif not session.headers.get("Authorization"):
auth_token = self.get_access_token()
header = "{} {}".format("Bearer", auth_token)
session.headers["Authorization"] = header
return session
def get_access_token(self, force_refresh: bool = False) -> str:
"""
Gets an OAuth access token.
:param force_refresh: True to force a refresh of the token; or false to get
a cached token if it exists.
:returns: Access token string
"""
if self.microsoft_app_id and self.microsoft_app_password:
if not force_refresh:
# check the global cache for the token. If we have it, and it's valid, we're done.
oauth_token = MicrosoftAppCredentials.cache.get(
self.token_cache_key, None)
if oauth_token is not None:
# we have the token. Is it valid?
if oauth_token.expiration_time > datetime.now():
return oauth_token.access_token
# We need to refresh the token, because:
# 1. The user requested it via the force_refresh parameter
# 2. We have it, but it's expired
# 3. We don't have it in the cache.
oauth_token = self.refresh_token()
MicrosoftAppCredentials.cache.setdefault(self.token_cache_key,
oauth_token)
return oauth_token.access_token
return ""
def refresh_token(self) -> _OAuthResponse:
"""
returns: _OAuthResponse
"""
token = self.authentication_context.acquire_token_with_client_credentials(
self.oauth_scope, self.microsoft_app_id,
self.microsoft_app_password)
oauth_response = _OAuthResponse.from_json(token)
oauth_response.expiration_time = datetime.now() + timedelta(
seconds=(int(oauth_response.expires_in) - 300))
return oauth_response
@staticmethod
def trust_service_url(service_url: str, expiration=None):
"""
Checks if the service url is for a trusted host or not.
:param service_url: The service url.
:param expiration: The expiration time after which this service url is not trusted anymore.
:returns: True if the host of the service url is trusted; False otherwise.
"""
if expiration is None:
expiration = datetime.now() + timedelta(days=1)
host = urlparse(service_url).hostname
if host is not None:
MicrosoftAppCredentials.trustedHostNames[host] = expiration
@staticmethod
def is_trusted_service(service_url: str) -> bool:
"""
Checks if the service url is for a trusted host or not.
:param service_url: The service url.
:returns: True if the host of the service url is trusted; False otherwise.
"""
host = urlparse(service_url).hostname
if host is not None:
return MicrosoftAppCredentials._is_trusted_url(host)
return False
@staticmethod
def _is_trusted_url(host: str) -> bool:
expiration = MicrosoftAppCredentials.trustedHostNames.get(
host, datetime.min)
return expiration > (datetime.now() - timedelta(minutes=5))
import requests
import json
import pandas as pd
import urllib
from adal import AuthenticationContext
# Azure App which need to be granted with Dynamics 365 permission on Azure portal
clientAppId = [AppId]
clientAppKey = [AppKey]
# Request Azure access token
requestURL = 'https://[domain].dynamics.com/'
auth_context = AuthenticationContext(
"https://login.microsoftonline.com/[domain]")
token_response = auth_context.acquire_token_with_client_credentials(
requestURL, clientAppId, clientAppKey)
# Example of CRM API
crmwebapi = 'https://[domain].api.crm.dynamics.com/api/data/v9.0'
crmwebapiquery = '/contacts'
#################################################################
# 1. Direct query example
# https://docs.microsoft.com/en-us/dynamics365/customer-engagement/developer/webapi/query-data-web-api
# example: https://[domain].api.crm.dynamics.com/api/data/v9.0/contacts?$select=fullname,contactid&$top=10
# params = '$select=fullname,contactid&$top=10'
#################################################################
# 2. Fetch data via fetchXml as parameter
# fetchXml can be generated from advance search page on CRM site