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!
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 OLEDBSQL Native Client (the default), or
* .Net ProvidersSQLClient 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…
www.vbforums.com/showthread.php?t=537137.
Your thoughts, Ben?
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.
Thanks buddy…that worked me
I researched a very similar issue recently when a SSAS developer was trying to deploy and process a cuve in a develoment environment. The only security option that was working was the Impersonation Information set to use “Specific Windows Account”. It was causing the developer problems b/c our password policy forces changes every couple of weeks. He had tried the other Impersonation Information settings and none of them worked.
Here’s an overview of the settings and how they did (or didn’t work for us):
Specific Windows Account
====================
This works but requires some manual intervention when the password changes. After reviewing all the available alternatives, this was my preferred method for development.
Service Account
============
Passes the Service account running the local SSAS instance. We are in a scale out model (SSAS is on a different box than the SQL data sources) and dev usually starts on local machine. So, the SSAS service account would have to have permissions on the actual data on the SQL Service. That’s not a best practice – the service account should just be able to power the service. In the real world I’ve found that is difficult to do for the SQL Server and SQL agent accounts and still allow jobs to execute and access different assets across our organization.
This will be a valid option for the roll into production, b/c I think this will pull the credentials of the service account invoking the job to run in SQL Agent. I’ll have to watch this one as we finish the dev work and roll it into automation.
Credentials of Current User
====================
This mode is NOT supported for processing cubes. It’s interesting that its even an option…oh well, it is what it is. Read BOL topic “Impersonation Information Dialog Box” to see where functionality is not supported by this option.
Inherit
=====
This defaults to the value of the DataSourceImpersonationInfo setting of the OLAP db. That db setting defaults to the SSAS Service account, so reference the Service Account section above.
The error message we were seeing when the Impersonation Information settings were not configured properly and we tried to process a cube:
*** Start of Error Message ***
Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of ‘DEV EDW’, Name of ‘DEV EDW’.
*** End of Error ***
Hope that helps shed some light on this issue.
Thanks Ben, it works for me
Oh my goodness! an incredible article dude. Thank you Nevertheless I am experiencing challenge with ur rss . Don’t know why Unable to subscribe to it. Is there anybody getting an identical rss downside? Anybody who is aware of kindly respond. Thnkx
A person who posted last week re the cost of steel sheet ,it realy depends where you go to buy it, galvinized is around seven forty pounds per metric ton and standard is around £650 per ton- hope this helps
I just wanted to develop a simple message to be able to express gratitude to you for all the pleasant advice you are writing on this website. My time consuming internet lookup has at the end of the day been recognized with incredibly good strategies to write about with my colleagues. I ‘d claim that most of us readers actually are unquestionably blessed to exist in a great website with many outstanding professionals with very beneficial ideas. I feel really lucky to have encountered the webpages and look forward to so many more amazing moments reading here. Thanks a lot once more for everything.
Youre so cool! I dont suppose Ive read something like this before. So nice to search out someone with some authentic thoughts on this subject. realy thanks for beginning this up. this web site is one thing that’s needed on the net, someone with a bit of originality. helpful job for bringing something new to the web!