Datamaker: SQL Server Error 18456- Login failed for user

Products

CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

When trying to connect to my SQL Server database in Datamaker, I am receiving the following error:

Cannot connect using supplied values.
Connection failed:
SQLState: '28000'
SQL Server Error: 18456
[Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user <username>\<servername>.

Steps to reproduce:
1. Launch Datamaker by right-clicking on the icon and choosing the 'Run as elevated' option.
2. In the 'Create connection profile for Test Data Repository' window, choose the 'I need to provide credentials for my Test Data Repository' radio button.
3. In the 'Database Type' tab, for the question 'What type of database is being used for your Test Data Repository?', choose Microsoft SQL Server.
4. In the 'ODBC Data Source Administrator' window, under the 'User DSN' tab and 'User Data Sources', choose 'dBASE files- Microsoft Access dBASE driver', and click 'OK'.
5. In the 'Create a new Data Source' window, select the driver 'SQL Server Native Client 11' and click 'Finish'.
6. Add in the name, description, and server that you want to connect to:
7. In response to 'How should SQL Server verify the authenticity of the login ID?', choose the 'With Integrated Windows authentication' radio button and click 'Next'.
- Also have the box checked for 'Connect to SQL Server to obtain default settings for this additional configuration options.
- Expected result: I continue setting up the repository.
- Actual result: I get a connection failed for user error message.

Environment

All support TDM release.

Portal

Cause

This problem occurs if the user tries to login with credentials that cannot be validated.

Resolution

This problem can occur in the following scenarios:

Scenario 1:
- The login may be a SQL Server login but the server only accepts Windows Authentication
- To resolve this issue, configure SQL Server in Mixed Authentication Mode.
- To do this, follow these steps:
1. Open SQL Server Management Studio. To do this, click Start, click All Programs, click Microsoft SQL Server 20xx (where xx is the version of SQL), and then click SQL Server Management Studio.
2. Right-click the server, and then click Properties.
3. On the Security page, under Server authentication, click the SQL Server and Windows Authentication mode option button, and then click OK.
4. In the SQL Server Management Studio dialog box, click OK to restart SQL Server.
- Note: When SQL Server is installed by using Windows Authentication mode and is later changed to SQL Server and Windows Authentication mode, the sa login is initially disabled.
- This causes the error: "Login failed for user 'sa'."
- To enable the sa login, see How to Change Server Authentication Mode.

Scenario 2:
- You are trying to connect by using SQL Server Authentication but the login used does not exist on SQL Server
- To resolve this issue, verify that the SQL Server login exists.
- For more information, see Create a login in SQL Server Books Online.

Scenario 3:
- The login may use Windows Authentication but the login is an unrecognized Windows principal
- An unrecognized Windows principal means that Windows can't verify the login.
- This might be because the Windows login is from an untrusted domain.
- To resolve this issue, verify that you are logged in to the correct domain.

Additional Information

If you are still having issues, please review the following knowledge documents for this error with your DBA: