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…