SSAS Processing Failure; OLE DB or ODBC error – 08001

Today I faced a new error type that I’d never seen before in my many years of experience with SSAS. It was one of the most weird errors that I’ve seen so far.

let me explain that in a scenario; I had a SSAS multi-dimensional cube working and fully processing on the DEV server. then I just copied that to UAT server with a simple backup and restore.

I’ve changed the data source connection from DEV database engine to UAT database engine, and processed, but I’ve got a very early processeing error as below:

OLE DB error: OLE DB or ODBC error: A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.; 08001; Client unable to establish connection; 08001; Encryption not supported on the client.; 08001.

So I did my troubleshooting as steps below; 

As the error says the problem is in connection to SQL Server. so I’ve checked UAT SQL Server DB engine connection and it worked both local and remote. then I compared connection configuration of UAT DB Engine server and compared it to DEV server, they were completely same. Version, edition of sql server, collations and other configuration all were exactly similar!

I’ve thought that problem might be exists in the impersonation account, and I’ve changed it with different options but no luck.

I’d opened SSDT and connected the SSAS project to UAT, and started processing. Unfortunately I’ve got same error there!

I did some google searches and I’ve found solutions like this: http://geekswithblogs.net/LifeLongTechie/archive/2012/06/21/ssas-processing-error-client-unable-to-establish-connection-08001-encryption.aspx

Which says that change the connection provider from SQL Native Client to OLE DB provider. 

Solution seems to be useful in some cases – for example for the blog author and some others -, But It didn’t worked for me. I’ve tried all types of connection providers but no luck. 

I’d started a trace on SQL Profiler to see which account used for the connection, and I thought that account might cause the problem. But surprisingly I’ve seen that nothing logged into SQL Profiler trace that tracked DB engine! 

Then I’d changed my target SSAS server for deployment to DEV server and processed the Dev Server’s cube with data from UAT DB Engine. It was working fine! So there were no doubt for me that there should be something wrong on SSAS service instance of the UAT server.

I’ve checked SSAS server properties on UAT, every thing was fine and OK and equally similar to DEV SSAS server configuration.

Then I’ve found this blog post: http://blogs.msdn.com/b/jsocha/archive/2011/05/27/fixing-cube-processing-odbc-errors.aspx

Which says that change the SSAS Service account to a local account, and restart the service.

Before doing that I’ve just tried this one; Just restarted the SSAS Service on UAT. and then I processed the cube. That was the keystone! It processed successfully! I couldn’t believe that; I’d spend about 2 hours to figure out the problem, and it solved just with a simple SSAS Server restart!

So I’d thought that maybe it would be better to list what I’ve done in this blog post, maybe it helps some of you. I hope you could get rid of this error much faster with help of this post.

If I list required efforts to figure out the issue I say;

1- Check source SQL Server DB engine connection to work

2- deploy SSAS project to another SSAS server and see is it working or not

3- change SSAS Service account to a local one, and restart (sometime, just do the restart service!)

4- try other connection providers such as OLE DB, Native Client…

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.