--- title: "How to Connect SQL Server" slug: "universal-sql-server" description: "Learn how to connect your data to an SQL Server with Dataddo and create reliable data flows. Get help with setup and configuration of data integration." updated: 2025-08-06T20:21:56Z published: 2025-08-06T20:21:56Z --- > ## 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. # SQL Server **SQL Server** is a relational database management system developed by Microsoft. It enables users to store, retrieve, and manage structured data using the SQL (Structured Query Language) for querying and manipulating data, making it a powerful tool for various data-driven applications and business operations. **DATADDO TIP** Dataddo supports **SQL Server** as both a cloud-managed and an on-premises source or destination. ## Prerequisites - You have a running instance of SQL server with public IP or hostname. - You have configured [an SQL Server user](/docs/universal-sql-server#in-sql-server) with at least `db_datareader` and `db_datawriter` roles. - You have configured your network firewall to allow incoming connection from [Dataddo IPs](/docs/network-acl). ## Authorize Connection to SQL Server ### In SQL Server Please keep in mind that this guide provides **general instructions**, and your individual setup might need a different approach or some extra steps. **Always take your organization's security guidelines into account when you're making these changes**. Additionally, it's a good idea to seek assistance from a database administrator or an IT professional to make sure everything is set up correctly. #### Create an SQL Server User 1. Open **SQL Server Management Studio (SSMS)** and connect to your server. 2. In the **Object Explorer** section, expand your server name, then expand the **Security** folder. 3. Right-click the **Logins** folder and select **New Login**. 4. Enter a login name, choose **SQL Server authentication**, and provide a password. 5. Uncheck the **Enforce password policy** and **Enforce password expiration** options if you don't want these policies to apply to this user. 6. In the **Default database** box, select the database you want the user to connect to by default. Then click **OK** to create the user. #### Set Permissions 1. In the **Object Explorer** section, expand the **Databases** folder and navigate to your database. 2. Expand the **Security** folder of the database, then right-click the **Users** folder and select **New User**. 3. Enter the same user name you created earlier, and in the **Login name** box, click on the three dots and select the corresponding login. 4. In the **Database role membership** section, check the roles you want to assign to this user. For read and write access, you willl typically want to select the `db_datareader` and `db_datawriter` roles. 5. Click **OK** to create the database user. #### Allow Network Connection For this destination, whitelisting IP addresses is not available in the database system. Instead, you can configure the network access **at the firewall level**. Here are the general steps: 1. **Identify the firewall** that's protecting your service. This could be a Windows or Linux firewall running on the server itself, or it could be a network firewall. 2. Open the **firewall's configuration settings**. The settings location will vary depending on your firewall type. 3. Look for the **setting** that allows the **definition of inbound rules or policies**. 4. Create **a new rule or policy** that allows inbound traffic on the port that the service is using (the default is **1433**) from the [Dataddo IP addresses](/docs/network-acl). 5. **Save** your changes and **test the connection from Dataddo** to make sure everything is working as intended. ### In Dataddo 1. In the **Authorizers** tab, click on [Authorize New Service](https://app.dataddo.com/service/new) and select **Universal MSSQL Server**. 2. You will be asked to fill the following fields: 1. **Server IP or Hostname**: Public IP or hostname of your SQL Server. 2. **Instance name**: Use only in case the server hosts a multiple instances named SQL Server, otherwise leave empty. 3. **Database**: Name of the database used for writing or reading data. 4. **Username**: Username of your SQL Server user. 5. **Password**: Password to your SQL Server user. 6. **Port**: Port to connect to SQL Server. The default value is **1433**. 3. [Optional] Configure your destination using an existing SSH tunnel or [create a new one](/docs/ssh-tunnelling). 4. Click on **Save**. ## Create a New SQL Server 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). ## Creating a Flow to SQL Server 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. ### Schema specification While configuring the flow, it's essential to **specify both the schema and the table name** in the **Table Name** field. Make sure that you have the necessary permissions to write data to the specified schema. **Example:** - Target schema name: `my_schema` - Target table name: `my_table` - Table name in Dataddo: `my_schema.my_table` If you don't specify schema and table name, Dataddo will use `dbo` as the default schema for writing. Dataddo does not create schemas automatically. Make sure **the target schema exists** and that you have the **necessary write permissions**. ### Table Naming Convention When naming your table, please make sure the table name: - Starts with a letter. - Contains only: - Letters, - Numbers, - Underscores, - Dashes. - Does **NOT** contain whitespaces. --- ## Troubleshooting ### Schema Error ``` The specified schema name ... either does not exist or you do not have permission to use it. ``` This issue might arise if you're trying to write data to an incorrect or unauthorized schema. 1. **Verify Schema in Table Name**. Review the table name in the corresponding flow to ensure [that it includes the schema name](/docs/universal-sql-server#schema-specification). If not, enter a schema name for which you have the appropriate write permissions. 2. **Check Default 'dbo' Schema Permissions**. If you're using the default 'dbo' schema, confirm that the user account you are using for authorization [has the required permissions to write data](/docs/universal-sql-server#in-sql-server). ### Identity insert error ``` Failed to perform SQL batch insert: run insert: mssql: Cannot insert explicit value for identity column in table 'table_name' when IDENTITY_INSERT is set to OFF ``` This issue is caused by the fact that for the given table identity insert is turned off. You can fix it by running the following command in your SQL server. Make sure to replace **table_name** for the actual name of your SQL server table. ``` SET IDENTITY_INSERT table_name ON ``` ## 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)