Category: Uncategorized

At work a colleague reached out asking for some help with getting some python code querying an Azure SQL Server database and getting it all working. This is right up my street, fixing things I don’t use on a day to day basis is something of a challenge I just love working on.

I will list out the steps in order to achieve this, bare in mind we have Azure SQL deployed as well as Azure DataBricks at this point and when we try to query the Azure SQL Database we see errors like the ones below:-

“com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host <redacted>.database.windows.net, port 1433 has failed. Error: “connect timed out. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.”.

as well as this one

com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open server “<server name redacted>” requested by the login. The login failed. ClientConnectionId:c5977705-8b83-4f12-a4ce-0268ac868798

Ok so reading these errors might mean you look into whitelisting IP addresses.

Lets write the steps down to fix this issue and maybe it will help someone, probably me when I forget I wrote this blog next week 🙂

Ok so we did the following steps:-

• Added a new Subnet to our databricks-vnet
• Find your Azure SQL Server instance in the portal, go to the Networking tab and clicked Private access, click the + to Create a Private Endpoint, on the Virtual Network tab choose the Virtual network your using for DataBricks and select the new Subnet we want to use. Make sure to keep ‘Integrate with Private DNS Zone’ ticked.
• Once the Private Endpoint has been created click on it and go to DNS Configuration, click on the link towards the bottom under the heading Private DNS Zone to be taken to your Private DNS Zone. Now click on ‘Virtual network links’. Again click the + to add a new Virtual Network Link and choose the DataBricks VNet, don’t tick Enable auto registration.

So it s just like any other Private Endpoint config, just remember to do the Virtual Network link. You also don’t need to whitelist an IP Addresses or anything like that.

Don’t forget to subscribe to my YouTube Channel. And my Newsletter

If you are reading this and thinking should I start my own newsletter then the answer is yes go do it now, what are you waiting for?

By the way you can sign up for my newsletter below:-

https://gregors-newsletter.beehiiv.com/subscribe

I had been meaning to create a newsletter for probably the best part of a 4 years and just never got around to it, the main reason why I created a newsletter is to keep in touch with people who read it and think its useful. I’m also using is for side quests but more on that in the newsletter.

Now I am only on week 2 of my newsletter and still figuring the format out – probably should have done that first but hey getting started is half the battle right. Go choose some where to create you’re newsletter and get started, you won’t regret it.

I looked at Beehiiv and Mailchimp and I am not here to say which is better because quite frankly I just wanted to get going.

I signed up to both and ended up going with Beehiiv, why who knows it just seemed to appeal more for whatever reason. Honestly it could have easily been Mailchimp, but anyhoo I went with Beehiiv.

It’s easy to use and easy to see your list of followers, easy to create posts and they can look pretty neat once you spend some time tweaking the look and feel. Its not a 10 out of 10 solid feel for me just yet, so I would give it maybe an 8 out of 10 but at this point I have 150 subscribers and I’m off and running.

So why should you create a newsletter – well if you want a list of people who follow you and have their email addresses should you want to reach these people then what better way to do it that with a weekly newsletter – that’s what its for in the end.

Don’t forget to subscribe to my YouTube Channel. And my Newsletter

This past week a customer asked me why cant I move some Azure Web apps from one app service plan to another so I had a quick look into the issue and learned something new so why not blog about it as its been a while.

The customer had 2 app service plans which were like so:-

App Service Plan 1, West Europe, rg-apps and was the Premium V2 P1:v2 Pricing Tier and OS Type Windows
App Service Plan 2, West Europe rg-apps and was the Premium V2 P1:v2 Pricing Tier and OS Type Windows

Now if you look at the docs (Manage App Service plan – Azure App Service | Microsoft Learn) it states that

“You can move an app to another App Service plan, as long as the source plan and the target plan are in the same resource group, geographical region,and of the same OS type. Any change in type such as Windows to Linux or any type that is different from the originating type is not supported.”

The next part of the docs was the reason why we couldn’t move the apps from App Service Plan 1 to App Service Plan 2.

So to check this I went to each App Service Plan and on the overview screen you can click on JSON view and there you can find the JSON property called “webspace” and they had differing values.

So if you ever run into this issue you can check this webspace setting and that just might be the issue. I am blogging this in case I ever come across this again and I forget what to check.

The Third app service plan that was there, they could move apps into this one without issue. There is a couple of ways to solve the problem and I’ll let you figure out what they might be but the customer decided to leave them as is for now.

