Create Server and Database
Despite the promise of a ‘fully managed cloud database’ – the concept of a server is still used by all the cloud providers I’ve seen. Azure is no exception, to create an Azure SQL Database, you must create an Azure SQL Server. This is more of a logical grouping rather than an actual server in the traditional sense. It provides a good way to assign resources (like ram and cpu) to a database, and provides a place for the system databases (master,model,msdb/postgres) to live.
Your Azure subscription will have a subscription administrator account, which is most likely your Microsoft account. This account (as with all ‘azure guest accounts’) is not supported for Azure AD (AAD) authentication. So, you have to create a user in the directory associated with your subscription.
In my case, the directory i created is
mwild.onmicrosoft.com. I created a new user for my admin purposes, and added this to the
Administrators group. Then configure the Azure SQL Server ‘Active Directory admin’. I mapped this to the
Administrators group. (You would probably create a ‘DBA’ group in real deployment scenarios). Now test you can login to the database using the admin user you created using SSMS/LINQPad/etc. (Connection strings are available on the database page in the Azure Portal).
Assuming everything went well, we now have a database server and database, and we can login with an AAD account.
Create a service account in AAD for the application that wants to talk to your database. In my case this is something like `[email protected]
. Then we can create a __contained database user__ for the service account. This is basically just a sqlUSER
in a database, with no sqlLOGIN` in master. I also just add read/write permission here as they will not be saved in source control.
USE <db_name>; CREATE USER [[email protected]] FROM EXTERNAL PROVIDER; ALTER ROLE [db_datareader] ADD MEMBER [[email protected]]; ALTER ROLE [db_datawriter] ADD MEMBER [[email protected]];
You can manage schema changes in your database however you want.
I usually use flyway for simple schema migration deployments, all you need to do is create a folder and put some sql scripts inside, then run
flyway migrate e.g.
$ tree my_project/ sql/ V1__baseline.sql V2__create_users_table.sql $ flyway migrate
Another option is to use SSDT in Visual Studio. You write scripts for each object in your database, then click “Publish” in VS.
$ tree my_db_project/ tables/ users.sql views/ accounts.sql my_db_project.sqlproj # right click --> publish
Both of these options allow you to keep all sql changes in source control, as well as easily target a local db for development.