---
title: "Azure SQL Database"
slug: "azure-sql-database"
description: "Send your data from any source to Azure SQL Database easily with Dataddo! Create secure and reliable data flows. Get started with your data insights now."
updated: 2024-02-11T09:52:43Z
published: 2024-02-11T09:52:43Z
---

> ## Documentation Index
> Fetch the complete documentation index at: https://docs.dataddo.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Azure SQL Database

**Azure SQL Database** is a fully managed relational database service offered by Microsoft Azure. It provides a cloud-based platform for hosting and managing SQL Server databases, allowing users to build, scale, and secure applications without the need to manage underlying infrastructure, making it an efficient and flexible solution for various data-driven applications in the cloud.

## Prerequisites

          
          

- You have a running instance of SQL Database.
- You have configured firewall settings to accept incoming connection from [Dataddo IPs](/docs/network-acl).
- You have [created a database user](/docs/azure-sql-database#in-azure-sql-database) with `CREATE`, `SELECT`, `UPDATE`, `INSERT`, `DELETE` permissions.

## Authorize Connection to Azure SQL Database

### In Azure Portal

#### Create an Instance of SQL Database

1. In the Azure portal, select **Create a resource** from the menu on the left.
2. Search for **SQL Database** and click on **Create**.
3. Select **Subscription**, **Resource Group**, and enter your **Database name**.
4. Select your **Server** (if you already have another SQL Database running) or create a new one.
5. In the **Networking** section
  1. Choose **Public endpoint** for connectivity method.
  2. Select yes for **Allow Azure services and resources to access this server**. The firewall will be configured in the next steps.
6. Review the remaining settings, adjust as necessary, and click on **Review + create**.

#### Configure the Firewall Settings

1. In the Azure portal, select **SQL databases** and choose the database you created in the previous step.
2. In the **Set server firewall** section, add each of [Dataddo IPs](/docs/network-acl).

### In Azure SQL Database

After you've set up your Azure SQL database, you need to **create a user** who can interact with the database on behalf of Dataddo. This can be done using the **SQL Server Management Studio** or **Azure Data Studio**.

Remember, it's important to regularly review your security settings and follow best practices to ensure your data is protected. Always consider your organization's security policies when setting up users and permissions.

#### Create a Database User

1. Connect to your database with the admin account.
2. Run the command below to create a new user.

```
CREATE USER dataddo WITH PASSWORD = '***';
```

#### Grant Permissions to User

1. Connect to your database with the admin account.
2. Run the commands below to grant **CREATE**, **SELECT**, **INSERT**, **UPDATE**, and **DELETE** permissions to Dataddo.
3. Run command to grant **ALTER ANY EXTERNAL DATA SOURCE** and **ADMINISTER BULK OPERATIONS** permissions to Dataddo. This is required in order to speed up the data insertion process as Dataddo is using Blob Storage for bulk data insertion.

```
GRANT CONNECT SQL TO dataddo;
GRANT SELECT, INSERT, UPDATE, DELETE TO dataddo;
GRANT CREATE TABLE TO dataddo;
GRANT ALTER ANY EXTERNAL DATA SOURCE TO dataddo;
GRANT ADMINISTER BULK OPERATIONS TO dataddo;
```

### In Dataddo

1. On the **Authorizers** page, click on [**Authorize New Service**](https://app.dataddo.com/service/new) and select **Azure SQL**.
2. You will be asked to fill the following fields.
  1. **Server IP or Hostname**: Public hostname of the Azure SQL database. You can find this under **Server name** in the Azure portal.
  2. **Database (Catalog)**: The database you want to use for reading or writing the data. Sometimes this might be referred to as **Catalog**.
  3. **Username**: Username of your database user.
  4. **Password**: Password to your database user.
  5. **Port**: Port to connect to database. The default is **1433**.
3. After filling-in all the details, click on **Save** to save the authorization details.

## Create a New Azure SQL Database Destination

1. On the **Destinations** page, click on the [**Create Destination**](https://app.dataddo.com/destinations) button and select the destination from the list.
2. Select your ***authorizer*** from the drop-down menu.
3. Name your ***destination*** and click on **Save**.

          Need to authorize another connection?

          

Click on **Add new Account** in drop-down menu during ***authorizer*** selection and follow the on-screen prompts. You can also go to the **Authorizers** tab and click on [**Add New Service**](https://app.dataddo.com/service/new).

## Create a Flow to Azure SQL Database

1. Navigate to **Flows** and click on [**Create Flow**](https://app.dataddo.com/flow/new).
2. Click on **Connect Your Data** to add your ***source(s)***.
3. Click on **Connect Your Data Destination** to add the ***destination***.
4. Choose the [write mode](https://docs.dataddo.com/docs/data-storages#write-modes) and fill in the other required information.
5. Check the **Data Preview** to see if your configuration is correct.
6. **Name** your flow and click on **Create Flow** to finish the setup.

### Table Naming Convention

When naming your table, please make sure the table name contains only:

- Letters
- Numbers
- Underscores

Also please keep in mind that when the **Microsoft Access driver** is used, the table name is **limited to 64 characters**.

---

## Troubleshooting

### Truncate Insert Error

The truncate insert [write mode](/docs/data-storages#write-modes) is not possible if your data is

- **Published for replication** or
- **Enabled for change data capture (CDC)**.

If you try to use truncate insert in these cases, you will receive the following error:

```
stream transfer: write data from stream: prepareTx: truncate table if requested: Failed to truncate table: mssql: Cannot truncate table ‘table_name' because it is published for replication or enabled for Change Data Capture.
```

For mor information on cases when truncate insert is not possible, refer to [Microsoft's official documentation](https://learn.microsoft.com/en-us/sql/t-sql/statements/truncate-table-transact-sql?view=sql-server-2017#restrictions).

### Inserting data takes too long

Check the permissions and make sure that **ALTER ANY EXTERNAL DATA SOURCE** was grated. This is required in order to speed up the data insertion process as Dataddo is using Blob Storage for bulk data insertion.

## Related Articles

- [Data Backfilling to Storages](https://docs.dataddo.com/docs/data-backfilling-to-storages)
- [Write Modes](https://docs.dataddo.com/docs/data-storages#write-modes)
- [Implementation of Batch Ingestion to Data Warehouses](https://docs.dataddo.com/docs/ingestion-to-data-warehouses)
- [Network Access Control List (ACL) Configuration](https://docs.dataddo.com/docs/network-acl)
- [SSH Tunnelling](https://docs.dataddo.com/docs/ssh-tunnelling)
- [Data Transformations](https://docs.dataddo.com/docs/data-transformations)
- [Data Quality Firewall](https://docs.dataddo.com/docs/data-quality-firewall)

Truncate insert deletes all existing data in a table before inserting the new data. This ensures that the table only contains the most recently extracted data. It is useful in scenarios where only the latest dataset is relevant.