Page Comparison

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

...

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: It Setting up and maintaining is easier and faster to set up and maintain. In the long run, assuming your data warehouse initiative grows (which is likely in most cases), you might need to migrate this database to an Azure SQL Server.

Azure SQL Server: We recommend this This approach because it simplifies consolidating reporting databases and managing users and resources.

...

• Global Administrator or similar rights on your Azure tenant.

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

• To create the new resource, use the search box in Azure for:

  • “Azure SQL” and follow the prompts. Read Notes below.

  • “SQL Servers” and follow the prompts. Read Notes below. After the SQL Server is created, click Create Database and follow the prompts.

• Notes:

  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

  .Overall, using Azure default options works well for data warehousing purposes

  .

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

Creating Service Account Users

...

• 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 run itexecute:

  • 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.

...

• 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, you’ll need to 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 DB 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 a userpeople, SQL Authentication for a service accountaccounts.

  • Username and password: their email address

  • Options → Connect to database: <EnterDbName>

  • Connect

  • Enter their the password