Chapter 5. Datasinks

Datasinks are the counterpart of datasources in ReportServer. Reports can be sent to any datasink defined, e.g. to a FTP or SFTP datasink. Reports may be sent directly to a datasink (via the SendTo button) or scheduled to be sent to a specific datasink. Multiple datasinks of the same type are allowed, analogously as datasources. Further, as with most other objects in ReportServer, datasinks are maintained in a hierarchical structure. The datasink management module can be found in the Administration module under Datasinks. The following object types can be created in the tree:

• Folder Serve to structure datasinks.
• Datasink Here various datasinks are optionally available which we will discuss in more detail in the following.

Datasinks will be configured in two steps. In datasink management, datasinks will be created and the basic settings made. For FTP datasinks, here, for instance, user name, password, default folder and FTP URL are stored. However, the specific configuration per use will be set at the point where the datasink will be used (this is mostly with the respective report). Here, for instance, for a FTP datasink, the specific folder can be set where the report should be stored. The default folder can be used or a new folder can be defined for the specific use.

Datasinks may either completely disabled, or disabled for a specific type, or scheduling may be disabled for this type. Disabling datasinks may be done in /fileserver/etc/datasinks/datasinks.cf. Details on this can be found in the Configuration Guide.

ReportServer supports the following datasinks.

• Email - SMTP
• Table datasinks (available in Enterprise Edition)
• SFTP
• FTPS
• FTP
• Samba - SMB/CIFS (available in Enterprise Edition)
• Amazon S3 (available in Enterprise Edition)
• SCP (available in Enterprise Edition)
• Local filesystem (available in Enterprise Edition)
• Printer (available in Enterprise Edition)
• Script datasinks (available in Enterprise Edition)
• OAuth2-authenticated datasinks:
  • Dropbox (available in Enterprise Edition)
  • OneDrive - SharePoint (O365) (available in Enterprise Edition)
  • Google Drive (available in Enterprise Edition)
  • Box (available in Enterprise Edition)

Email SMTP datasinks enable the sending and scheduling of reports via an SMTP server. The configuration for an Email SMTP datasink can be managed within the Administration area, and its permissions are administered similarly to other objects in ReportServer.

To allow ReportServer to send emails, you need to specify the appropriate mail server settings. Configure your Email SMTP datasink as follows:

Setting up the SMTP server. Provide the necessary values for host, port, username, and password according to your SMTP server's configuration.

Host: mail.yourmailserver.com
Port: 25
Username: rs@yourmailserver.com
Password: passwordsecret
SSL: false
TLS enable: false
TLS require: false

If you are using SSL or TLS please also specify these values. Next, configure the sender name, email address and forceSender options. If the forceSender option is set to true, the emails will be sent using the given (generic) sender details. If set to false, the specific user sending the email will determine the sender details.

Sender: rs@yourmailserver.com
Sender name: ReportServer
Force sender: false
Encryption policy: allow_mixed

The encryption policy option controls whether or not mails have to be encrypted or whether it is ok to send mails unencrypted if a user's public key is not specified. Choose between strict and allow_mixed. Note that if you choose strict then mails to users that do not have public key registered with ReportServer will not receive any messages.

Note that you can specify a default datasink per datasink type in the /etc/datasinks/datasinks.cf configuration file. Details can be found in the Configuration Guide.

Table datasinks allow you to send and/or schedule dynamic list reports into to a given table in any datasource supported by ReportServer. This allows you to easily transfer data from MySQL to Oracle, for example. The destination table must exist and must be compatible with the dynamic list, i.e. the fields must exist, have the same name, and have compatible data types.

Table datasink configuration can be made in the Administration area. Its permissions can be administrated analogous as other objects in ReportServer.

In order to configure a table datasink, the following must be set:

