Azure SQL with Managed Service Identity sandbox

Azure SQL with Managed Service Identity sandbox

by | Nov 16, 2020 | Technology

This article is contributed. See the original author and article here.

 


Introduction


Are you moving from OnPremises to Azure SQL? Using Managed Identity may help with your legacy applications authentication.


In a previous post, we saw how to use SSO with your current domain by leveraging AD Connect synchronization of your Active Directory with AAD.


But if your AD is not yet synchronized and your application connects with a domain service account? Use SQL authentication? Use AAD authentication with password?


Following the great post from Sergio Fonseca, Using Managed Service Identity (MSI) to authenticate on Azure SQL DB, explaining in details how Managed Service Identity works with Azure SQL, here’s how to set a sandbox and try them in 15 minutes.


 


Subscriptions


In this example, we’ll use a MSDN subscription, providing an Azure Active Directory Free license.


If using a different subscription, ensure that you’ll have sufficient administration rights at the Azure Active Directory level.


If you don’t have a subscription, you can create a free account here


 


Provisioning Resources


We’ll start by creating the resources required for this sandbox. The following AzCli commands can be executed in your Azure Shell or with Windows Terminal


 



  • Create a Windows Server virtual machine Client1. (link)


 

LOCATION=westeurope
RESOURCEGROUP=ManagedIdentityRG
ADMINNAME=AzureUser
ADMINPWD=<StrongPasswordRequired>

# Resource Group Creation
az group create --name $RESOURCEGROUP --location $LOCATION

# Client1 VM Creation
az vm create --resource-group $RESOURCEGROUP --name Client1 --image win2016datacenter --size Standard_B4ms --admin-username $ADMINNAME --admin-password $ADMINPWD --no-wait

 


 



  • Create a Windows Server virtual machine Client2


 

# Client2 VM Creation
az vm create --resource-group $RESOURCEGROUP --name Client2 --image win2016datacenter --size Standard_B4ms --admin-username $ADMINNAME --admin-password $ADMINPWD --no-wait

 


 


 



  • Create an Azure SQL Database


 

SERVER="server-$RANDOM"
DATABASE="AdventureWorksLT"
ALLOWAZURESERVICE=0.0.0.0

# Azure SQL DB logical server Creation
az sql server create --name $SERVER --resource-group $RESOURCEGROUP --location $LOCATION --admin-user $ADMINNAME --admin-password $ADMINPWD

# Allow Azure Service to access Azure SQL
az sql server firewall-rule create --resource-group $RESOURCEGROUP --server $SERVER -n AllowAZServices --start-ip-address $ALLOWAZURESERVICE --end-ip-address $ALLOWAZURESERVICE

# Azure SQL DB Creation
az sql db create --resource-group $RESOURCEGROUP --server $SERVER --name $DATABASE --sample-name $DATABASE --service-objective Basic

echo "Server Name : $SERVER"

 


 



  • If you want to add your public IP


 

MYIP=X.X.X.X

az sql server firewall-rule create --resource-group $RESOURCEGROUP --server $SERVER -n MyIP --start-ip-address $MYIP --end-ip-address $MYIP

 


 



  • Add an Azure Active Directory admin to the Azure SQL


 

AADADMIN="XXXXXX@XXXXXXXX.onmicrosoft.com"
AADADMINOBJECTID=$(az ad user show --id $AADADMIN --query objectId -o tsv)

# Set AAD admin for Azure SQL
az sql server ad-admin create --display-name $AADADMIN --object-id $AADADMINOBJECTID --resource-group $RESOURCEGROUP --server $SERVER

 


 



  • And finally configure Client1 and Client2


Managed Identities can be used starting with the ODBC driver 17.6, JDBC driver 7.2, OLEDB 18.3


https://docs.microsoft.com/en-us/sql/connect/odbc/windows/release-notes-odbc-sql-server-windows?view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/connect/jdbc/release-notes-for-the-jdbc-driver?view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/connect/oledb/release-notes-for-oledb-driver-for-sql-server?view=sql-server-ver15

 


Installing Visual C++ 2017 Redistributable for Visual Studio 2017 is also required.


https://support.microsoft.com/en-us/help/2977003/the-latest-supported-visual-c-downloads

 

