Azure SQL for Data Warehousing

Organizations interested in or using Azure, Office 365, and other Microsoft cloud products will find Azure SQL is an affordable, low-maintenance, and fully managed database platform well-suited for data warehousing. Azure SQL also provides excellent ways to cross-pollinate its data with other Microsoft services such as Power BI, Power Apps, Data Lake, and Fabric.

Creating a new Database for Data Warehousing Purposes

While there are reasons to set your data warehouse(s) in existing Azure SQL databases, in most cases, we recommend creating a clean new environment for your data warehousing.

Azure SQL Database or Azure SQL Server

You might use an Azure SQL Database (stand-alone database) or an Azure SQL Server instance (a server to host multiple databases) with at least one Azure SQL Database.

Azure SQL Database: Setting up and maintaining is easier and faster. In the long run, assuming your data warehouse initiative grows (which is likely), you might need to migrate this database to an Azure SQL Server.

Azure SQL Server: This approach simplifies consolidating databases and managing users and resources.

Creating the new Azure SQL Server and/or Azure SQL Database

There are many details to consider when creating a new resource in Azure. The following are guidelines required or recommended by DataSelf as of early 2024:

• Global Administrator or similar rights on your Azure tenant.

• An Azure Subscription and Resource Group to attach your new database to.

• For Authentication, select “Use both SQL and Microsoft Entra authentication”.

• Do not set up “Enable Microsoft Defender for SQL” unless you understand in detail the implication of this choice.

• Please consult Azure documentation for more information or contact DataSelf.

Creating Service Account Users

Create SQL users for external applications such as DataSelf ETL+ to read & write to the new database(s):

• Connect to your Azure SQL via SSMS using Global Admin credentials.

• Right-click the data warehouse DB → New Query.

• Copy/paste the query below to create the new user with read/write access to the new data warehouse, change the user and password accordingly, and click F5 to execute:

  • CREATE USER [new_username]
    WITH PASSWORD = 'complex_pw',
    DEFAULT_SCHEMA = dbo;

    ALTER ROLE db_ddladmin ADD MEMBER [new_username];

  • Note, for read-only users, replace db_ddladmin with db_datareader in the last line above.

Granting Access to External Users

• Log to https://portal.azure.com with the Global Admin user.

• Go to Microsoft Entra ID → Users → New User > Invite external user → fill out the form → Invite

• Ask users to accept the Azure invitation email.

• Ask users to inform you what their fixed public IP address is.

• To add the external users to an Azure SQL DB: Log in to SSMS with the Global Admin credentials → select the dB → New Query:

  • CREATE USER [user@email.com] FROM EXTERNAL PROVIDER;

  • GRANT CONTROL ON SCHEMA::SchemaName TO [user@email.com];

• Unless you’re giving the external users access to your VPN or similar, whitelist the external users' IP:

  • Ask for their public IP addresses.

  • Look for the Networking → Firewall Rules in your Azure SQL or SQL Server, and add a Firewall rule to whitelist the users' IPs.

• Inform the users to log in to Azure SQL via SSMS (similar to other tools):

  • Server name: look for your Server name entry in your Azure SQL or SQL Server.

  • Authentication: Microsoft Entra MFA for people, SQL Authentication for service accounts.

  • Username and password: their email address

  • Options → Connect to database: <EnterDbName>

  • Connect

  • Enter the password