Connect to Amazon Redshift from SQL Server using Linked Server with ODBC Data Source

Database developers can integrate SQL Server and Amazon Redshift databases using SQL Server Linked Server configuration via System DSN with ODBC drivers. Linked Server creation enables SQL programmers to access to other resources like table and views on Amazon Redshift cluster databases from SQL Server environment easily and execute SQL statements and queries. In this tutorial, I want to show how to download ODBC drivers for Redshift, how to install these Amazon Redshift ODBC drivers, creating ODBC data source and creation of Linked Server to Amazon Redshift on SQL Server Management Studio.

Download and Install ODBC Drivers for Amazon Redshift

To connect to Amazon Redshift database, developers can use ODBC drivers as well as JDBC drivers. In a previous AWS tutorial I have shown download JDBC driver for Amazon Redshift and connect from SQL Workbench tool.
In this tutorial, I will show how Amazon Redshift ODBC drivers can be used to connect Redshift database from SQL Server instances using Linked Servers.

Open a web browser and navigate to the AWS official documentation for ODBC driver downloads.
I download 64-bit ODBC driver for Amazon Redshift database.

Later when the Amazon Redshift ODBC driver .msi installation file download is completed, execute the setup with an administrator account.
You can launch Command Prompt with Run as Administrator option and navigate to the folder where the .msi setup file for the Redshift database ODBC driver exists, and run the setup by executing the AmazonRedshiftODBC64-1.4.6.1000.msi file.
The setup wizard will lead you through the installation.

When the setup is completed, you can define the ODBC connection to Amazon Redshift Cluster as in following step

Create ODBC Data Source for Amazon Redshift Database

Launch ODBC Data Source Management application with Run as Administrator option.

Switch to System DSN tab and press on add button to create a new ODBC data source.
From the available lists of drivers, choose Amazon Redshift driver for database connection to Redshift cluster lead node.

Define a name for your Amazon Redshift ODBC data source in "Data Source Name" textbox.
Then type the private IP address of your Amazon Redshift cluster in "Server" input area.
If the Amazon Redshift endpoint is public available and if you don't have a firewall policy preventing you to the Redshift database, you can use the public IP or DNS name in server textbox too.
Port is by default 5439 for Amazon Redshift connection.
In "Database" textbox, enter the name of the target database that you want to connect to in the cluster.

In authentication area, since I use databases username and password for connection, I typed the user name and password of the database user.

After all configuration in DSN setup for the Amazon Redshift ODBC Driver is completed, press Test button. If you see "Successfully connected to the data source!" success message, then we can launch SQL Server Management Studio and define Linked Server to Amazon Redshift database as shown in following step.

Create SQL Server Linked Server to Connect to Amazon Redshift Database

In this step, SQL Server database administrators and database developers can see how they can easily define and create Linked Server to Amazon Redshift databases for a connection Redshift cluster nodes.

I always prefer to launch SQL Server Management Studio (SSMS) with Run as Adminstrator option.
Since my regular user has limited access to resources on my laptop, I use my local admin account for installing and configuration of programs and such requirements. For example, in this tutorial sample I failed to create Linked Server successfully with regular Windows user but managed to create Linked Server successfully with a local administrator account.

On SQL Server Management Studio (SSMS) connect to target SQL Server instance.
On Object Explorer window, drill down to "Server Objects > Linked Servers" node as follows.

Right click on "Linked Servers" node and on context menu, choose option "New Linked Server..."

When "New Linked Server" "General" screen is displayed, in "Linked Server" textbox enter a descriptive name to your linked server datasource.

The "Server type" will be "Other data source" and it is crucial to select "Microsoft OLE DB Provider for ODBC Drivers" in Provider combobox.

Type the Product Name as "REDSHIFT" (fixed for Amazon Redshift connections over ODBC providers)

The Data Source will be the data source name that you have used in ODBC connection creation step before.
Data Source (in Linked Server) = Data Source Name (in ODBC DSN entry)

Now switch to "Security" tab and in "Be made using this security context" option, type the "Remote login" and "With password" entries by providing the database username and password for the Amazon Redshift database.

And in "Server Options" tab, by double-click on "False" values on "RPC" and "RPC Out" convert these configuration settings to "True"

Click OK button to complete creation of Linked Server to Amazon Redshift cluster database from SQL Server instance. If no error is reported, then you are ready to use your linked server connection from SQL Server to Redshift database in your SQL queries.

If you drill-down the Linked Server on Object Explorer window, database developers will see Catalogs node, your target database node with tables and views from public schema.

Sample SQL Queries on Amazon Redshift over SQL Server Linked Server

After Linked Server is created on SQL Server connecting to Amazon Redshift, SQL database programmers can execute SQL queries on Redshift database.

Assume that there is a table named city on target Redshift database, then following SQL SELECT query can be executed to list data stored in Redshift database table.

Please note that the table is in fully qualified form with linked server name, database name, schema name and table name concatenated with "."
Output is as follows for our sample SQL Select query.

A simple SQL Insert command can be executed as follows with EXEC AT statement.

During executing SQL commands, following SQL engine errors can be experienced:

Msg 7411, Level 16, State 1, Line 4
Server 'REDSHIFT' is not configured for RPC.
This error points to "RPC" Server Options on SQL Server "Linked Server" configuration settings that are left unchanged as False.

Msg 7356, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "REDSHIFT" supplied inconsistent metadata for a column. The column "id" (compile-time ordinal 1) of object ""kodyazdb"."public"."city"" was reported to have a "DBCOLUMNFLAGS_ISNULLABLE" of 0 at compile time and 32 at run time.

DBCOLUMNFLAGS_ISNULLABLE error is interesting. If Redshift database table column does not accept NULL values, though you pass NOT NULL values, this error occurs. This error shows a mismatch between meta data of Redshift table and SQL command interpretation. If you change the column as accepting NULL values in table DDL, DBCOLUMNFLAGS_ISNULLABLE error will be resolved.