Unable to set up SQL 2012 Linked Server to Teradata ODBC connection

Connectivity
Visitor

Unable to set up SQL 2012 Linked Server to Teradata ODBC connection

Hi,

I am getting below error when I tried to create a LinkServer in SQL management Studio 2012.  I was able to create and tested the connection with the Teradata ODBC system DSN.  I can use Teradata SQL assistance to access Teradata from the server.  Any suggestion?

 

TITLE: Microsoft SQL Server Management Studio
------------------------------

The linked server has been created but failed a connection test. Do you want to keep the linked server?

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "TD".
OLE DB provider "MSDASQL" for linked server "TD" returned message "The driver returned invalid (or failed to return) SQL_DRIVER_ODBC_VER: 03.80".
OLE DB provider "MSDASQL" for linked server "TD" returned message "". (Microsoft SQL Server, Error: 7303)

Tags (1)
1 REPLY
Teradata Employee

Re: Unable to set up SQL 2012 Linked Server to Teradata ODBC connection

My SQL Server knowledge is limited so I hope this procedure may help you.

 

My SQL Server 2014 (possible the same for SQL 2012) is a 32-bit version, so the ODBC Driver

DSN that I used is from 32-bit ODBC Administrator. It's the same DSN name I used with 32-bit SQLA.

 

I created my Linked Server and named it "sql_td". Under Server Type is clicked Other Data Source.

From "General":

Provider: Microsoft OLE DB Provider for ODBC Drivers

Product Name: Teradata

Data Source: td_1510   (DSN Name from the 32-bit ODBC Administrator I created for SQLA)

Provider String & Location I left them Blanks.

 

Click  "Security" on the left pane to bring up the next screen.

 

I only selected the button  "Be made using this Security Context"

and entered Teradata username under:

Remote login = systemfe

With Password = <systemfe password>

Click Ok. 

 

I did this test twice because it initially failed with a similar error you reported when I entered

"sa" and checked the "Impersonate". I deleted the newly created Linked Server and start

a new none. This time, the Test Connection works.

 

The Linked Server may not work if your SQL Server is 64-bit version and the

ODBC Driver is 32-bit due to imcompatibility.

I haven't tested the 64-bit SQL Server 2014 with 64-bit ODBC Driver as I don't

have a 64-bit SQL Server.