# Install VSC++ Redist, ODBC 17.6 Driver on Client1
az vm run-command invoke --command-id RunPowerShellScript --name Client1 --resource-group $RESOURCEGROUP --scripts "New-Item -Path C:Install -ItemType Directory" 
"cd /Install" 
"Invoke-WebRequest -Uri 'https://aka.ms/vs/16/release/vc_redist.x64.exe' -OutFile 'vc_redist.x64.exe'" 
".vc_redist.x64.exe /install /quiet /norestart | Out-Null" 
"Invoke-WebRequest -Uri 'https://go.microsoft.com/fwlink/?linkid=2137027' -OutFile 'msodbcsql.msi'" 
".msodbcsql.msi /quiet /norestart IACCEPTMSODBCSQLLICENSETERMS=YES"

 

# Install VSC++ Redist, ODBC 17.6 Driver on Client2
az vm run-command invoke --command-id RunPowerShellScript --name Client2 --resource-group $RESOURCEGROUP --scripts "New-Item -Path C:Install -ItemType Directory" 
"cd /Install" 
"Invoke-WebRequest -Uri 'https://aka.ms/vs/16/release/vc_redist.x64.exe' -OutFile 'vc_redist.x64.exe'" 
".vc_redist.x64.exe /install /quiet /norestart | Out-Null" 
"Invoke-WebRequest -Uri 'https://go.microsoft.com/fwlink/?linkid=2137027' -OutFile 'msodbcsql.msi'" 
".msodbcsql.msi /quiet /norestart IACCEPTMSODBCSQLLICENSETERMS=YES"

 


 


Activating System Assigned Managed Identity 


 


To activate System Assigned Managed identity, navigate to your Client1 VM and click on the Identity in the left pane. On the System Assigned, Set the Status to ON and Save.


 


image001.png


 

az vm identity assign --resource-group $RESOURCEGROUP --name Client1

 


 


 You’ll now see the identity assigned to the Client1 VM. 


image003.png


 


In your AAD, a new identity has been created


image005.png


 


This identity is correlated with your VM lifecycle. If you delete the VM, this identity will be deleted too.


At this point, your VM Client1 has now its own identity and we need to give access to the Azure SQL DB. To do so :


 



  • Connect to your Azure SQL DB with your AAD admin account to create a user for your VM Client1.


 

sqlcmd -S "$SERVER.database.windows.net" -G -U $AADADMIN -d master -Q "CREATE USER Client1 FROM EXTERNAL PROVIDER"

sqlcmd -S  "$SERVER.database.windows.net" -G -U $AADADMIN -d $DATABASE -Q "CREATE USER Client1 FROM EXTERNAL PROVIDER; ALTER ROLE db_datareader ADD MEMBER Client1"

 


 



  • From Client1 VM, you can test using Powershell


 

$AzServerName = “”

$AzSqlCn = New-Object System.Data.Odbc.OdbcConnection
$AzSqlCn.ConnectionString = "DRIVER={ODBC Driver 17 for SQL Server};Server=$AzServerName;Database=AdventureWorksLT;Authentication=ActiveDirectoryMsi;"
$AzSqlCn.Open()
$Query = "SELECT @@SERVERNAME AS AzureSQLSrv, DB_NAME() AS AzureSQLDB, HOST_NAME() AS RemoteClient, SYSTEM_USER AS CurrentIdentity”
$OdbcCmd = New-object System.Data.Odbc.OdbcCommand($Query,$AzSqlCn)
$Ds = New-Object System.Data.DataSet
$Da = New-Object System.Data.Odbc.OdbcDataAdapter
$Da.SelectCommand = $OdbcCmd
$Da.Fill($Ds)
$AzSqlCn.Close()
$Ds.Tables[0]

 


 



  • If you prefer to continue using Azure Shell


 

# Create Powershell script
cat <<'EOF'> script.ps1
param(
    [Parameter(Mandatory=$true)][string]$AzServerName
    )

$AzSqlCn = New-Object System.Data.Odbc.OdbcConnection
$AzSqlCn.ConnectionString = "DRIVER={ODBC Driver 17 for SQL Server};Server=$AzServerName;Database=AdventureWorksLT;Authentication=ActiveDirectoryMsi;"
$AzSqlCn.Open()
$Query = "SELECT @@SERVERNAME AS AzureSQLSrv, DB_NAME() AS AzureSQLDB, HOST_NAME() AS RemoteClient, SYSTEM_USER AS CurrentIdentity"
$OdbcCmd = New-object System.Data.Odbc.OdbcCommand($Query,$AzSqlCn)
$Ds = New-Object System.Data.DataSet
$Da = New-Object System.Data.Odbc.OdbcDataAdapter
$Da.SelectCommand = $OdbcCmd
$Da.Fill($Ds)
$AzSqlCn.Close()
$Ds.Tables[0]
EOF

