Skip to main content

3: Azure SQL Database Setup

Updated

DvSchemaSync synchronizes Dataverse data to an Azure SQL Database. This page covers creating a database and configuring access.

Required Permissions

The permissions required depend on whether you're creating new resources or configuring existing ones:

To create an Azure SQL Database and Server:

• Contributor or SQL DB Contributor role: On the resource group where the database will be created

• Owner role: Required if you also need to assign roles to other users

To configure Microsoft Entra authentication on Azure SQL:

• SQL Server Contributor role: On the SQL Server to set the Microsoft Entra admin

• db_owner role: On the target database to create external users (run as the Entra admin)

To create a SQL Server login (SQL Authentication):

• Server admin credentials: The SQL Server administrator login created when the server was provisioned

• db_owner role: On the target database to create users and grant permissions

💡 Note: If you're using an existing Azure SQL Database managed by your DBA team, provide them with the App Registration details (or request a SQL login) and ask them to grant db_owner permissions on your target database.

Creating an Azure SQL Database

If you don't already have an Azure SQL Database, follow these steps to create one:

  1. Sign in to the Azure Portal (https://portal.azure.com).
  2. Click Create a resource and search for "SQL Database".
  3. Click Create.
  4. Select your Subscription and Resource Group.
  5. Enter a Database name (e.g., "DataverseSync").
  6. For Server, click Create new or select an existing server.
  7. When creating a new SQL Database Server, we recommend you set the new SQL server to “Use Microsoft Entra-only authentication” and set the admin to either yourself or your DBA, then select OK.
  8. Configure compute and storage based on your data volume.
  9. Click Review + create, then Create.

⚠ Important: Although supported, using SQL server credentials is not recommended instead ensure you use Entra AD.

Network Access Options for Remote Users

If you're connecting to Azure SQL from outside your corporate network (home office, travel, etc.), you have several options with different security and convenience trade-offs.

Option 1: VPN + Private Endpoint (Most Secure)

Connect your computer to an Azure VPN Gateway, making it part of the Azure Virtual Network. Access the SQL database via Private Endpoint with no public internet exposure.

Pros: No public exposure; works from any location (home, hotel, coffee shop); enterprise-grade security

Cons: Requires Azure VPN Gateway setup; more Azure networking knowledge needed

Option 2: Public Access + IP Whitelisting (Simplest)

Allow connections from specific IP addresses via firewall rules. Works remotely as long as your public IP is whitelisted.

Pros: Easiest to set up; works from anywhere when IP is whitelisted

Cons: SQL Server exposed to internet; IP changes break connection; must update firewall when switching networks

Option 3: Public Access + Microsoft Entra Authentication (Balanced)

Combines public access with stronger authentication via Entra tokens. Improves security even with a publicly reachable SQL server.

Pros: Strong token-based authentication; works from anywhere; no password-based SQL logins

Cons: Still not as secure as private networking; still requires firewall rules for your IP

What Doesn't Work for Remote Users:

Service Endpoints: These only work inside Azure Virtual Networks. Your laptop cannot directly use them from home or while traveling.

✓ Recommendations:

Maximum security: VPN Gateway + Private Endpoint — works from anywhere while keeping your SQL server completely off the public internet.

Balance of convenience and security: Public access + IP whitelist + Microsoft Entra authentication.

Simplest option: Public access + IP whitelist (least secure, but adequate for non-sensitive development/test environments).

Configuring Firewall Rules (See Network Access Options for Remote Users)

⚠ Important: Azure SQL Database blocks all connections by default. You MUST configure firewall rules before DvSchemaSync can connect.

Your Azure SQL server must allow connections from the computer running DvSchemaSync, if you have your Azure SQL database setup for public access you must whitelist your IP before using the application.

  1. Navigate to your SQL Server (not database) in the Azure Portal.
  2. Select Networking from the left menu under Security.
  3. Under Public access, choose ‘Selected networks’
  4. Under Firewall rules, click Add your client IPv4 address.
  5. Alternatively, add a specific IP range for your organization.
  6. Click Save.

Common Firewall Error:

Cannot open server 'yourserver' requested by the login. Client with IP address 'x.x.x.x' is not allowed to access the server.

This error means your IP address is not in the firewall allowlist. Add the IP address shown in the error message to the firewall rules.

💡 Tip: If your IP address changes frequently (e.g., home network, VPN), consider using " VPN + Private Endpoint" for production environments.

Configuring Microsoft Entra Authentication

⚠ Required for App Registration: We recommend you use Microsoft Entra App Registration to authenticate to Azure SQL (instead of SQL username/password), you MUST set up a Microsoft Entra admin on your SQL Server first. Without this, the App Registration cannot authenticate.

Step 1: Set a Microsoft Entra Admin on the SQL Server

  1. Navigate to your SQL Server in the Azure Portal.
  2. Select Microsoft Entra ID from the left menu under Settings.
  3. Ideally, check the ‘Support only Microsoft Entra authentication for this server’ option
  4. Click Set admin and select a user or group as the Entra admin.
  5. Click Save.

Step 2: Create a Database User for Your App Registration

  1. Connect to the database using the Entra admin account (e.g., via Azure Data Studio or SSMS).
  2. Run the following SQL to grant access to your App Registration:

-- Replace 'YourAppName' with your App Registration's display name

CREATE USER [YourAppName] FROM EXTERNAL PROVIDER;

ALTER ROLE db_owner ADD MEMBER [YourAppName];

Common Entra Authentication Error:

Login failed for user '<token-identified principal>'. The server is not currently configured to accept this token.

This error means no Microsoft Entra admin has been set on the SQL Server. Complete Step 1 above to resolve.

💡 Tip: The App Registration name used in CREATE USER must match exactly. Find the display name in Azure Portal → App registrations → Your app → Overview.

Recording Your Connection Details

You'll need the following information for DvSchemaSync:

Server name: Found on the Overview page (e.g., yourserver.database.windows.net)

Database name: The name you specified when creating the database

Authentication: Either SQL credentials (username/password) or Microsoft Entra App Registration details

Related to