Connecting to MS SQL Server database using Active Directory (Windows Authentication)

Products

• CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

How can I connect to a MSSQL Server database from the CA TDM SERVER components (DataMaker, FastDataMasker, GTSubset, Javelin) using Windows authentication (or Active Directory account)?

Environment

All support TDM releases.

Cause

N/A

Resolution

These are the methods to connect to a MSSQL DB from the different TDM products using Active Directory credentials (or Windows authentication).

For Datamaker, in the connection profile properties:
1. For DBMS, pick "DSN-Less ODBC" from the drop-down.
2. For ODBC Driver, select "SQL Server Native Client x.x" from the drop down (x.x could be 11.0 for instance).
3. Provide the server and database details.
4. Click on the "No login required (e.g. Integrated login)" radio-button.
5. Test the connection and save the profile.

For FDM:
1. Copy the 64-bit (x64) version of sqljdbc_auth.dll to "C:\Program Files\Grid-Tools\FastDataMasker" and "C:\Program Files\Grid-Tools\FastDataMasker\lib\sqljdbc_auth.dll" folders (assuming FDM is installed at default location).
2. When saving the profile in FDM, leave username and password blank.

Note: The requirement to place the DLL file in the "sqljdbc_auth.dll" folder under "lib" will be fixed and no longer required in TDM 4.0 and later versions. The DLL file will need to be directly placed under the "lib" folder instead. 

For Subset:
1. Copy the 64-bit (x64) version of sqljdbc_auth.dll to "C:\Program Files (x86)\Grid-Tools\GTDatamaker\lib".
2. When saving the profile in Subset, leave the username and password blank.

For Javelin:
Use the ConnectionString property in the SQLServerActivity block and set "Integrated Security=True" to use AD Account for authentication against SQL Server. 
For example, the connection string would read "Data Source=localhost;Initial Catalog=Demo;Integrated Security=True"

For TDoD Service:
1. In the GTWCFHOST.exe.config file configure the connect string with Integrated Security=true: 
<connectionStrings> 
<add name="ConnectionString" connectionString="Data Source=[server]\MSSQLSERVER,1433;Integrated Security=True;Initial Catalog=gtrep;Connection Lifetime=60;Min Pool Size=1;Network Library=dbmssocn;Application Name=GT TDoD Service;" providerName="System.Data.SqlClient" 

2. Instead of using Local System, the ‘Log On As’ user in the properties for the 'CA TDoD Service' should be the domain user that has access to SQL Server and the database.