# Remotely execute Powershell script on Client1
az vm run-command invoke --command-id RunPowerShellScript --name Client1 --resource-group $RESOURCEGROUP --scripts @script.ps1 --parameters "AzServerName=$SERVER.database.windows.net" --query 'value[0].message' -o tsv

 


*Using Run Command increases the execution time, but using it is more convenient here than RDP.

 


Note, that you don’t have to specify Login/Password in the connection string. The authentication option is ActiveDirectoryMsi


The driver will acquire accessToken for establishing a secure connection to the Azure SQL DB


If your application required a user datasource, or if you are not familiar with Powershell, you can try to connect via the ODBC driver UI.


 



  • Test using the ODBC driver UI. (Create a User Data Source via odbcad32.exe)


image007.png


 


image009.png


Login ID must be empty.


 


image011.png


 



  • Now, Remove the user on Azure SQL DB, on master and AdventureWorksLT.


 

sqlcmd -S "$SERVER.database.windows.net" -G -U $AADADMIN -d master -Q "DROP USER Client1"

sqlcmd -S "$SERVER.database.windows.net" -G -U $AADADMIN -d $DATABASE -Q "DROP USER Client1"

 


 


Activating User Assigned Managed Identity


Another possibility is to create a User Assigned Managed Identity. This lifecycle of this kind of identity is not correlated with your resources.


 



  • Select Managed Identity in the search bar


image013.png


 



  • Add one named Client-UAMI


image015.png


 

az identity create --name Client-UAMI --resource-group $RESOURCEGROUP --location $LOCATION

 


 



  • Click Review and Create


Once done, you’ll see the newly created identity in your AAD. Client1 is still present as we don’t have remove the System Assigned Identity yet


 


image017.png


 


We’ll now update the Client1 identity.



  • Go to the Client1 on your Azure portal and Click on the Identity on the left pane.



  • On System Assigned, set Status to Off. Then Save. The System Assigned managed Identity will be removed and deleted in your AAD.


image019.png


 

az vm identity remove --resource-group $RESOURCEGROUP -n Client1

 


 



  • Then on User Assigned block, you’ll be able to select Client-UAMI. Select it and Click Add


image021.png


 

az vm identity assign --resource-group $RESOURCEGROUP --name Client1 --identities Client-UAMI

 


 



  • Connect to your Azure SQL DB with your AAD admin account to create a user for your VM Client1.


 

sqlcmd -S "$SERVER.database.windows.net" -G -U $AADADMIN -d master -Q "CREATE USER [Client-UAMI] FROM EXTERNAL PROVIDER"

sqlcmd -S  "$SERVER.database.windows.net" -G -U $AADADMIN -d $DATABASE -Q "CREATE USER [Client-UAMI] FROM EXTERNAL PROVIDER; ALTER ROLE db_datareader ADD MEMBER [Client-UAMI]"

 


 


But as this identity is User Managed, you can use it on another client as well.


So, you can assign it to Client2


 

az vm identity assign --resource-group $RESOURCEGROUP --name Client2 --identities Client-UAMI

 


 


and you can now try to connect to your database from Client1 and Client2 


 

# Remotely execute Powershell script on Client1
az vm run-command invoke --command-id RunPowerShellScript --name Client1 --resource-group $RESOURCEGROUP --scripts @script.ps1 --parameters "AzServerName=$SERVER.database.windows.net" --query 'value[0].message' -o tsv

# Remotely execute Powershell script on Client2
az vm run-command invoke --command-id RunPowerShellScript --name Client2 --resource-group $RESOURCEGROUP --scripts @script.ps1 --parameters "AzServerName=$SERVER.database.windows.net" --query 'value[0].message' -o tsv

 


 


 


Hope this helps.


 


Ryad B


Customer Engineer – Data & AI

Experiencing Data Access Issue in Azure portal for Log Analytics – 11/16 – Investigating

by | Nov 15, 2020 | Technology

This article is contributed. See the original author and article here.

Initial Update: Monday, 16 November 2020 01:06 UTC

We are aware of issues within Log Analytics and are actively investigating. Some customers may experience issues with missed, delayed or wrongly fired alerts or experience difficulties accessing data for resources hosted in West US2 and North Europe. 
  • Work Around: None
  • Next Update: Before 11/16 03:30 UTC
We are working hard to resolve this issue and apologize for any inconvenience.
-Eric Singleton

Open API Extension Support for Azure Functions V1

by | Nov 15, 2020 | Technology

This article is contributed. See the original author and article here.