• Datasource The datasource to send the data to.
• Destination table name The table name. This table must exist in the given datasource, be reachable, and compatible to the dynamic list transfering the data.
• ;-separated list of primary keys in destination table The destination table may of course contain one or more primary keys. These can be entered here, separating them by '';''. For example, if your destination table has one primary key ''ENTITY_ID'', you can enter ''ENTITY_ID''. If your destination table has two primary keys ''ENTITY_ID'' and ''ENTITY_ID2'', you can enter ''ENTITY_ID;ENTITY_ID2''.
• Copy primary keys If your destination primary keys are of autoincrement-type, their values should not be copied to the destination table, as your DB calculates the ids automatically. In this case, you should not set this checkbox. However, if you want to copy the primary key values, you should set this checkbox.
• Batch size The table is copied in batches for performance reasons. You may adapt the batch size here.

When you schedule a dynamic list to a table datasink, you should select ''Stream table'' as an export type.

Note that you can specify a default datasink per datasink type in the /etc/datasinks/datasinks.cf configuration file. Details can be found in the Configuration Guide.

SFTP datasinks allow to send and/or schedule reports to a SFTP server. SFTP datasink configuration can be made in the Administration area. Permissions to SFTP datasinks can be given analogously as to other objects in ReportServer.

The host can be entered using the following format:

sftp://sftpHost

Currently, ReportServer supports the following authentication methods:

• Username Password Authentication
• Public Key Authentication

All passwords and private keys are encrypted and saved into the ReportServer database. Note that the private key passphrase may be empty, but it is highly recommended to use a passphrase.

