Microsoft – SQL Server (v2008 and above) – Reporting Services – Data Source – Configuration – Error Message – Log on failed: Ensure the user name and password are correct

Microsoft – SQL Server – Reporting Services – Data Sources – Configuration

While configuring a “Microsoft SQL Server – Reporting Services – Data source”, we kept getting this error “Log on failed: Ensure the user name and password are correct”.

Obviously the grammar is incorrect: – It should read Log on failed: Ensure the user name and password is correct”.

But, seriously:

The problem manifests in Microsoft SQL Server – v2008:

  1. We are trying to create a new data source
  2. We chose the option that reads “Credentials stored securely in the report server”
  3. We entered the username as [domain name] \ [username] – i.e. LAB\myuser
  4. We entered the password as sarahjames
  5. We chose “Use as Windows credentials when connecting to the data source”
  6. Once we clicked “Test Connection” we started getting the error “Log on failed: Ensure the user name and password are correct”

To remedy, please try the following path:

Via Local Policy Configuration, ensure that “Auditing” is enabled:

To do so, follow the steps below:

  1. Launch “Local Security Policy”
  2. On the left panel, Access “Security Settings”\”Local Policies”\”Audit Policy”
  3. The actual auditable policies will be displayed on the right panel
  4. Ensure that at minimum “Failures” are recorded for the following events:
    1. Audit Logon events

Review the Security Logs

MS Windows 2008, 2008-R2:

  1. Launch Event Viewer
  2. Select “Security Log” à On the left panel, select “Windows Logs”\”Security”
  3. Choose to filter the listing by:
    1. Right clicking on the Security Log
    2. From the drop-down menu, select “Filter Current Log…”
    3. The “Filter Current Log” window is displayed:
      1. In the Include/Excludes Event IDs, enter 4625

Review the entries that match the filtered event ids:

Failures are often reflective of constraint placed on specific accounts or\and missing “User Rights Assignment”:

Constraint placed on specific accounts:

  1. Account Logon time restrictions
  2. Account Logon Workstation restrictions

To address have a Windows or Security Admin, review Account settings via
“Active Directory Users and Computers”

     Missing “Users Rights Assignment”:

      Depending on your setup \ topology, you might need the following policies:

  1. “Allow log on locally”
  2. “Access this computer from the network”

Of course, errors may also be due to the account not having access to SQL Server Instance. Such errors are also registered in the “Windows Event Viewer”.

But, richer and more precise entries are logged in the SQL Server ErrorLog files. Have a SQL Server Admin. Review the error log files to get more helpful data.

2 thoughts on “Microsoft – SQL Server (v2008 and above) – Reporting Services – Data Source – Configuration – Error Message – Log on failed: Ensure the user name and password are correct

  1. When using the Test Connection function using a service account’s credentials, SSRS does not act as a service, rather as a batch. This is why the authentication is failing and you are getting the error “Log on failed. Ensure the user name and password are correct”. To fix this, edit the Local Security Policy of the SSRS box and add that service account to “log on as a batch job”
    Cheers

    • James Cooke:

      Thanks so much.

      Guidance is duly noted and will use in the future.

      Thanks for handsomely contributing back to yours faithfully and the community at large.

      In his grace,
      Daniel

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s