Azure SQL for Data Warehousing

Organizations interested in or using Microsoft cloud products for reporting and data analysis, such as Azure, Excel, and Power BI, will find Azure SQL to be 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.

System Requirements

Click here for the recommended system requirements.

Options: Azure SQL Database or Azure SQL Managed Instance

Azure SQL Database works well for most clients and is cost-effective.

Azure SQL Managed Instance is better when clients need multiple DBs with cross-database transactions, and other SQL instances’ features, such as Linked Servers and SQL Agents.

Azure SQL is usually enough for most DataSelf systems. Azure SQL Managed Instance is a safer bet if you want to avoid a potential need to re-architect in the future, and are Ok with the additional cost. Re-architecting usually takes hours.

Creating the new Azure SQL Database for Data Warehousing

We recommend creating a clean new Azure SQL database to host your data warehouse.

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 = 'create_a_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

Related Pages

• DataSelf ETL+ Desktop Minimum System Requirements