Today, I was playing around with making a small cube out of the data in the sys.bandwidth_usage table in SQL Azure, and I got an error message which reads:
"Test Connection failed because of an error in initializing provider. Server Name cannot be deteremined. It must appear as the first segment of the server's dns name (servername.database.windows.net.) Some libraries do not send the server name, in which case the server name must be included as part of the user name (username@servername). In addition, if both formats are used, the server names must match."
I solved this the way the error says to solve it, by adding what was in the server name field after the user name in the user name field. I also noticed that it can be solved by switching to the native .NET provider, and not using the server name in the user name. I played with the data source in SSIS, too, and it works the same way. Interestingly, SSRS chooses a native .NET SQL provider by default, and therefore doesn't have this problem.