SQL Server Reporting Services

An overview of the SQL Server Reporting Services integration with Secoda

SQL Server Reporting Services Metadata Extracted

Getting Started with SQL Server Reporting Services

There are 2 options to connect SQL Server Reporting Services with Secoda:

1. REST API

2. Report Server database

Option 1: REST API

Use the SQL Server Reporting Services REST API to retrieve metadata. Note that older versions of SQL Server Reporting Services do not support the REST API.

Retrieve the host domain

To retrieve the host domain, navigate to the home page of your report services workspace. Take the part of the URL that does not change when navigating around the workspace as your host domain.

For example, if the page is on the 'browse' tab and the link is https://example.secoda/reports/browse, then the host domain is https://example.secoda/reports in this case.

Retrieve account credentials

Your username and password is required to connect SQL Server Reporting Services to Secoda. Note that the display name in the top right of the home page may not be the username for your account. If you unsure of your username, it can be found by following these steps:

1. Enter your host domain into your browser, and add the following to the end of the url, /api/v2.0/me?%24select=Username

2. To the right of the “username” field, your account username is found

Connect SQL Server Reporting Services

After retrieving the host domain and user credentials, the next step is to connect to Secoda:

1. In the Secoda app, select "Connect integration" on the Integrations page

2. Search for and select SQL Server Report Services

3. Enter your host, username and password you retrieved

4. Click "Connect"

Option 2: Report Server database

Query the metadata database underlying your SQL Server Reporting Services instance.

Create a database user

In Report Server Configuration Manager, identify the SQL Server database backing your SQL Server Reporting Services instance. By default, its name is ReportServer.

Log in to the server and create a login for our user:

CREATE LOGIN <login_name> WITH PASSWORD = '<password>';

Then create a user and grant it reader permissions on the metadata database:

CREATE USER <user_name> FOR LOGIN <login_name>;
GRANT SELECT ON DATABASE::<report_db> TO <user_name>;

Use this username and password when configuring the integration in Secoda.

Connect SQL Server Reporting Services to Secoda

1. In the Secoda app, select "Connect integration" on the Integrations page

2. Search for and select SQL Server Report Services

3. Enter your host, port, username, password, and database name.

4. Click "Connect"

Security

If your SQL Server is inside a VPC or behind a firewall, whitelist Secoda’s outbound IP addresses so our workers can reach the host. Alternatively, use a reverse SSH Tunnel.

Once an SSH tunnel is configured (if you are using one), choose SSH Tunnel in the connection form and provide the tunnel details. See the full list here: What are the IP addresses for Secoda?