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.

Prerequisites

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 you will use for writing or reading the 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

Creating a Flow to SQL Server

Schema specification

While configuring the flow, it's essential to specify both the schema and the table name in the Table Name field. If the schema you are targeting is my_schema, and the table is my_table, then you should input my_schema.my_table as the table name. Failing to do so will cause Dataddo to use dbo as the default schema for writing. Ensure that you have the appropriate permissions to write to the specified schema.

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