1

New to Azure. I'm attempting to mimic a manual setup through a script run from CloudShell setting up an Azure SQL database. I've used this site as a starting point: https://www.sqlshack.com/provisioning-azure-sql-database-using-azure-powershell/

I want to script out:

  1. A public endpoint as the connectivity method
  2. Set "Allow Azure services and resources to access this server" to yes
  3. Set "Add current client IP address" to yes

I'm not familiar with Azure, firewalls so please be gentle.

enter image description here

Improve this question

2 Answers 2

Reset to default
1

You can run the PowerShell script below to enable a public IP address in Azure SQL database:

Import-Module Az.Accounts
Import-Module Az.Sql 
Connect-AzAccount
Select-AzSubscription -SubscriptionId "<subscriptionId>"
$SecureString = ConvertTo-SecureString "<adminPassword>" -AsPlainText -Force
Set-AzSqlServer -ServerName "<serverName>" -ResourceGroupName "<RGName>" -SqlAdministratorPassword $SecureString -PublicNetworkAccess "Enabled"

You can see the result as shown below:

enter image description here

And the public network access is enabled as mentioned below:

enter image description here

As per this, if you add a firewall rule with 0.0.0.0 as the start and end IP address to the SQL server, then "Allow Azure services and resources to access this server" is set to yes. Run the script below to set the firewall rule:

az sql server firewall-rule create --resource-group "<RGName>" --server "<serverName>" -n "<firewallRuleName>" --start-ip-address 0.0.0.0 --end-ip-address 0.0.0.0

You can see the result as mentioned below:

enter image description here

And "Allow Azure services and resources to access this server" is set to yes, as mentioned below:

enter image description here

You can run the script below to add the current IP address to the SQL server. Invoke 'https://api.ipify.org' to get the current IP, as mentioned below:

$ipAddress = Invoke-WebRequest 'https://api.ipify.org' | Select-Object -ExpandProperty Content    
az sql server firewall-rule create --resource-group "<RGName>" --server "<serverName>" -n "<firewallRuleName>" --start-ip-address $ipAddress --end-ip-address $ipAddress

You can see the result as mentioned below:

enter image description here

It will add the current IP address to the firewall rules, as mentioned below:

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you for being the guiding light leading me out of the darkness! Virtual "bro" hug sent. To add to the common knowledge the complete script I used to meet my needs is included. I made 1 change to address logging into Azure (???). Thanks again!!!
0

  1. Read more:
    https://www.sharepointdiary.com/2021/07/how-to-connect-to-azure-ad-using-powershell.html#ixzz8D6vt9t6V

    https://www.sqlshack.com/provisioning-azure-sql-database-using-azure-powershell/

link to az sql server firewall-rule:

https://learn.microsoft.com/en-us/cli/azure/sql/server/firewall-rule?view=azure-cli-latest

link to Set-AzSqlServer:
https://learn.microsoft.com/en-us/powershell/module/az.sql/set-azsqlserver?view=azps-10.2.0

 #This section is used in place of Connect-AzAccount or Connect-AzAccount -UseDeviceAuthentication   $AdminUserName =
    "MySubscriptionName" $AdminPassword = "MySubscriptionPassword"   
    #Variable for Pscredential object $SecurePassword = ConvertTo-SecureString $AdminPassword -AsPlainText -Force
    $Credential = New-Object System.Management.Automation.PSCredential
    -argumentlist $AdminUserName, $SecurePassword
       
    #Connect to Azure Active Directory Connect-AzureAD –Credential $Credential
    
    
    # Set variables for your server and database $resourceGroupName = "MyResourceGroupName" $location = "MyLocationToCreateDBServer"
    $adminSqlLogin = "MydbAdmin" $adminpassword = "MyDBAdminPassword"
    $databaseName = "MyDBName" $serverName = "MyDBServer" $subscription
    = "MySubscriptionID"
    #Used when building firewall rules $azureservicesfirewallname = "AzureServices" $currentIPfirewallname = "MyIPAddress"
    
    #Creates the Azure SQL Server  $server = New-AzSqlServer -ResourceGroupName $resourceGroupName -ServerName $serverName -Location $location -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminSqlLogin, $(ConvertTo-SecureString -String $adminpassword
    -AsPlainText -Force))
    
    # Creates Azure SQL database $database = New-AzSqlDatabase  -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -DatabaseName $databaseName `
    -RequestedServiceObjectiveName "S0" `
    -SampleName "AdventureWorksLT"
    
    # Enable a public IP address in Azure SQL database Import-Module Az.Accounts Import-Module Az.Sql 
    #Connect-AzAccount
    #Connect-AzAccount -UseDeviceAuthentication
    
    Select-AzSubscription -SubscriptionId $subscription $SecureString =
    ConvertTo-SecureString $adminpassword -AsPlainText -Force
    Set-AzSqlServer -ServerName $serverName -ResourceGroupName
    $resourceGroupName -SqlAdministratorPassword $SecureString
    -PublicNetworkAccess "Enabled"
    
    # Allows Azure services and resources to access this server az sql server firewall-rule create --resource-group $resourceGroupName
    --server $serverName -n $azureservicesfirewallname --start-ip-address 0.0.0.0 --end-ip-address 0.0.0.0
    
    # adds the current IP address to access the SQL server $ipAddress = Invoke-WebRequest 'https://api.ipify.org' | Select-Object
    -ExpandProperty Content     az sql server firewall-rule create --resource-group $resourceGroupName --server $serverName -n $currentIPfirewallname --start-ip-address $ipAddress
    --end-ip-address $ipAddress
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.