Sql-server – sql server authentication login failed

Whatever username you are using on the "remote" machine must be set up on the "server" machine.

So, if you are logged-in to "remote" as "John," there must be a "John" user on the machine that is hosting SQL Server 2008 Express, and unless you are going to provide different credentials, it should have the same password, too.

I have confirmed this is an issue with how the reports get executed on the server -- I reproduced the issue exactly by using two virtual machines, neither of which was in a domain.

Moreover, I created a test user (member of the Users built-in group and nothing more) on the SQL box, ran Management Studio locally as that user, logged in with sa, and attempted to run the report. As I expected, it failed, and returned the error Failed to connect to server MDWTestSQL. --> Login failed for user 'MDWTESTSQL\TestUser'.

This is clear indication that the reports are implemented to only use Windows authentication for some portion of it. Therefore, sufficient SQL-level permissions must be granted to the Windows principal running Management Studio, even if you authenticate to the server using a SQL login.

There are two workarounds:

1. Use pass-through authentication: create a matching (user name and password) local login on the workstation, and impersonate the local login to run Management Studio remotely. (I tested that this works.)

2. Use pass-through authentication by creating new matching local accounts on both boxes, and set up SQL permissions for the new account.

As I mentioned in chat, this problem is "fixed" in the 2012 Management Studio -- the reports in the menu are hidden if the Windows account doesn't have permissions to run the reports.