There are many ways in which an Azure SQL database can be created, and if you are new to the development all those different options can be quite intimidating. Let me show you, in just a few steps, how easy it is instead. This is what we are going to do:
- Create a resource group
- Create an Azure SQL logical server
- Create an Azure SQL database
- Configure the firewall
- Create a user
- Get the connection string
I'll be using the Azure Shell portal, as it provides a full experience without the need to install anything on your machine. The only thing you must have ready, is an Azure subscription.
Open a browser and point to https://shell.azure.com and let's get started.
A resource group is a container of Azure resources. It is needed as it simplifies quite a lot the management of those resources. Let's create one in the East US region, and name it
az group create -n dev-demo -l eastus
An Azure SQL logical server is needed to make it easier to manage many databases. A logical server administrator can automatically access any database in the server. Let's create a server named
dev-demo-sql-srv and create an administrator. The administrator user should NEVER be used to allow applications to connect to any database hosted in that server. It is for administrative tasks only.
az sql server create -g dev-demo -n dev-demo-sql-srv --admin-user devdemoadmin --admin-password SomeVery_STRONG_Passw0rd!
Now that you have a server, you can create databases in it. The database will be named
db1 and it will be using a serverless (I know...it's fun. We had to create a server before...) model, so that you'll pay for it only when you use it (auto pause is set by default after 1 hour of no activity):
az sql db create -g dev-demo -n db1 -s dev-demo-sql-srv --service-objective GP_S_Gen5_2
If you plan to use the created database with some other Azure services (like, for example, Azure Functions or Web Apps or Containers) and you don't have extremely high security policies, you can allow Azure services to connect to your database by creating this firewall rule:
az sql server firewall-rule create -g dev-demo -s dev-demo-sql-srv -n AllAzureServices--start-ip-address 0.0.0.0 --end-ip-address 0.0.0.0
Now you need to allow your development machine to connect to Azure SQL. Since you're using the cloud shell, this is really not needed for now, but I'm pretty sure at some point you'll want to use Azure Data Studio or SQL Server Management Studio to connect and manage the database from your machine, and so you need to make sure the firewall will allow such connection. Open a browser and go to https://ipinfo.io/ and get your IP address, and then create a firewall rule using the reported IP for start and end address:
az sql server firewall-rule create -g dev-demo -s dev-demo-sql-srv -n MyIP --start-ip-address 220.127.116.11 --end-ip-address 18.104.22.168
You learned before that the administrative account should never be used to allow applications we create or work with to connect to the database, so we need to create a dedicated user. It's easy. You need to connect to Azure SQL database using the
sqlcmd tool (as you notice you'll be using the administrator login here):
sqlcmd -S dev-demo-sql-srv.database.windows.net -d db1 -U devdemoadmin -P SomeVery_STRONG_Passw0rd!
once you are logged in into the database - you'll see a
1> prompt, you can create a user:
create user [app-user] with password = '4pplication_Passw0rd!'; go
Once the user has been created, you need to give it enough permission to work with the data in the database. If it just needs to read and write from tables, you can assign it the
db_writer roles. If it also needs to create tables or objects, you may want to add it to the
alter role [db_owner] add member [app-user] go
You can exit from the
sqlcmd prompt just by executing the
Please note that users in the db_owner group are basically local administrator so they can do pretty much everything on the database. Security is a complex - but extremely important - topic, make sure you check the basics out here: An overview of Azure SQL Database and SQL Managed Instance security capabilities
To get the connection string that you need to use in your application to connect to Azure SQL, you can just use the following AZ command, where you can also specify for which language or library you want to connection string:
az sql db show-connection-string -s dev-demo-sql-srv -c ado.net
The command will print out the connection string. You just have to replace the values in the angular brackets, for example
<databasename> with your values. Make sure to use the application user you created in the step above, and you'll be good to go!
If you want to learn what are the other ways (using the Portal, Powershell, or AZ CLI), here you can find a detailed article: Quickstart: Create an Azure SQL Database single database
And if you are wondering why you should be looking at Azure SQL, this post is for you: 10 reasons to use Azure SQL in your next project
Photo by Pixabay