Recently I was having some issues getting a new SQL Server Analysis Services (SSAS) cube to process on a new development (dev) server. The error I kept receiving was:
[Analysis Services Execute DDL Task] Error: OLE DB error: OLE DB or ODBC error: Login timeout expired; HYT00; An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.; 08001; SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. ; 08001.
After much toying around I was able to determine what was causing this. In my case I had my shared data source connection for my data warehouse using Windows Authentication and the Impersonation Info set to Default. The problem appears to stem from the translation from Windows Authentication to the Impersonation Info option.
To fix this I changed my shared data source connection to use a SQL Server account and left the Impersonation Info option as default. This then must have allowed SSAS to translate the username and password info when doing the impersonation. I could be wrong but it is now working.
If you have any comments on this or a deeper explination please share? If you too are experiencing this issue o I hope this helps!
September 9th, 2008 at 9:03 pm
Ben,
Thanks for the insights. Question: When you connect BI Dev Studio to SQL Server (eg. for cube dev), do you prefer to use…
* Native OLEDB\SQL Native Client (the default), or
* .Net Providers\SQLClient Data Providers…?
I’ve never had problems with the default, but I’ve been hearing that (at least for app-dev purposes), .Net may be superior. To see one such discussion thread, click on…
http://www.vbforums.com/showthread.php?t=537137.
Your thoughts, Ben?
September 9th, 2008 at 9:57 pm
In SSIS I like the .Net provider for Execute SQL tasks because of the interface with the .Net data types and named parameters is much better, but in the data flows I generally use OLEDB because the .Net destination (SQL Server Destination) is only available when the SSIS code is on the same server as the destination database.
October 2nd, 2008 at 6:35 pm
Thanks buddy…that worked me