Skip Ribbon Commands
Skip to main content
Navigate Up
Sign In

Quick Launch

Average Rating:

(4 Ratings)
facebook Twitter
Email
Print Bookmark Alert me when this article is updated

Feedback

HOW TO: Configure PowerCenter on UNIX to connect to Microsoft SQL Server using Windows authentication
Solution

Prerequisites

Following are the prerequisites to use Windows authentication with Microsoft SQL Server from UNIX:

  • PowerCenter 9.5.1 (or later)
  • DataDirect 7.x (or later) ODBC

Procedure

To configure PowerCenter on UNIX to connect to Microsoft SQL Server using Windows authentication, set the AuthenticationMethod entry to 9 (if you are using NTLMv1 authentication mechanism for your Windows Domain) or set the AuthenticationMethod entry to 10 (if you are using NTLMv2 authentication mechanism for your Windows Domain) ​and the Domain entry to the Windows_Domain_Name:

AuthenticationMethod=9
Domain=<Windows_Domain_Name>

Following are the complete steps:

  1. Configure the DataDirect ODBC component on the machine hosting the PowerCenter Services.
    (For complete ODBC installation directions, refer to HOW TO: Configure DataDirect ODBC on UNIX with PowerCenter (KB 14684).
  2. Log in to the system where the PowerCenter Integration Service is running.
  3. Create an entry in the odbc.ini file to add a Data Source Name (DSN) for the Microsoft SQL Server ODBC driver.
    For complete details on configuring the DataDirect New SQL Server ODBC driver refer to HOW TO: Configure PowerCenter to connect to a Microsoft SQL Server ODBC from UNIX (KB 15373)
  4. Ensure that you set the Authentication Method as 9 or 10 depending on the NTLM authe mechanism used in your Windows domain, so that the driver uses the NTLM authentication:
    • AuthenticationMethod=9

    • Domain=<Windows_Domain_Name>

Sample entries in the odbc.ini file are as follows:

[My_ODBC_DSN]
Driver=<dwsqlsxx.so driver location>
Description=SQL Server DSN
Address=<hostname>,<port>
Database=<database name>
LogonID=
Password=
QuotedId=No
AnsiNPW=Yes
AuthenticationMethod=9
Domain=<Windows domain name>

Create an ODBC Connection in the Workflow Manager

On Windows, ensure that the PowerCenter Integration Service uses the same Windows NT login as the Microsoft SQL Server connection.
On LINUX, ensure that the PowerCenter Integration Service uses the user details in the connection object for authentication.

  1. Log in to the Workflow Manager.
  2. Go to Connections > Relational.
  3. Click the New button and select ODBC as the subtype.
  4. Click OK.
    The Connection Object Definition dialog box appears.
    • Name: Name you want to use for this connection. The connection name cannot contain spaces or other special characters, except for the underscore.
    • User Name: Enter your Windows NT login without the domain prefix.
    • Password: Enter your Windows NT password.
    • Connect String: Enter the ODBC data source name previously configured in the odbc.ini file.
    • Code Page: Code page the PowerCenter Integration Service uses to read from a source database or write to a target database.

      174677_01.jpg
  5. Click OK to save the connection to the repository.
  6. Use this relational connection in sessions connecting to this SQL Server database.
More Information
Reference
Applies To
Product: PowerCenter
Problem Type:
User Type:
Project Phase:
Product Version: PowerCenter 9.5.1; PowerCenter 9.6.0; PowerCenter 9.6.1
Database: Microsoft SQL Server; ODBC
Operating System: UNIX; AIX; Linux; SUSE Linux ES; Redhat Linux
Other Software:
Attachments
Last Modified Date:4/18/2016 2:57 AMID:174677
People who viewed this also viewed

Feedback

Did this KB document help you?



What can we do to improve this information (2000 or fewer characters)