Don’t forget to subscribe to my YouTube Channel. And my Newsletter

Ok so this blog post covers deploying an Azure Web App that talks to an Azure SQL Server database which we will then secure access to the database using a VNet and a Private Endpoint.

First we will deploy the web application which talks to Azure SQL, this wont be using a VNet nor a Private Endpoint and is unsecure and open to the internet, then we will tighten it down by adding the VNet and Private endpoint.

What are Azure Private Endpoints?

An Azure Private Endpoint is a network interface that connects your virtual network privately to a service powered by Azure Private Link. This allows you to access Azure services over an Azure Private Link, which is a private endpoint in your virtual network. This means that traffic between your virtual network and the service traverses over the Microsoft Azure backbone network, eliminating exposure from the public internet.

Ok lets get to it.

Step 1

Firstly follow the steps in this Microsoft article which you follow to deploy a web app taking to a local db, and then you can deploy an Azure SQL Database once we deploy to Azure (all steps within the following link)

https://learn.microsoft.com/en-us/azure/app-service/app-service-web-tutorial-dotnet-sqldatabase

At this point you should have an Azure Resource Group, an Azure App service plan (hosting plan) and an Azure Web Application deployed and working.

Enable If the database has no tables then you need to Enable code migrations, so go to the Tools Menu and then select Nuget Package Manager and then Package Manager Console, in the console enable Code First Migrations by typing Enable-Migrations and then press enter.

Read the section titled Enable Code First Migrations in Azure in the above link of the tutorial from Microsoft, make sure to publish to Azure again after this step.

Step 2

Now we need to VNet integrate this so we will start off by creating a new Azure VNet, I created my VNet with a 10.1.0.0/16 address space and then I created subnets like so:-

webappsSubnet: 10.1.2.0.24
sqlSubnet: 10.1.1.0/24

And then I clicked save.

Now we have a VNet with 2 subnets, lets VNet integrate both the SQL Server and the Web Application.

Step 3

Go to the Web app you deployed to Azure and then select Networking and then choose VNET Integration and select your VNet and then choose the webappsSubnet.

Once you add VNet integration it should look something like the following:-

Step 4

Ok so next do the same for the Azure SQL Server you deployed from the Microsoft guide and VNet integrate your Azure SQL Server.

On the Networking tab of your Azure SQL Server, make sure Public Access is set to Disable like the following:-

Now click on the Private Access tab and select create a Private Endpoint.

Create a Private Endpoint in Azure

In the second screen, make sure to select the correct VNet and choose the sqlSubnet.

So now we have setup a Private Endpoint for Azure SQL and we have waited for a few seconds so that the connection-state is Approved we are all set.

Troubleshooting

At this point your web application should be able to communicate with your backend Azure SQL Server using a Private Endpoint, if you delete the Private Endpoint you will see this:-

You will also see this is you don’t have the database populated, you should see the following if you have enable-migrations and re-published the code.

Connecting to the Database from your local pc

If you want to check that we have tables and data you can use a number of tools to connect to your new Azure SQL Server. I tend to use SQL Server Management Studio because I am old 🙂 – but before you can connect we need to change the Azure SQL Server firewall to allow my IP Address to connect to the database. To do this go to the Azure SQL server and then Networking and click on Public Access and fill it in like so:-

Give the Rule Name a decent name so you know who’s IP address you have whitelisted, in case you need to add several.

Note – Don’t tick the Box that says Allow Azure Services and resources to access this server, its not recommended to do this.

Once you connect you should see something like this:-

Summary

To summarise this blog post we initially deploy an Azure App Service with a SQL Server backend Database. Then we VNet integrate the Web App and SQL Server and then we use a Private Endpoint so that the communication from the Web App to Azure SQL traverses over the Microsoft Azure backbone network, eliminating exposure from the public internet.

If you have questions reach out to me here in the comments below or on twitter.

Don’t forget to subscribe to my YouTube Channel.

All 4 parts of the exam are now available on LinkedIn Learning, for anyone who was looking for all 4 parts they are now all available at the links below:-

Part 1- https://www.linkedin.com/learning/azure-data-fundamentals-dp-900-cert-prep-1-core-data-concepts

Part 2 – https://www.linkedin.com/learning/azure-data-fundamentals-dp-900-cert-prep-2-working-with-relational-data-on-azure-17091985

