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.
IMPORTANT: The following are just guidelines. Please engage an Azure expert to set up your Azure resources and settings for data warehousing. Contact us for assistance/guidance.
Creating a new Database for Data Warehousing Purposes
While there are reasons to set your data warehouse(s) in existing Azure SQL databases, we recommend creating a clean new one for your data warehousing.
Creating the new 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 = '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