There is an open-source project that generates an Open API document on-the-fly on an Azure Functions app. The open-source project also provides a NuGet package library. This project has been supporting the whole Azure Functions runtimes from v1 since Day 1. Although it does, the v1 runtime has got its own limitation that cannot generate the Open API document automatically. But, as always, there’s a workaround. I’m going to show how to generate the Open API document on-the-fly and execute the v1 function through the Azure Function Proxy feature.


 


Legacy V1 Azure Function


 


Generally speaking, many legacy enterprise applications still need Azure Functions v1 runtime due to their reference dependencies. Let’s assume that the Azure Functions v1 endpoint looks like the following:


 


    namespace MyV1ProxyFunctionApp

    {

        public static class LoremIpsumHttpTrigger

        {

            [FunctionName(“LoremIpsumHttpTrigger”)]

            public static async Task Run(

                [HttpTrigger(AuthorizationLevel.Function, “GET”, Route = “lorem/ipsum”)] HttpRequest req,

                ILogger log)

            {

                return await Task.FromResult(new OkResult()).ConfigureAwait(false);

            }

        }

    }

 


The v1 runtime has a strong tie to the Newtonsoft.Json package version 9.0.1. Therefore, if the return object of MyReturnObject has a dependency on Newtonsoft.Json v10.0.1 and later, the Open API extension cannot be used.


 


Azure Functions Proxy for Open API Document


 


