I recently encountered the error, “Unable to validate source query” when trying to refresh the metadata for the tables in my tabular model using Tabular Editor. I immediately googled that at came up with a great post by Koen Verbeeck (Blog | Twitter). I had never seen this error before and since my metadata refreshes had been working flawlessly for weeks, I was so excited when I found this post.
Long story short, this post did not help me. I tried everything suggested, I ran my partition queries wrapped in SET FMTONLY ON and they came back instantaneously in SSMS. I added the TabularEditor_AddFalseWhereClause annotation from this thread. Neither worked. So wasn’t quite sure what was going on.
My last-ditch effort was to add a new table to my model to see if I was even getting a successful connection to my data source. I was prompted for the password, which it had not done before when adding new tables or refreshing table metadata (for weeks). I was using a legacy data source (Azure SQL Database) w/ SQL Server Authentication. Once I supplied the password, I could see a list of available objects in my database. I cancelled out of the new tables dialog and clicked Refresh Table Metadata and winner-winner chicken dinner, no more “Unable to validate source query” error. Turns out my password “mysteriously disappeared” from my connection string.
The moral of the story is: It’s not always zebras when you hear hoofbeats, sometimes it is horses.
Hopefully, this post will help someone else waste significantly less time than I did on fixing this error.