Create Amazon Redshift Data Source on SQL Server Reporting Services

In this SSRS aka SQL Server Reporting Services tutorial, I want to show how business intelligence BI developers can create a shared data source on ReportServer for an Amazon Redshift cluster database. Amazon Redshift is a managed cloud data warehouse platform provided by AWS.

Before you continue with creation of a new data source for Amazon Redshift cloud data warehouse databases, be sure that you have already created an ODBC System DSN connection via the ODBC Data Source Administrator tool.
On System DSN tab, if you have previously created an ODBC connection to your Redshift data warehouse platform, you will see the connection listed here. If not, using "Add" create a new system DSN. Of course, connecting to an Amazon Redshift database requires the installation of the related ODBC driver first on the SSRS server. As I noted in following sections, I downloaded the native Amazon Redshift ODBC Driver from AWS Management Console Amazon Redshift dashboard.
You can also make a quick web search and identify the download link.
I also put here the 32-bit and 64-bit ODBC driver download links for Amazon Redshift.

ODBC Data Source Administrator with Redshift driver and connection

Now we are ready to continue our Reporting Services tutorial for SQL Server developers. Let's start

Launch the ReportServer URL with user credentials allowed to create a new datasource.
Click on "New data source" icon

Enter a short descriptive name and a brief description for the new data source which is being created at the moment on SQL Server Reporting Services Report Server.

As seen in below screenshot, I provided "RedshiftDS" as data source name and "Amazon Redshift DWH Cluster Sample Database" for the description. I preferred to enable this data source by marking the "Enable this data source" checkbox.

Amazon Redshift as new data source on SQL Server Reporting Services SSRS

On the Connection section, choose ODBC

Here is the ODBC connection string to connect Amazon Redshift databases.
I downloaded and installed the Amazon Redshift ODBC driver provided by AWS and accessible via AWS Management Console Redshift Data Warehouse Dashboard.
Since I used the native ODBC driver for Amazon Redshift, I entered the driver name as "{Amazon Redshift (x64)}"

Default port use by Amazon Redshift database connections is 5439.

Data Source is the ODBC System DSN name given on "ODBC Data Source Administrator" tool.

For the database name enter the target database name in small letters.

Server is the IP address or the DNS name shared on Redshift cluster's properties screen as the end point for connection.

Redshift ODBC connection string on Reporting Services

On "Credentials" section, you can use a fixed username and password if applicable for your use case. As seen below, I selected the option "Using the following credentials" and entered a valid user name and password combination.

Redshift Data Warehouse database credentials for new SSRS data source

When you hit "Test connection" button, you will see an error message that can help you identify the issue with the ODBC connection string or credentials, or you will see the success message saying "Connected successfully" as above.

Click "Create" to save your Amazon Redshift data source on the current SQL Server Report Server

You will see that the new data source is created successfully so that business intelligence developers can connect to Amazon Redshift data warehouse databases for accessing external data.

SQL Server Reporting Services data sources

To summarize, the ODBC connection string for the Amazon Redshift might be tricky for some SQL Server Reporting Services administrator and developers. I tried to share a sample ODBC connection string with SQL developers and show the link between System ODBC entries and the new data source configuration.