The Azure Functions Proxy feature comes the rescue! Although it’s not a perfect solution, it provides with the same developer experience, which is worth trying. Let’s build an Azure Functions app targeting the v3 runtime. The name of the proxy function is MyV1ProxyFunctionApp (line #1). All the rest are set to be the same as the legacy v1 app (line #3-7). However, make sure this is the proxy purpose, meaning it does nothing but returns an OK response (line #10).


 


    namespace MyV1ProxyFunctionApp

    {

        public static class LoremIpsumHttpTrigger

        {

            [FunctionName(“LoremIpsumHttpTrigger”)]

            public static async Task Run(

                [HttpTrigger(AuthorizationLevel.Function, “GET”, Route = “lorem/ipsum”)] HttpRequest req,

                ILogger log)

            {

                return await Task.FromResult(new OkResult()).ConfigureAwait(false);

            }

        }

    }

 


Once installed the Open API library, let’s add decorators above the FunctionName(…) decorator (line #5-9).


 


    namespace MyV1ProxyFunctionApp

    {

        public static class LoremIpsumHttpTrigger

        {

            [OpenApiOperation(operationId: “getIpsum”, tags: new[] { “ipsum” }, Summary = “Gets Ipsum from Lorem”, Description = “This gets Ipsum from Lorem.”, Visibility = OpenApiVisibilityType.Important)]

            [OpenApiParameter(name: “name”, In = ParameterLocation.Query, Required = true, Type = typeof(string), Summary = “Lorem name”, Description = “Lorem name”, Visibility = OpenApiVisibilityType.Important)]

            [OpenApiResponseWithBody(statusCode: HttpStatusCode.OK, contentType: “application/json”, bodyType: typeof(MyReturnObject), Summary = “The Ipsum response”, Description = “This returns the Ipsum response”)]

            [OpenApiResponseWithoutBody(statusCode: HttpStatusCode.NotFound, Summary = “Name not found”, Description = “Name parameter is not found”)]

            [OpenApiResponseWithoutBody(statusCode: HttpStatusCode.BadRequest, Summary = “Invalid Lorem”, Description = “Lorem is not valid”)]


            [FunctionName(“LoremIpsumHttpTrigger”)]

            public static async Task Run(

                [HttpTrigger(AuthorizationLevel.Function, “GET”, Route = “lorem/ipsum”)] HttpRequest req,

                ILogger log)

            {

                return await Task.FromResult(new OkResult()).ConfigureAwait(false);

            }

        }

    }

 


All done! Run this proxy app, and you will be able to see the Swagger UI page. As I mentioned above, this app doesn’t work but show the UI page. For this app to work, extra work needs to be done.


 


proxies.json to Legacy Azure Functions V1


 


Add the proxies.json file to the root folder. As we added the same endpoint as the legacy function app on purpose (line #6,11), API consumers should have the same developer experience as before except the hostname change. In addition to that, both querystring values and request headers are relayed to the legacy app (line #13-14).


 


    {

        “$schema”: “http://json.schemastore.org/proxies”,

        “proxies”: {

          “DummyOnOff”: {

            “matchCondition”: {

              “route”: “/api/lorem/ipsum”,

              “methods”: [

                “GET”

              ]

            },

            “backendUri”: “https://mylegacyfunctionapp.azurewebsites.net/api/lorem/ipsum”,

            “requestOverrides”: {

              “backend.request.headers”: “{request.headers}”,

              “backend.request.querystring”: “{request.querystring}”

            }

          }

        }

      }

 


Then update the .csproj file to deploy the proxies.json file together (line #10-12).


 


    <Project Sdk=”Microsoft.NET.Sdk”>

      <PropertyGroup>

        <TargetFramework>netcoreapp3.1</TargetFramework>

        <AzureFunctionsVersion>v3</AzureFunctionsVersion>

        …

      </PropertyGroup>

      …

      <ItemGroup>

        …

        <None Update=”proxies.json”>

          <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>

        </None>

      </ItemGroup>

      …

    </Project>

 


All done! Run this proxy function on your local machine or deploy it to Azure, and hit the proxy API endpoint. Then you’ll be able to see the Open API document generated on-the-fly and execute the legacy API through the proxy.


 



 


So far, we have created an Azure Functions app using the Azure Functions Proxy feature. It also supports the Open API document generation for the v1 runtime app. The flip-side of this approach costs doubled because all API requests hit the proxy then the legacy. The cost optimisation should be investigated from the enterprise architecture perspective.


This article was originally published on Dev Kimchi.

Experiencing Data Access Issue in Azure portal for Log Analytics – 11/15 – Investigating

by | Nov 14, 2020 | Technology

This article is contributed. See the original author and article here.

Initial Update: Sunday, 15 November 2020 01:18 UTC

We are aware of issues within Log Analytics and are actively investigating. Some customers may experience issues with missed, delayed or wrongly fired alerts or experience difficulties accessing data for resources hosted in West US2 and North Europe. .
  • Work Around: None
  • Next Update: Before 11/15 03:30 UTC
We are working hard to resolve this issue and apologize for any inconvenience.
-Eric Singleton

New release: An early holiday season 2020 gift, SharePointDsc v4.4

by | Nov 14, 2020 | Technology

This article is contributed. See the original author and article here.

After several last minutes improvements and bugfixes this week, SharePointDsc v4.4 saw the daylight on Saturday November 14th. This version contains a lot of bugfixes that were fixed over the last period. 


 


The biggest changes in this release are:



  • The switch in SPFarm from a Lock database to a Lock table in the TempDB. This is done to fully support pre-created databases and the fact that most of the time this means that SharePoint admins don’t have dbcreator permissions and therefore cannot create the Lock database.

  • More diagnostic logging to the event log. When the code throws an exception, the error is now also logged to the custom SPDsc event log. This functionality will be extended over time. For more information see our previous blog post. 


 


You can find the SharePointDsc v4.4 in the PowerShell Gallery!


 


NOTE: We can always use additional help in making SharePointDsc even better. So if you are interested in contributing to SharePointDsc, check-out the open issues in the issue list, check-out this post in our Wiki or leave a comment on this blog post.


 


Improvement/Fixes in v4.4:



Added



  • SharePointDsc

    • Added logging to the event log when the code throws an exception

    • Added support for trusted domains to Test-SPDscIsADUser helper function



  • SPInstall

    • Added documentation about a SharePoint 2019 installer issue





Changed


  • SharePointDsc

    • Updated Convert-SPDscHashtableToString to output the username when parameter is a PSCredential






  • SPFarm

    • Switched from creating a Lock database to a Lock table in the TempDB. This to allow the use of precreated databases.

    • Updated code to properly output used credential parameters to verbose logging



  • SPSite

    • Added more explanation to documentation on which parameters are checked



  • SPWeb

    • Added more explanation to documentation on using this resource





Fixed



  • SPConfigWizard

    • Fixes issue where a CU installation wasn’t registered properly in the config database. Added logic to run the Product Version timer job



  • SPSearchTopology

    • Fixes issue where applying a topology failed when the search service instance was disabled instead of offline



  • SPSecureStoreServiceApP

    • Fixes issue where custom database name was no longer used since v4.3



  • SPShellAdmins

    • Fixed issue with Get-DscConfiguration which threw an error when only one item was returned by the Get method



  • SPWordAutomationServiceApp

    • Fixed issue where provisioning the service app requires a second run to update all specified parameters



  • SPWorkflowService

    • Fixed issue configuring workflow service when no workflow service is currently configured






 


A huge thanks to the following guy for contributing to this project:
Jens Otto Hatlevold


 


Also a huge thanks to everybody who submitted issues and all that support this project. It wasn’t possible without all of your help!


 


For more information about how to install SharePointDsc, check our Readme.md.


 


Let us know in the comments what you think of this release! If you find any issues, please submit them in the issue list on GitHub.


 


Happy SharePointing!!