Part 3 – https://www.linkedin.com/learning/azure-data-fundamentals-dp-900-cert-prep-3-working-with-non-relational-data-on-azure/

Part 4 – https://www.linkedin.com/learning/azure-data-fundamentals-dp-900-cert-prep-4-analytics-workloads-on-azure

If you have questions reach out to me here in the comments below or on twitter.

Don’t forget to subscribe to my YouTube Channel.

In this blog post I show you how to enable Defender for Cloud using Bicep

Microsoft Azure Defender is a cloud-based security solution that helps protect Azure resources and workloads running in Azure, on-premises, or in other clouds.

As always I try to make use of the following GitHub repository https://github.com/Azure/ResourceModules/ this is where I go to make use of the hundreds of already written Bicep scripts which I can make use of very quickly.

I start by cloning the repository then lifting the files I need to make what ever I need to deploy work, in this case I want the following folder(s) https://github.com/Azure/ResourceModules/tree/84fe9dfd578a22079b03bbdee3554b9ac51c2dc2/modules/Microsoft.Security/azureSecurityCenter

I store the files in a modules folder.

// Defender for Cloud Details

// Defender for Cloud parameters

param defenderAutoProvision string = 'On'
param defenderAppServicesPricingTier string = 'Standard'
param defenderVirtualMachinesPricingTier string = 'Standard'
param defenderSqlServersPricingTier string = 'Standard'
param defenderStorageAccountsPricingTier string = 'Standard'
param defenderDnsPricingTier string ='Standard'
param defenderArmPricingTier string = 'Standard'

module enableDefenderForCloudOnSubscription 'modules/defenderForCloud.bicep' = {
  name: 'defenderForCloud'
  params: {
    scope: subscription().id
    workspaceId: createLogWorkspace.outputs.resourceID
    autoProvision: defenderAutoProvision
    virtualMachinesPricingTier: defenderVirtualMachinesPricingTier
    sqlServersPricingTier: defenderSqlServersPricingTier
    storageAccountsPricingTier: defenderStorageAccountsPricingTier
    appServicesPricingTier: defenderAppServicesPricingTier
    dnsPricingTier: defenderDnsPricingTier
    armPricingTier: defenderArmPricingTier
   }
  }

To run this I run a very small PowerShell script, that contains the following:-

$deploymentID = (New-Guid).Guid
$location = 'westeurope'

az deployment sub create --name $deploymentID
--location $location --template-file ./main-deployment-1.bicep
--parameters location=$location --confirm-with-what-if
--output none

And this will enable Defender for Cloud and you can change the parameters as you like.

If you have questions reach out to me here in the comments below or on twitter.

Don’t forget to subscribe to myYouTube Channel.

Part of my job is creating Azure Architectural diagrams and to be honest I really didn’t get on that well with Visio, its a great product but there was a lot of swearing when fiddling around with spacing and drawing arrows the way I wanted them etc., I just never felt proud of what I had created (It’s me not Visio lets be clear).

I stumbled across a video from https://twitter.com/LiorKamrat

Lior teaches you how to use PowerPoint, yes you read that correctly, PowerPoint to create really awesome looking architectural diagrams – definitely watch all of his video if you like me don’t get on well with Visio etc.

Whilst tweeting about this Dave Brannan asked if I had used draw.io inside Visual Studio Code, I had dabbled with draw.io but not within VS Code, so I installed the extension and started looking into it. Not bad I must say, I do like it and its very easy to create cracking diagrams.

Here is one I created from a sample diagram from the Microsoft docs about using Private Endpoints from a Web App to a SQL Server Database. I can reuse these super easily and use the GitHub repository David-Summers/Azure-Design: My Azure stencil collection for Visio. Highly functional and always up to date. (github.com) to copy in .png or .svg files which ever you prefer.

How about Draw.io?

If you install the draw.io integration extension by Henning Dietrichs to VS Code and then crate new empty file with the .drawio extension then you end up with this:-

This means you can use VS Code along with Draw.io to create diagrams – very cool.

Summary

I like the PowerPoint way of doing it as I can open up existing diagrams and easily create new diagrams off it it, I am sure you can do the same in any tool but I do particularly like how the PowerPoint diagrams turn out.

Massive thanks to Lior Kamrat for creating the video and I hope you find this post useful.

In Lior’s video he has some awesome links to to Azure Icon sets and example PowerPoints.

If you have questions reach out to me here in the comments below or on twitter.

Don’t forget to subscribe to myYouTube Channel.

