Created a 'datareader' user linked to Windows AD to embed in SSRS reports; and user has authentication issues

Question

I am attempting to add a Windows AD User account in order to embed it in SSRS reports for org wide reports.

Using SSMS (with <Domain>\Administrator access):

• Server Level:

  • Created the account on SQL Server > Security
  • Logins: General (Windows Authentication)
  • Default Database: <company database>
  • Server Roles: Public
  • User Mapping: <company database> (selected)
  • Status:
    • Connect: Grant
    • Login: Enabled

• Database Level:

  • Membership: db_datareader

• ImperviousInclemency: What error do you get when attempting to connect? Does this error occur when attempting to connect directly in SSMS or is it through SSRS?
• S M: "An error occurred during local report processing. An error occurred during report processing. Cannot create connection to data source '<database>'. Login failed for user '<DOMAIN>\<user>." The user on AD enabled, unlocked, with password set to Never expire. I have successfully logged on to a Windows workstation. Using Windows Authentication, the report runs successfully.
• Sean Gallardy: Check the SQL Server errorlog for that datasource and see what the reason for the 18456 error is (look at the state).
• S M: Login failed. Attempting to use an NT Account name with SQL Server Authentication. In VS2015, for Data Source properties Credentials, Use this User name and password: <DOMAIN>\<user> and the password.
• Craig: Actually, I just realised that you may be referring to setting username/password in the "Credentials" section within either Visual Studio or the Report Designer - is that the case? If that is the case, then you will instead need to (after deploying the report to the server) go to the actual Data Source on the server and in the "Credentials" section there, you should be able to specifically set "Windows username and password", and then enter your <DOMAIN>\<user> and corresponding password

Answer 1

I got that; it doesn't make sense. Microsoft SSRS on microsoft Windows Server, can't access data on Microsoft SQL Server installed on a Microsoft Windows Server running in Windows Active Directory environment where other Active Directory Users are using an ERP software with Data hosted on MIcrosoft SQL Server? I don't know what I am missing. I can run the same report witth Integrated Authentication. As soon as I change the Credentials in the Data Source of the Report from Windows Integrated Authentication to Use this Username (<user>) and password, the authentication fails.

Per the documentation:

When you select Use this user name and password, a user name and password must be supplied to access the data source. For a SQL Server database, the credentials might be for a database sign-in. The credentials are passed to the data source for authentication.

Thus you get the error Login failed. Attempting to use an NT Account name with SQL Server Authentication. because you're giving it a username and password which will be used as a sql login attempt per the documentation but you're giving it a windows credential, which is incorrect as I stated in the comments.

You'll want to consult what is available for SSRS in the documentation linked above and make the appropriate changes per your needs and local factors limitations.