Have had success establishing and using SQL Server linked server (SQL Server 2008 R2) connected to Teradata using OLE DB Provider for Teradata (Version 126.96.36.199) using native Teradata (TD2) authentication.
Our Teradata data source will transition to use LDAP authentication, so we must adapt our linked server for this new authentication method. (To prepare for that transition and to conduct tests, we’re provided access to a Teradata database that requires LDAP authentication.)
I am able to successfully employ the OLE DB Provider for Teradata driver using LDAP authentication using various tools (e.g., SSIS, Reporting Services, .NET OleDbConnection) that utilize an OLE DB initstring, which is in the form:
"Provider=TDOLEDB.1;Password=(password);Persist Security Info=True;User ID=(userid);Data Source=(datasource);Authentication Mechanism=ldap"
However, using the same driver, I am unable to configure a SQL Server linked server with a specific Authentication Mechanism setting.
My understanding is that the authentication mechanism should be configured as part of the linked server definition via the sp_addlinkedserver system stored procedure. I have tried specifying the authentication mechanism via sp_addlinkedserver's @provstr parameter, as in the following:
@server = 'Teradata_LDAP',
@srvproduct = 'Teradata',
@provider = 'TDOLEDB.1', -- OLE DB Provider for Teradata
@provstr = 'Authentication Mechanism=ldap'
Executing the statement creates the linked server, and subsequent creation of a linked server login proceeds. Upon attempting to use the linked server, the connection to Teradata is attempted and the following error is returned:
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "TDOLEDB.1" for linked server "Teradata_LDAP".
I have tried using @provstr = 'Authentication Mechanism=TD2' against the database that uses native Teradata authentication, and the same error arises upon using the linked server. (For other tools that take a single connection string, I am able to properly establish a connection to the Teradata database that uses native Teradata authentication via the connection string cited above, specifying td2 for the authentication mechanism instead of ldap.)
All of my attempts to use a linked server using OLE DB Provider for Teradata that involve specifying a non-empty @provstr parameter value results in the same error.
Appreciate any guidance on how the authentication mechanism can be specified when setting up a SQL Server linked server using the OLE DB Provider for Teradata.
Thanks very much.
I am trying to achieve the linked server from SQL2005 Express to Teradata 13. I was able to establish this by setting up on ODBC connection to Teradata and then creating a linked server through this. However, I would like to get this working with the OLE DB driver for Teradata. I have found some information on this, but not enough to get me going on what data I need to enter in Product Name, Data Source, etc. I have read that the Product Name is the actual Teradata database name and the Data Source is the server name/IP?
How did you actually create the linked Server? I've been attempting to do this same thing on a SQL Server 2008 and have had no luck. I've created the 64 bit ODBC connection but the Linked Server errors out.
So the questions are:
What is the Product Name? It says it's the OLE DB Data source to add as a linked Server. Does that mean it's the name of the Teradata Server?
I understand that the Data Source is is the name used for the 64 bit ODBC connection.
What do you use for the Provider String?
Any help getting this to work would be hugely appreciated.