Further, note that for SFTP to work, you have to add your SFTP host to your /etc/ssh/known_hosts file (https://en.wikibooks.org/wiki/OpenSSH/Client_Configuration_Files#~/.ssh/known_hosts) in order to verify the identity of the remote host, thus protecting against impersonation or man-in-the-middle attacks. Its location can be configured in the /fileserver/etc/security/misc.cf file as described in the Configuration Guide. For manually adding a public key to the /etc/ssh/known_hosts file, check here: https://en.wikibooks.org/wiki/OpenSSH/Client_Configuration_Files#Manually_Adding_Public_Keys_to_~/.ssh/known_hosts.

E.g.: Use ssh-keyscan IP >> known_hosts in order to add the given IP to your known_hosts file.

Note that if the (extension) folder/folders does/do not exist, it/they is/are created. If the report already exists in the same path, it is overwritten.

FTPS datasinks allow to send and/or schedule reports to a FTPS server. FTPS datasink configuration can be made in the Administration area. Permissions to FTPS datasinks can be given analogously as to other objects in ReportServer.

The host can be entered using the following format:

ftps://sftpHost

Currently, ReportServer supports the following authentication method:

• Username Password Authentication

All passwords are encrypted and saved into the ReportServer database.

Note that if the (extension) folder/folders does/do not exist, it/they is/are created. If the report already exists in the same path, it is overwritten.

FTP datasinks allow to send and/or schedule reports to a FTP server. FTP datasink configuration can be made in the Administration area. Active and passive FTP modes are supported. Permissions to FTP datasinks can be given analogously as to other objects in ReportServer.

The host can be entered using the following format:

ftp://ftpHost

Note that if the (extension) folder/folders does/do not exist, it/they is/are created. If the report already exists in the same path, it is overwritten.

Samba - SMB/CIFS datasinks allow to send and/or schedule reports to a given Samba server using SMB/CIFS protocols. Samba - SMB/CIFS datasink configuration can be made in the Administration area. Permissions to local Samba - SMB/CIFS datasinks can be given analogously as to other objects in ReportServer.

The host can be entered using the following format:

smb://sambaHost

Note that the path must exist and must be writable by your Samba's user. If the report already exists in the same path, it is overwritten.

If you get the error ''SmbException: The network name cannot be found.'', you should probably add or correct your domain name or check if the folder is correct. Note that the folder must be complete, e.g. if your Samba share is ''myshare'', and you have a directory ''temp'' inside this share, your folder should be ''myshare/temp''.

Amazon S3 datasinks allow to send and/or schedule reports to a given S3 bucket. The datasink configuration can be made in the Administration area. Permissions to local S3 datasinks can be given analogously as to other objects in ReportServer.

You can create an access and a secret key as described here: https://docs.aws.amazon.com/general/latest/gr/aws-sec-cred-types.html#access-keys-and-secret-access-keys. Further, the unique bucket name and region must be entered into the datasink definition.

If the report already exists in the same path, it is overwritten.

More information can be found here: https://docs.aws.amazon.com/en_en/AmazonS3/latest/userguide/Welcome.html.

SCP datasinks allow to send and/or schedule reports to a given SCP (SSH) server. SCP datasink configuration can be made in the Administration area. Permissions to local SCP datasinks can be given analogously as to other objects in ReportServer.

The host can be entered using the following format:

scp://scpHost

Note that the path must exist and must be writable by your SCP user. An absolute destination folder is required, so relative paths like "./" do not work here. If the report already exists in the same path, it is overwritten.

Currently, ReportServer supports the following authentication methods:

• Username Password Authentication
• Public Key Authentication

All passwords and private keys are encrypted and saved into the ReportServer database. Note that the private key passphrase may be empty, but it is highly recommended to use a passphrase.

Further, note that for SCP datasinks to work, you have to add your SCP server host to your /etc/ssh/known_hosts file (https://en.wikibooks.org/wiki/OpenSSH/Client_Configuration_Files#~/.ssh/known_hosts) in order to verify the identity of the remote host, thus protecting against impersonation or man-in-the-middle attacks. Its location can be configured in the /fileserver/etc/security/misc.cf file as described in the Configuration Guide. For manually adding a public key to the /etc/ssh/known_hosts file, check here: https://en.wikibooks.org/wiki/OpenSSH/Client_Configuration_Files#Manually_Adding_Public_Keys_to_~/.ssh/known_hosts.

E.g.: Use ssh-keyscan IP >> known_hosts in order to add the given IP to your known_hosts file.

Local filesystem datasinks allow to send and/or schedule reports to a given directory of your local filesystem, i.e. where your ReportServer is installed. Local filesystem datasink configuration can be made in the Administration area. Permissions to local filesystem datasinks can be given analogously as to other objects in ReportServer.

You can enter a path to a given folder in your filesystem as follows:

/users/myuser/myreports

in Linux/Mac, and

C:/myuser/myreports

in Windows.

The path in the datasink definition determines the base path of the datasink. It also contains a default folder/default folders, which can be overriden in specific uses of this datasink, analogously to other datasink types.

Note that the base path must exist and must be writable by your Tomcat's user. If the (extension) folder/folders does/do not exist, it/they are created. If the report already exists in the same path, it is overwritten.

Printer Datasinks allow to send and/or schedule reports to a given printer. Printer datasink configuration can be made in the Administration area. Permissions to printer datasinks can be given analogously as to other objects in ReportServer.

Note that, while scheduling, you have to select the ''PDF'' export type in order to use printer datasinks.

Script Datasinks allow you to send and/or schedule reports to virtually any custom location you need. They also allow you to add custom files/logic to your datasinks. Script datasink configuration can be made in the Administration area. Permissions to script datasinks can be given analogously as to other objects in ReportServer.

Details on script datasinks and an example datasink can be found in the Script Guide.

OAuth2-authenticated datasinks use, as their name suggest, OAuth2 https://oauth.net/2/, the industry-standard protocol for authorization.

Before being able to use oauth2-datasinks, you have to create an app in the service needed and authorize file uploads in this app. You can then add your app key and app secret into the respective ReportServer fields and save the datasink.

When this is achieved, you can click the ''Datasink OAuth2 Authentication Setup'', which redirects you to the service authorization. Please note that you have to add the ''redirect URI'' into your app. This redirect URI is shown when clicking the before-mentioned button. The URI should be similar as this: http://localhost:8080/ReportServer/reportserver/oauth. When this is done, after app authorization and automatic redirection, you should be able to use your oauth2-datasinks. For testing your datasink, as with other ReportServer datasinks, you can use the ''Test datasink'' button.

Dropbox OAuth2-authorized datasinks allow you to send and/or schedule reports to a given directory in your Dropbox https://www.dropbox.com/ account. Note that Dropbox for Business is also supported: https://www.dropbox.com/business/.

For getting your ''app key'' and ''secret key'', you have to create an app here: https://www.dropbox.com/developers/apps/ and give it the appropriate permissions. Specifically, files.content.write and files.metadata.write is needed. More information on this can be found here: https://developers.dropbox.com/de-de/oauth-guide. Note that when permissions are changed, it is necessary to create a new ''refresh token'' with the ''Datasink OAuth Authentication Setup'' button.

The path in the datasink definition determines the base path of the datasink. It also contains a default folder/default folders, which can be overriden in specific uses of this datasink, analogously to other datasink types.

Note that the base path must exist. If the (extension) folder/folders does/do not exist, it/they is/are created. If the report already exists in the same path, it is overwritten. The path should be entered like this: /my/path. Note that, different as in other datasinks, ./my/path does not work.

OneDrive - SharePoint (O365) OAuth2-authorized datasinks enable you to send and schedule reports directly to a specified directory in your OneDrive or SharePoint (O365) account (http://onedrive.com/). This functionality supports OneDrive for Business (Office 365) as well as SharePoint O365.

Please Note: Older versions of SharePoint are not supported.

Below, you will find a step-by-step guide for setting up a OneDrive - SharePoint (O365) datasink in ReportServer.

1. Log in to ReportServer with the necessary permissions.
2. Navigate to Administration → Datasinks.
3. Right-click on Datasink Root (or on any datasink folder), select Insert, and choose OneDrive - SharePoint (O365).
4. Fill in the required fields as follows:
   1. Name: Enter a meaningful name for the datasink.
   2. Tenant ID: For the initial setup, leave the default value "common".
   3. Base Root: Leave this at the default setting (/me/drive/items/root:) for the initial setup. Details on custom drive configuration are provided below.
   4. Folder: Set this to / (default) or specify a subfolder path if needed.
   5. App Key and Secret Key: For the initial setup, enter 0 in both fields.
5. Click Apply to save the settings.

1. In ReportServer, click the Datasink OAuth2 Authentication Setup button, located at the top of the screen next to the ''Test Datasink'' button.
   1. A new window opens, displaying the Redirect URI. Ensure this URI uses the ''HTTPS'' protocol.
   2. Copy the URI and close the window using the ''(x)'' button.
2. A OneDrive Administrator must register the app in Microsoft Azure:
   1. Log in to the Microsoft Azure Portal at https://portal.azure.com/.
   2. Navigate to ''App Registrations → New Registration''.
   3. Provide a ''Display Name'' for the app.
   4. Select ''Accounts in this organizational directory only'' under ''Supported Account Types'' (or as per organizational requirements).
   5. Select the ''Web'' option in the platform dropdown menu.
   6. Enter the Redirect URI from the ReportServer setup in the field next to the ''Web'' option.
   7. Click ''Register'' to complete the registration.
3. A OneDrive Administrator must assign the required API permissions to the app:
   1. Navigate to ''Manage → API Permissions → Add Permissions''.
   2. Choose ''Microsoft Graph → Application Permissions''.
   3. Enter ''Files'' in the search field.
   4. Select ''Files.ReadWrite.All''.
   5. Click ''Add Permissions'' to finalize.
4. A OneDrive Administrator must generate the secret key:
   1. Navigate to ''Manage → Certificates & Secrets → New Client Secret''.
   2. Provide a description (e.g., ''ReportServer Key'') and specify a validity period (e.g., ''1 year'').
   3. Click on ''Add''.
   4. IMPORTANT: After creating the key, make sure to note down its value immediately, as it will be required for the configuration and cannot be retrieved later.

1. Return to Administration → Datasinks in ReportServer and open the created datasink.
2. Update the following fields:
   1. App Key: Enter the Application (Client) ID from the registered app in Azure. Your administrator can find this in the overview of the registered app in Azure, under Application (client) ID.
   2. Secret Key: Enter the value of the secret key generated in Azure in Step 2.
   3. Tenant ID: Enter the tenant ID. Your administrator can find this in the overview of the registered app in Azure, under Directory (tenant) ID.
3. Click Apply to save the settings.
4. Complete the authentication process:
   1. Select the datasink and click the Datasink OAuth2 Authentication Setup button.
   2. Click Start Authentication Process and follow the prompts to grant permissions.
   3. Return to ReportServer and click the Test Datasink button.

A test file will be created in the user's OneDrive under My Files. You can check this here, e.g.,: https://your-company-my.sharepoint.com/my.

If a company-related SharePoint drive needs to be integrated:

1. The user (account) must have access to (must be a member of) the SharePoint site. This ensures the user can upload files to the Documents area.
2. The user must obtain the Group ID and Drive ID for the desired drive. This can be done through two methods: directly via ReportServer (recommended) or using Microsoft Graph Explorer. Both approaches are outlined below.

1. Obtain the user's Access Token from Graph Explorer: https://developer.microsoft.com/en-us/graph/graph-explorer (Access token tab).
2. Execute the following commands in the ReportServer terminal:
   1. onedrive group getmygroups [path datasink] [accessToken]: Lists all groups accessible to the user. Copy the Group ID.
   2. onedrive group getdrivesof [group-id] [path datasink] [accessToken]: Lists all drives for the group. Copy the Drive ID.

1. Log in to Microsoft Graph Explorer: https://developer.microsoft.com/en-us/graph/graph-explorer with the user's account.
2. Retrieve the Group ID:
   1. Endpoint: https://graph.microsoft.com/v1.0/me/memberOf. Identify the desired group and copy its Group ID.
3. Retrieve the Drive ID:
   1. Endpoint: https://graph.microsoft.com/v1.0/groups/GROUP-ID/drives. Verify the drive (e.g., by checking its webUrl) and copy the Drive ID.

Using the Drive ID for setting a Base Root in ReportServer:

Base Root	Enter the Drive ID obtained from either the ReportServer terminal or Microsoft Graph Explorer into the Base Root field of the datasink configuration as follows: /drives/DRIVE-ID/root:
Folder	Specify the folder path if necessary (e.g., /Reports/).

1. Save and test the configuration by clicking the Test Datasink button.

• An OneDrive Administrator must ensure all API permissions and keys are properly configured in Azure. For more information, refer to: Microsoft Graph API Documentation: https://learn.microsoft.com/en-us/graph/api/resources/onedrive?view=graph-rest-1.0
• To use ReportServer terminal commands, refer to Section 20.46. onedrive in the documentation.
• When permissions are changed, it is necessary to create a new refresh token with the Datasink OAuth Authentication Setup button.
• You can use the default tenant id if a custom tenant id is not needed or if you don't have a custom tenant id.
• Note that the base path must exist. The base path is the Base Root of your datasink together with the Folder path.
• If the report already exists in the same path, it is overwritten.
• The path should be entered like this: /my/path. Note that, unlike in other datasinks, ./my/path does not work.

By following these detailed steps, both users and administrators can successfully configure the OneDrive - SharePoint (O365) datasink in ReportServer.

Google Drive OAuth2-authorized datasinks allow you to send and/or schedule reports to a given directory in your Google Drive https://drive.google.com/ account.

For getting your ''app key'' and ''secret key'', you have to create an app registration here: https://console.developers.google.com/ (Google API Console) and give it the appropriate API permissions.

The ''base root'' path in the datasink definition, together with the ''folder'' path, determines the base path of the datasink. The default folder/folders are also included, which can be overriden in specific uses of this datasink, analogously to other datasink types.

Note that the base path must exist. If the (extension) folder/folders does/do not exist, it/they is/are created. If the report already exists in the same path, it is overwritten. The path should be entered like this: /my/path. Note that, different as in other datasinks, ./my/path does not work.

More information on this can be found here: https://developers.google.com/drive/api/v3/about-sdk.

Box OAuth2-authorized datasinks allow you to send and/or schedule reports to a given directory in your Box Drive https://box.com/ account.

For getting your ''app key'' and ''secret key'', you have to create an app registration here: https://app.box.com/developers/console (Box Developer Console) and give it the appropriate API permissions.

The ''base root'' path in the datasink definition, together with the ''folder'' path, determines the base path of the datasink. The default folder/folders are also included, which can be overriden in specific uses of this datasink, analogously to other datasink types.

Note that the base path must exist. If the (extension) folder/folders does/do not exist, it/they is/are created. If the report already exists in the same path, it is overwritten. The path should be entered like this: /my/path. Note that, different as in other datasinks, ./my/path does not work.

More information on this can be found here: https://developer.box.com/reference/.