Connect an Azure App (with an Connection String) to an Azure SQL Database (with the PowerShell)!

%3CLINGO-SUB%20id%3D%22lingo-sub-1842060%22%20slang%3D%22en-US%22%3EConnect%20an%20Azure%20App%20(with%20an%20Connection%20String)%20to%20an%20Azure%20SQL%20Database%20(with%20the%20PowerShell)!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1842060%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Azure%20friends%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20used%20the%20PowerShell%20ISE%20for%20this%20configuration.%20But%20you%20are%20also%20very%20welcome%20to%20use%20Visual%20Studio%20Code%2C%20just%20as%20you%20wish.%26nbsp%3BPlease%20start%20with%20the%20following%20steps%20to%20begin%20the%20deployment%20(the%20Hashtags%20are%20comments)%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%23The%20first%20two%20lines%20have%20nothing%20to%20do%20with%20the%20configuration%2C%20but%20make%20some%20space%20below%20in%20the%20blue%20part%20of%20the%20ISE%3C%2FP%3E%3CP%3E%3CSTRONG%3ESet-Location%20C%3A%5CTemp%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EClear-Host%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%23So%20that%20you%20can%20carry%20out%20the%20configuration%2C%20you%20need%20the%20necessary%20cmdlets%2C%20these%20are%20contained%20in%20the%20module%20Az%20(is%20the%20higher-level%20module%20from%20a%20number%20of%20submodules)%3C%2FP%3E%3CP%3E%3CSTRONG%3EInstall-Module%20-Name%20Az%20-Force%20-AllowClobber%20-Verbose%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%23Log%20into%20Azure%3CBR%20%2F%3E%3CSTRONG%3EConnect-AzAccount%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%23Select%20the%20correct%20subscription%3CSTRONG%3E%3CBR%20%2F%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3EGet-AzContext%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3EGet-AzSubscription%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3EGet-AzSubscription%20-SubscriptionName%20%22your%20subscription%20name%22%20%7C%20Select-AzSubscription%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3E%23%26nbsp%3BGenerates%26nbsp%3Ba%26nbsp%3BRandom%26nbsp%3BValue%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSTRONG%3E%24Random%3D(New-Guid).ToString().Substring(0%2C8)%3C%2FSTRONG%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3CDIV%3E%3CSPAN%3E%23%26nbsp%3BVariables%26nbsp%3B(never%26nbsp%3Buse%26nbsp%3Bsecrets%26nbsp%3Bin%26nbsp%3Bcode%2C%26nbsp%3Bbut%26nbsp%3Bfor%26nbsp%3Bthis%26nbsp%3Bdemo%26nbsp%3Bit's%26nbsp%3Bfine!)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSTRONG%3E%24ResourceGroup%3D%22MyResourceGroup%24Random%22%3C%2FSTRONG%3E%3C%2FDIV%3E%3CDIV%3E%3CSTRONG%3E%24AppName%3D%22webappwithSQL%24Random%22%3C%2FSTRONG%3E%3C%2FDIV%3E%3CDIV%3E%3CSTRONG%3E%24Location%3D%22WestEurope%22%3C%2FSTRONG%3E%3C%2FDIV%3E%3CDIV%3E%3CSTRONG%3E%24ServerName%3D%22webappwithsql%24Random%22%3C%2FSTRONG%3E%3C%2FDIV%3E%3CDIV%3E%3CSTRONG%3E%24StartIP%3D%22your%26nbsp%3Bpublic%26nbsp%3Bip%22%3C%2FSTRONG%3E%3C%2FDIV%3E%3CDIV%3E%3CSTRONG%3E%24EndIP%3D%22your%26nbsp%3Bpublic%26nbsp%3Bip%22%3C%2FSTRONG%3E%3C%2FDIV%3E%3CDIV%3E%3CSTRONG%3E%24Username%3D%22ServerAdmin%22%3C%2FSTRONG%3E%3C%2FDIV%3E%3CDIV%3E%3CSTRONG%3E%24Password%3D%22P%40ssw0rd123!%22%3C%2FSTRONG%3E%3C%2FDIV%3E%3CDIV%3E%3CSTRONG%3E%24SqlServerPassword%3DNew-Object%26nbsp%3B-TypeName%26nbsp%3BSystem.Management.Automation.PSCredential%26nbsp%3B-ArgumentList%26nbsp%3B%24Username%2C(ConvertTo-SecureString%26nbsp%3B-String%26nbsp%3B%24Password%26nbsp%3B-AsPlainText%26nbsp%3B-Force)%3C%2FSTRONG%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3CDIV%3E%3CSPAN%3E%23Create%26nbsp%3Ba%26nbsp%3BResource%26nbsp%3BGroup%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSTRONG%3ENew-AzResourceGroup%26nbsp%3B-Name%26nbsp%3B%24ResourceGroup%26nbsp%3B-Location%26nbsp%3B%24Location%3C%2FSTRONG%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3CDIV%3E%3CSPAN%3E%23Create%26nbsp%3Ban%26nbsp%3BApp%26nbsp%3BService%26nbsp%3BPlan%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSTRONG%3ENew-AzAppservicePlan%26nbsp%3B-Name%26nbsp%3BWebAppwithSQLPlan%26nbsp%3B-ResourceGroupName%26nbsp%3B%24ResourceGroup%26nbsp%3B-Location%26nbsp%3B%24Location%26nbsp%3B-Tier%26nbsp%3BBasic%3C%2FSTRONG%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3CDIV%3E%3CSPAN%3E%23Create%26nbsp%3Ba%26nbsp%3BWeb%26nbsp%3BApp%26nbsp%3Bin%26nbsp%3Bthe%26nbsp%3BApp%26nbsp%3BService%26nbsp%3BPlan%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSTRONG%3ENew-AzWebApp%26nbsp%3B-Name%26nbsp%3B%24AppName%26nbsp%3B-ResourceGroupName%26nbsp%3B%24ResourceGroup%26nbsp%3B-Location%26nbsp%3B%24Location%26nbsp%3B-AppServicePlan%26nbsp%3BWebAppwithSQLPlan%3C%2FSTRONG%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3CDIV%3E%3CSPAN%3E%23Create%26nbsp%3Ba%26nbsp%3BSQL%26nbsp%3BDatabase%26nbsp%3BServer%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSTRONG%3ENew-AzSQLServer%26nbsp%3B-ServerName%26nbsp%3B%24ServerName%26nbsp%3B-Location%26nbsp%3B%24Location%26nbsp%3B-SqlAdministratorCredentials%26nbsp%3B%24SqlServerPassword%26nbsp%3B-ResourceGroupName%26nbsp%3B%24ResourceGroup%3C%2FSTRONG%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3CDIV%3E%3CSPAN%3E%23Create%26nbsp%3BFirewall%26nbsp%3BRule%26nbsp%3Bfor%26nbsp%3BSQL%26nbsp%3BDatabase%26nbsp%3BServer%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSTRONG%3ENew-AzSqlServerFirewallRule%26nbsp%3B-FirewallRuleName%26nbsp%3B%22AllowYourIp%22%26nbsp%3B-StartIpAddress%26nbsp%3B%24StartIP%26nbsp%3B-EndIPAddress%26nbsp%3B%24EndIP%26nbsp%3B-ServerName%26nbsp%3B%24ServerName%26nbsp%3B-ResourceGroupName%26nbsp%3B%24ResourceGroup%3C%2FSTRONG%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3CDIV%3E%3CSPAN%3E%23Create%26nbsp%3BSQL%26nbsp%3BDatabase%26nbsp%3Bin%26nbsp%3BSQL%26nbsp%3BDatabase%26nbsp%3BServer%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSTRONG%3ENew-AzSQLDatabase%26nbsp%3B-ServerName%26nbsp%3B%24ServerName%26nbsp%3B-DatabaseName%26nbsp%3BMySampleDatabase%26nbsp%3B-ResourceGroupName%26nbsp%3B%24ResourceGroup%3C%2FSTRONG%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3CDIV%3E%3CSPAN%3E%23Assign%20the%20Connection%26nbsp%3BString%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSTRONG%3ESet-AzWebApp%26nbsp%3B-ConnectionStrings%26nbsp%3B%40%7B%26nbsp%3BMyConnectionString%26nbsp%3B%3D%26nbsp%3B%40%7B%26nbsp%3BType%3D%22SQLAzure%22%3B%26nbsp%3BValue%26nbsp%3B%3D%22Server%3Dtcp%3A%24ServerName.database.windows.net%3BDatabase%3DMySampleDatabase%3BUser%26nbsp%3BID%3D%24Username%40%24ServerName%3BPassword%3D%24password%3BTrusted_Connection%3DFalse%3BEncrypt%3DTrue%3B%22%26nbsp%3B%7D%26nbsp%3B%7D%26nbsp%3B-Name%26nbsp%3B%24AppName%26nbsp%3B-ResourceGroupName%26nbsp%3B%24ResourceGroup%3C%2FSTRONG%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3CDIV%3E%3CSPAN%3E%23Check%26nbsp%3Bthe%26nbsp%3Bresult%26nbsp%3Bin%26nbsp%3Bthe%26nbsp%3Bportal%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CP%3ENow%20you%20have%20used%20the%20PowerShell%20to%26nbsp%3Bcreate%20an%20Azure%20SQL%20Database%20and%20an%20Azure%20App%20(with%20an%20connection%20string)!%20Congratulations!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%23Delete%20all%20resources%20(when%20you%20no%20longer%20need%20it)%3CBR%20%2F%3E%3CSTRONG%3ERemove-AzResourceGroup%20-Name%26nbsp%3B%24ResourceGroup%3C%2FSTRONG%3E%26nbsp%3B%3CSTRONG%3E-Force%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20this%20article%20was%20useful.%20Best%20regards%2C%20Tom%20Wechsler%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EP.S.%26nbsp%3BAll%20scripts%20(%23PowerShell%2C%20Azure%20CLI%2C%20%23Terraform%2C%20%23ARM)%20that%20I%20use%20can%20be%20found%20on%20github!%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fgithub.com%2Ftomwechsler%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fgithub.com%2Ftomwechsler%3CBR%20%2F%3E%3C%2FA%3E%3C%2FP%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1842060%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EWeb%20Apps%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
MVP

 

Hi Azure friends,

 

I used the PowerShell ISE for this configuration. But you are also very welcome to use Visual Studio Code, just as you wish. Please start with the following steps to begin the deployment (the Hashtags are comments):

 

#The first two lines have nothing to do with the configuration, but make some space below in the blue part of the ISE

Set-Location C:\Temp
Clear-Host

 

#So that you can carry out the configuration, you need the necessary cmdlets, these are contained in the module Az (is the higher-level module from a number of submodules)

Install-Module -Name Az -Force -AllowClobber -Verbose

 

#Log into Azure
Connect-AzAccount

 

#Select the correct subscription

Get-AzContext

Get-AzSubscription

Get-AzSubscription -SubscriptionName "your subscription name" | Select-AzSubscription

 

# Generates a Random Value
$Random=(New-Guid).ToString().Substring(0,8)

# Variables (never use secrets in code, but for this demo it's fine!)
$ResourceGroup="MyResourceGroup$Random"
$AppName="webappwithSQL$Random"
$Location="WestEurope"
$ServerName="webappwithsql$Random"
$StartIP="your public ip"
$EndIP="your public ip"
$Username="ServerAdmin"
$Password="P@ssw0rd123!"
$SqlServerPassword=New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $Username,(ConvertTo-SecureString -String $Password -AsPlainText -Force)

#Create a Resource Group
New-AzResourceGroup -Name $ResourceGroup -Location $Location

#Create an App Service Plan
New-AzAppservicePlan -Name WebAppwithSQLPlan -ResourceGroupName $ResourceGroup -Location $Location -Tier Basic

#Create a Web App in the App Service Plan
New-AzWebApp -Name $AppName -ResourceGroupName $ResourceGroup -Location $Location -AppServicePlan WebAppwithSQLPlan

#Create a SQL Database Server
New-AzSQLServer -ServerName $ServerName -Location $Location -SqlAdministratorCredentials $SqlServerPassword -ResourceGroupName $ResourceGroup

#Create Firewall Rule for SQL Database Server
New-AzSqlServerFirewallRule -FirewallRuleName "AllowYourIp" -StartIpAddress $StartIP -EndIPAddress $EndIP -ServerName $ServerName -ResourceGroupName $ResourceGroup

#Create SQL Database in SQL Database Server
New-AzSQLDatabase -ServerName $ServerName -DatabaseName MySampleDatabase -ResourceGroupName $ResourceGroup

#Assign the Connection String
Set-AzWebApp -ConnectionStrings @{ MyConnectionString = @{ Type="SQLAzure"; Value ="Server=tcp:$ServerName.database.windows.net;Database=MySampleDatabase;User ID=$Username@$ServerName;Password=$password;Trusted_Connection=False;Encrypt=True;" } } -Name $AppName -ResourceGroupName $ResourceGroup

#Check the result in the portal
 

Now you have used the PowerShell to create an Azure SQL Database and an Azure App (with an connection string)! Congratulations!

 

#Delete all resources (when you no longer need it)
Remove-AzResourceGroup -Name $ResourceGroup -Force

 

I hope this article was useful. Best regards, Tom Wechsler

 

P.S. All scripts (#PowerShell, Azure CLI, #Terraform, #ARM) that I use can be found on github! https://github.com/tomwechsler

 
0 Replies