© 2014 Informatica Corporation. No part of this document may be reproduced or transmitted in any form, by any means (electronic, photocopying, recording or otherwise) without prior consent of Informatica Corporation. All other company and product names may be trade names or trademarks of their respective owners and/or copyrighted materials of such owners.
Using Microsoft Windows
Authentication for Microsoft SQL
Server Connections in Data Archive
2
Abstract
You can use Windows Authentication mode (NTLM and NTLMJAVA) to connect to Microsoft SQL Server. This article explains how to use a Microsoft Windows Authenticated user as the ILM repository user during Data Archive installation. It also explains how to connect to a Microsoft SQL Server database through Enterprise Data Manager to import metadata, and how to configure Microsoft SQL Server as a source connection in Data Archive.
Supported Versions
Data Archive 6.1.1, 6.2
Table of Contents
Components ... 2
Prerequisites ... 2
Connection Methods ... 3
Data Archive Installation with a Windows Authenticated User ... 3
NTLM Method ... 3
NTLMJAVA Method ... 4
Microsoft SQL Server Authentication in Enterprise Data Manager ... 5
NTLM Method ... 5
NTLMJAVA Method ... 6
Microsoft SQL Server as a Source or Target Connection ... 6
NTLM Method ... 6
NTLMJAVA Method ... 7
Components
To use Windows Authentication mode to connect to Microsoft SQL Server, you need the following components:
A Microsoft Windows Authenticated user in Microsoft SQL Server
The Data Archive installer
The Microsoft SQL Server shared library file DDJDBCAuth05.dll/DDJDBCx64Auth05.dll. The file you use depends on your Microsoft Windows Server bit.
-If you have the PowerCenter client installed on your machine, you can download the dll file from <PowerCenterDir>\clients\PowerCenterClient\clients\tools\datadirect.
-Alternatively, you can download the file from this Knowledge Base article:
http://psv28cmsmas1:7000/howto/6/Pages/1/156461.aspx
Prerequisites
• Before you begin, find the domain of your Microsoft Windows user:
1. Click the Start button.
2. Right-click Computer.
3. Click Properties.
3
If your computer is connected to a domain, the domain name appears under the heading
“Computer name, domain, and workgroup settings.”
• Grant the required privileges for Microsoft SQL Server database users. For more information about the required privileges for database users, see Chapter 3 of the Data Archive
Administrator Guide.
Connection Methods
To connect to Microsoft SQL Server with Microsoft Windows Authentication mode, use one of the following two methods:
NTLM method. Use the NTLM connection method when the Data Archive server is installed on a Microsoft Windows machine. To use the NTLM method, you must specify the path of the Microsoft SQL Server shared library file DDJDBCAuth05.dll/DDJDBCx64Auth05.dll in the URL. This method of creating a connection takes the username and password for connecting to Microsoft SQL Server from your Microsoft Windows account. The username and password you enter when you create the connection is irrelevant.
NTLMJAVA method. Use the NTLMJAVA method when the Data Archive server is installed on a UNIX server. Since the Data Archive server is on a UNIX server, you must provide your Microsoft Windows username and password in the connections you create. The NTLMJAVA method does not require the .dll file.
Data Archive Installation with a Windows Authenticated
User
You can use a Microsoft Windows Authenticated user as the ILM repository user when you create the ILM repository on a Microsoft SQL Server during Data Archive installation.
NTLM Method
1. Copy the DDJDBCAuth05.dll/DDJDBCx64Auth05.dll file to the Data Archive installation folder.
2. Open a command prompt and go to the Data Archive installer’s location.
3. Set the PATH variable to include the location of the
DDJDBCAuth05.dll/DDJDBCx64Auth05.dll file. Use the following format:
set PATH=$ILMInstaller;%PATH%
For example, set PATH=C:\Installers\ILM_6.1.1
4. Run the Data Archive installer from the same command prompt window.
For example, run C:\Installers\ILM_6.1.1>ILMInstaller.exe
5. For more information about running the Data Archive installer, see Chapter 3 of the Data Archive Installation Guide.
6. When the installer prompts you to enter database connection details to create the ILM repository, enter the following information:
Field Value
Database Type Select the Microsoft SQL Server version.
Database URL $DB_HOST:$PORT;AuthenticationMethod=ntlm Login Name Enter any username. Since you are using a
Microsoft Windows Authenticated user as the ILM repository user, the username you provide
4
here is not used by the installer.
Password Enter any password. Since you are using a Microsoft Windows Authenticated user as the ILM repository user, the password you provide here is not used by the installer.
Database Name Name of the Microsoft SQL Server database.
7. Click Test Connection and complete the installation.
NTLMJAVA Method
1. Go to the location of the Data Archive installer.
2. Run ILMInstaller.bin.
3. For more information about running the Data Archive installer, see Chapter 3 of the Data Archive Installation Guide.
4. When the installer prompts you to enter database connection details to create the ILM repository, enter the following information:
Field Value
Database Type 7
Database URL SQL_SERVER:PORT;AuthenticationMethod=ntlmjava;Domain=<Domain>
For example:
SQL_SERVER:1433;AuthenticationMethod=ntlmjava;Domain=INFA Login Name Microsoft Windows username with which the Microsoft SQL Server user is
authenticated.
Password Microsoft Windows password.
Database Name Name of the Microsoft SQL Server database.
5
5. Complete the Data Archive installation. For more information, see the Data Archive Installation Guide.
Microsoft SQL Server Authentication in Enterprise Data
Manager
You can use a Microsoft Windows Authenticated user to connect to a Microsoft SQL Server database in Enterprise Data Manager.
NTLM Method
In this method, you must have the DDJDBCAuth05.dll/DDJDBCx64Auth05.dll file on the machine that launches Enterprise Data Manager.
1. From the Data Archive user interface, launch Enterprise Data Manager.
2. Select the appropriate Product Family and then click File > Import Metadata from Database.
3. Enter the following connection details for the Microsoft SQL Server source database:
Field Value
Database Type Select the type of database. For example, “Microsoft SQL Server 2008 – Type 4 Driver”
Database Host Microsoft SQL Server database host.
Database Port <port>;AuthenticationMethod=ntlm;LoadLibraryPath=<Path_
of_DDJDBCAuth05.dll/DDJDBCx64Auth05.dll>
Service Name DB_NAME
Username Enter any username. Enterprise Data Manager uses your Microsoft Windows username.
Password Enter any password. Enterprise Data Manager uses your Microsoft Windows password.
6
NTLMJAVA Method
1. From the Data Archive user interface, launch Enterprise Data Manager.
2. Select the appropriate Product Family and then click File > Import Metadata from Database.
3. Enter the following connection details for the Microsoft SQL Server source database:
Field Value
Database Type Select the type of database. For example, “Microsoft SQL Server 2008 – Type 4 Driver”
Database Host Microsoft SQL Server database host.
Database Port <port>;AuthenticationMethod=ntlmjava;Domain=<Domain>
Service Name DB_NAME
Username Microsoft Windows username that is authenticated in Microsoft SQL Server.
Password Microsoft Windows password that is authenticated in Microsoft SQL Server.
Microsoft SQL Server as a Source or Target Connection
You can use a Microsoft Windows Authenticated user to connect to a Microsoft SQL Server as a source or target connection.
NTLM Method
The NTLM method can be used only when the Data Archive server is installed on a Microsoft Windows server. The Microsoft Windows user with which you start Data Archive should be an
7
authenticated user in the source Microsoft SQL Server database. You must have the
DDJDBCAuth05.dll/DDJDBCx64Auth05.dll library file on the machine where Data Archive is installed.
1. Log in to Data Archive.
2. Click Administration > New Source/Target Connection.
3. For Microsoft SQL Server authentication, enter the following information:
Field Value
Host IP address of the source application database server.
Port <PORT>;AuthenticationMethod=ntlm;LoadLibraryPath=<Path_
of_DDJDBCAuth05.dll/DDJDBCx64Auth05.dll>
Admin Login Name Enter any username. Data Archive uses your Microsoft Windows username.
Password Enter any password. Data Archive uses your Microsoft Windows password.
4. Enter the remaining connection details. For more information, see Chapter 4 of the Data Archive Administrator Guide.
NTLMJAVA Method
You can use the NTLMJAVA method of creating a source connection when the Data Archive server is installed on a UNIX server or a Microsoft Windows server.
1. Log in to Data Archive.
2. Click Administration > New Source/Target Connection.
3. For Microsoft SQL Server authentication, enter the following information:
Field Value
Host IP address of the source application database server.
Port <PORT>;AuthenticationMethod=ntlmjava;Domain=<Domain>
Admin Login Name Microsoft Windows username that is authenticated in Microsoft SQL Server.
8
Password Microsoft Windows password that is authenticated in Microsoft SQL Server.
4. Enter the remaining connection details. For more information, see Chapter 4 of the Data Archive Administrator Guide.
Authors
Shailesh Khuperkar
Senior Technical Support Engineer, Global Customer Support