Contents x
    SQL Server
    • 5 Minutes to read
    • Dark
      Light
    Contents

    SQL Server

    • 5 Minutes to read
    • Dark
      Light
    Article summary

    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 with at least db_datareader and db_datawriter roles.
    • You have configured your network firewall to allow incoming connection from Dataddo IPs.

    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. This process will vary depending on the type of your firewall.
    3. Look for the setting that allows you to define 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.
    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 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 the connection via SSH tunnel. To do so, please follow these steps.
    4. Click on Save.

    Create a New SQL Server Destination

    1. On the Destinations page, click on the Create Destination 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.

    Creating a Flow to SQL Server

    1. Navigate to Flows and click on Create Flow.
    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 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. 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.

    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

    Was this article helpful?
    What's Next