Question 247172 in Azure Question posted in
The official documentation to get you started can be found here.

Powershell script Azure SQL DB firewall

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

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    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

  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search