In this blog post I show you how to create a new Azure PostgreSQL Flexible server using Bicep, the single server will be no longer at the start of 2024 so many of you will need to migrate to the flexible offering.

Azure PostgreSQL Flexible Server is a fully managed, cloud-based PostgreSQL database service that provides the ability to scale compute and storage resources independently, making it more flexible and cost-effective than other Azure PostgreSQL offerings.

As always I try to make use of the following GitHub repository https://github.com/Azure/ResourceModules/ this is where I go to make use of the hundreds of already written Bicep scripts which I can make use of very quickly.

I start by cloning the repository then lifting the files I need to make what ever I need to deploy work, in this case I want the following folder(s) https://github.com/Azure/ResourceModules/tree/main/modules/Microsoft.DBforPostgreSQL/flexibleServers

I store the files in a modules folder.

// Azure PostgreSQL Server details

param administratorLogin string = 'postgresqladmin'
param skuName string = 'Standard_D4s_v3'
param tier string = 'GeneralPurpose'
param availabilityZonestring string = '1'
param backupRetentionDays int = 20
param geoRedundantBackup string = 'Enabled'
param highAvailability string = 'SameZone'
param storageSizeGB int = 1024
param version string = '14'
param servername string = 'gregorspostgresql'

@description('Deploy an Azure PostgreSQL Server')
module createPostgresFlexibleServer 'modules/psqlflexibleServer_modules/deploy.bicep' = {
  scope: resourceGroup(dataTierRg)
  name: 'createPostgresFlexibleServer'
  params: {
    administratorLogin: administratorLogin
    administratorLoginPassword: administratorLoginPassword
    name: servername
    skuName: skuName 
    tier: tier 
    location: location
    availabilityZone: availabilityZonestring 
    backupRetentionDays: backupRetentionDays 
    geoRedundantBackup: geoRedundantBackup
    highAvailability: highAvailability
    storageSizeGB: storageSizeGB 
    version: version 
  }
}

To run this I run a very small PowerShell script, that contains the following:-

$deploymentID = (New-Guid).Guid
$location = 'westeurope'

az deployment sub create `
        --name $deploymentID `
        --location $location `
        --template-file ./main-deployment-1.bicep `
        --parameters location=$location  `
        --confirm-with-what-if `
        --output none
     

And this will deploy an Azure PostgreSQL Flexible server and you can change the parameters as you like.

If you have questions reach out to me here in the comments below or on twitter.

Don’t forget to subscribe to myYouTube Channel.

I have a terrible memory so this blog post is mainly to remind me how to VNet Integrate Azure SQL.

The code below is creating an Azure SQL Server and VNet integrating it – the VirtualNetworkRule is the key part and the following is how to go about it.

I use this existing Bicep repo for all of the Bicep that I write – https://github.com/Azure/ResourceModules/

@description('Deploy an Azure SQL Server')
module createAzureSQL 'modules/azuresql_modules/deploy.bicep' = if (deployAzureSQL) {
  scope: resourceGroup(dataTierRg)
  name: azureSQLServerName
  params: {
    name: azureSQLServerName
    location: sqllocation 
    administratorLogin: azureSQLServerAdminLogin
    administratorLoginPassword: azureSQLAdminPassword
    tags: tags
    virtualNetworkRules: [
      {
        name: 'vnet-rule-${azureSQLServerName}'
        serverName: azureSQLServerName
        ignoreMissingVnetServiceEndpoint: false 
        virtualNetworkSubnetId: '/subscriptions/${subscriptionID}/resourceGroups/${appTierRg}/providers/Microsoft.Network/virtualNetworks/${appVNetName}/subnets/dataSubNet'
      }
    ]
  }
  dependsOn: [
    newRG
    createAppVNet
  ]
}

To get this to work you should also add a service endpoint into your subnet like the following:-

@description('An array of the subnets for the Application VNet.') 
var appSubnets = {
  shared: [

    {
      name: 'appSubnet'
      addressPrefix: '172.16.0.0/24'
      delegations: [
        {
          name: 'delegation'
          properties: {
            serviceName: 'Microsoft.Web/serverfarms'
          }
        }
      ]
    }
    {
      name: 'dataSubNet'
      addressPrefix: '172.16.1.0/24'
      serviceEndpoints: [
        {
          service: 'Microsoft.Sql'
        }
      ]
    }
  ]
}

Let me know if you found this example useful.