Use PowerShell to create a single database and configure a server-level firewall rule
Applies to: Azure SQL Database
This Azure PowerShell script example creates a single database in Azure SQL Database and configures a server-level firewall rule. After the script has been successfully run, the database can be accessed from all Azure services and the allowed IP address range.
This article uses the Azure Az PowerShell module, which is the recommended PowerShell module for interacting with Azure. To get started with the Az PowerShell module, see Install Azure PowerShell. To learn how to migrate to the Az PowerShell module, see Migrate Azure PowerShell from AzureRM to Az.
Use Azure Cloud Shell
Azure hosts Azure Cloud Shell, an interactive shell environment that you can use through your browser. You can use either Bash or PowerShell with Cloud Shell to work with Azure services. You can use the Cloud Shell preinstalled commands to run the code in this article, without having to install anything on your local environment.
To start Azure Cloud Shell:
|Select Try It in the upper-right corner of a code block. Selecting Try It doesn't automatically copy the code to Cloud Shell.|
|Go to https://shell.azure.com, or select the Launch Cloud Shell button to open Cloud Shell in your browser.|
|Select the Cloud Shell button on the menu bar at the upper right in the Azure portal.|
To run the code in this article in Azure Cloud Shell:
Start Cloud Shell.
Select the Copy button on a code block to copy the code.
Paste the code into the Cloud Shell session by selecting Ctrl+Shift+V on Windows and Linux, or by selecting Cmd+Shift+V on macOS.
Select Enter to run the code.
If you choose to install and use PowerShell locally, this tutorial requires Az PowerShell 1.4.0 or later. If you need to upgrade, see Install Azure PowerShell module. If you are running PowerShell locally, you also need to run
Connect-AzAccount to create a connection with Azure.
# Connect-AzAccount # The SubscriptionId in which to create these objects $SubscriptionId = '' # Set the resource group name and location for your server $resourceGroupName = "myResourceGroup-$(Get-Random)" $location = "westus2" # Set an admin login and password for your server $adminSqlLogin = "SqlAdmin" $password = "ChangeYourAdminPassword1" # Set server name - the logical server name has to be unique in the system $serverName = "server-$(Get-Random)" # The sample database name $databaseName = "mySampleDatabase" # The ip address range that you want to allow to access your server $startIp = "0.0.0.0" $endIp = "0.0.0.0" # Set subscription Set-AzContext -SubscriptionId $subscriptionId # Create a resource group $resourceGroup = New-AzResourceGroup -Name $resourceGroupName -Location $location # Create a server with a system wide unique server name $server = New-AzSqlServer -ResourceGroupName $resourceGroupName ` -ServerName $serverName ` -Location $location ` -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminSqlLogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force)) # Create a server firewall rule that allows access from the specified IP range $serverFirewallRule = New-AzSqlServerFirewallRule -ResourceGroupName $resourceGroupName ` -ServerName $serverName ` -FirewallRuleName "AllowedIPs" -StartIpAddress $startIp -EndIpAddress $endIp # Create a blank database with an S0 performance level $database = New-AzSqlDatabase -ResourceGroupName $resourceGroupName ` -ServerName $serverName ` -DatabaseName $databaseName ` -RequestedServiceObjectiveName "S0" ` -SampleName "AdventureWorksLT" # Clean up deployment # Remove-AzResourceGroup -ResourceGroupName $resourceGroupName
Clean up deployment
Use the following command to remove the resource group and all resources associated with it.
Remove-AzResourceGroup -ResourceGroupName $resourcegroupname
This script uses the following commands. Each command in the table links to command-specific documentation.
|New-AzResourceGroup||Creates a resource group in which all resources are stored.|
|New-AzSqlServer||Creates a server that hosts databases and elastic pools.|
|New-AzSqlServerFirewallRule||Creates a server-level firewall rule for a server.|
|New-AzSqlDatabase||Creates a database in a server.|
|Remove-AzResourceGroup||Deletes a resource group including all nested resources.|
Configure managed identities
For more information on the benefits of using a user-assigned managed identity for the server identity in Azure SQL Database, see User-assigned managed identity in Azure AD for Azure SQL.
To configure the system-assigned managed identity (SMI) or user-assigned managed identity or identities (UMI) of an Azure SQL Database, see Get or set a managed identity for a logical server or managed instance.
For more information on Azure PowerShell, see Azure PowerShell documentation.
Additional SQL Database PowerShell script samples can be found in the Azure SQL Database PowerShell scripts.
Submit and view feedback for