Last active
August 23, 2022 20:21
-
-
Save SQLvariant/498b829cf59b40b3ab6a579702cef84d to your computer and use it in GitHub Desktop.
PowerShell commands for setting the AAD Auth of an Azure SQL instance to a Remote Group in a different tenant
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| { | |
| "metadata": { | |
| "kernelspec": { | |
| "name": ".net-powershell", | |
| "display_name": ".NET (PowerShell)", | |
| "language": "powershell" | |
| }, | |
| "language_info": { | |
| "name": "PowerShell", | |
| "version": "7.0", | |
| "file_extension": ".ps1", | |
| "pygments_lexer": "powershell" | |
| }, | |
| "extensions": { | |
| "azuredatastudio": { | |
| "version": 1, | |
| "views": [] | |
| } | |
| } | |
| }, | |
| "nbformat_minor": 2, | |
| "nbformat": 4, | |
| "cells": [ | |
| { | |
| "cell_type": "markdown", | |
| "source": [ | |
| "# Configure Azure SQL to Authenticate AAD Users via a Group in different tenant\n", | |
| "\n", | |
| "First, grab a bearer token from the Azure tenant you want to use. An easy way to obtain one is by using the Get-AzAccessToken cmdlet from the Az.Accounts PowerShell module." | |
| ], | |
| "metadata": { | |
| "azdata_cell_guid": "942fd229-a4e1-4dba-a02b-dbf7a1fd69ed" | |
| }, | |
| "attachments": {} | |
| }, | |
| { | |
| "cell_type": "code", | |
| "source": [ | |
| "$token = (Get-AzAccessToken).Token\r\n", | |
| "$SecureToken = ConvertTo-SecureString -AsPlainText $token" | |
| ], | |
| "metadata": { | |
| "azdata_cell_guid": "c523c015-cc60-40a4-8afd-a6bb218d9134" | |
| }, | |
| "outputs": [], | |
| "execution_count": null | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "source": [ | |
| "Fill in the `$variables` below with the corresponding information of the Azure SQL instance you want to modify.\n", | |
| "\n", | |
| "- $SubscriptionId = ''\n", | |
| "- $ResourceGroupName = ''\n", | |
| "- $SqlServerName = ''" | |
| ], | |
| "metadata": { | |
| "azdata_cell_guid": "9402dbc7-9f09-4b45-82e1-1036dc801b26" | |
| }, | |
| "attachments": {} | |
| }, | |
| { | |
| "cell_type": "code", | |
| "source": [ | |
| "$SubscriptionId = ''\r\n", | |
| "$ResourceGroupName = ''\r\n", | |
| "$SqlServerName = ''\r\n", | |
| "\r\n", | |
| "# For Azure SQL use this URL path\r\n", | |
| "$ResourceUrl = \"https://management.azure.com/subscriptions/$SubscriptionId/resourceGroups/$ResourceGroupName/providers/Microsoft.Sql/servers/$SqlServerName/administrators/ActiveDirectory?api-version=2021-02-01-preview\"" | |
| ], | |
| "metadata": { | |
| "azdata_cell_guid": "cdf52a6c-54f7-49d0-a687-9eaa52d65ffc" | |
| }, | |
| "outputs": [], | |
| "execution_count": null | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "source": [ | |
| "<span style=\"color: rgba(0, 0, 0, 0.9); font-family: "Segoe UI VSS (Regular)", "Segoe UI", -apple-system, BlinkMacSystemFont, Roboto, "Helvetica Neue", Helvetica, Ubuntu, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji", "Segoe UI Symbol"; font-size: 15px; background-color: rgb(255, 255, 255);\">If you are working with Azure Synapse, you need to use a different URL. Make sure to skip this next code block if you're working with Azure SQL.</span>" | |
| ], | |
| "metadata": { | |
| "azdata_cell_guid": "307c4f43-8897-422c-a3da-7db80258a378" | |
| }, | |
| "attachments": {} | |
| }, | |
| { | |
| "cell_type": "code", | |
| "source": [ | |
| "$SubscriptionId = ''\r\n", | |
| "$ResourceGroupName = ''\r\n", | |
| "$WorkspaceName = ''\r\n", | |
| "# For Synapse use this URL path, otherwise skip the next line.\r\n", | |
| "$ResourceUrl = \"https://management.azure.com/subscriptions/$SubscriptionId/resourceGroups/$ResourceGroupName/providers/Microsoft.Synapse/workspaces/$WorkspaceName/administrators/activeDirectory?api-version=2021-06-01\"" | |
| ], | |
| "metadata": { | |
| "azdata_cell_guid": "f90c365c-ce51-4272-b9f8-8f5241862eac" | |
| }, | |
| "outputs": [], | |
| "execution_count": null | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "source": [ | |
| "Test your token by running a Get first." | |
| ], | |
| "metadata": { | |
| "azdata_cell_guid": "79f1576e-0d25-4a94-8bd6-a70e2f257049" | |
| }, | |
| "attachments": {} | |
| }, | |
| { | |
| "cell_type": "code", | |
| "source": [ | |
| "Invoke-RestMethod -Method GET -Uri $ResourceUrl -Authentication Bearer -Token $SecureToken" | |
| ], | |
| "metadata": { | |
| "azdata_cell_guid": "8b2d54bb-8798-471a-9522-5a4dc35ac06f" | |
| }, | |
| "outputs": [], | |
| "execution_count": null | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "source": [ | |
| "<span style=\"color: rgba(0, 0, 0, 0.9); font-family: "Segoe UI VSS (Regular)", "Segoe UI", -apple-system, BlinkMacSystemFont, Roboto, "Helvetica Neue", Helvetica, Ubuntu, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji", "Segoe UI Symbol"; font-size: 15px; background-color: rgb(255, 255, 255);\">Complete the missing information in the </span> `$body` <span style=\"color: rgba(0, 0, 0, 0.9); font-family: "Segoe UI VSS (Regular)", "Segoe UI", -apple-system, BlinkMacSystemFont, Roboto, "Helvetica Neue", Helvetica, Ubuntu, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji", "Segoe UI Symbol"; font-size: 15px; background-color: rgb(255, 255, 255);\"> variable. Make sure to retrieve the sid from the </span> `ObjectID` <span style=\"color: rgba(0, 0, 0, 0.9); font-family: "Segoe UI VSS (Regular)", "Segoe UI", -apple-system, BlinkMacSystemFont, Roboto, "Helvetica Neue", Helvetica, Ubuntu, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji", "Segoe UI Symbol"; font-size: 15px; background-color: rgb(255, 255, 255);\"> field in the AAD of the </span> _source_ <span style=\"color: rgba(0, 0, 0, 0.9); font-family: "Segoe UI VSS (Regular)", "Segoe UI", -apple-system, BlinkMacSystemFont, Roboto, "Helvetica Neue", Helvetica, Ubuntu, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji", "Segoe UI Symbol"; font-size: 15px; background-color: rgb(255, 255, 255);\"> tenant.</span>" | |
| ], | |
| "metadata": { | |
| "azdata_cell_guid": "55f5dffc-6348-4d11-a682-ba84aacf4f0d" | |
| }, | |
| "attachments": {} | |
| }, | |
| { | |
| "cell_type": "code", | |
| "source": [ | |
| "$body = '{\r\n", | |
| " \"properties\": {\r\n", | |
| " \"administratorType\": \"ActiveDirectory\",\r\n", | |
| " \"login\": \"\",\r\n", | |
| " \"sid\": \"\",\r\n", | |
| " \"tenantId\": \"\"\r\n", | |
| " }\r\n", | |
| " }'" | |
| ], | |
| "metadata": { | |
| "azdata_cell_guid": "eef30ed0-190f-489f-8424-3dcd8544a146" | |
| }, | |
| "outputs": [], | |
| "execution_count": null | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "source": [ | |
| "<span style=\"color:rgba(0, 0, 0, 0.9);font-family:"Segoe UI VSS (Regular)", "Segoe UI", -apple-system, BlinkMacSystemFont, Roboto, "Helvetica Neue", Helvetica, Ubuntu, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji", "Segoe UI Symbol";font-size:15px;background-color:rgb(255, 255, 255);\">Run the Put below to assign the AAD group to use for authentication.</span>" | |
| ], | |
| "metadata": { | |
| "azdata_cell_guid": "7fc14989-4482-4a19-a1db-63d65b55ef7f" | |
| }, | |
| "attachments": {} | |
| }, | |
| { | |
| "cell_type": "code", | |
| "source": [ | |
| "Invoke-WebRequest -Uri $ResourceUrl -Method Put -Authentication Bearer -Token $SecureToken -Body $body -ContentType 'application/json'" | |
| ], | |
| "metadata": { | |
| "azdata_cell_guid": "2fddf378-2ff7-4644-be93-0a8a31f7f861" | |
| }, | |
| "outputs": [], | |
| "execution_count": null | |
| } | |
| ] | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment