Published
Mar 06 2019 02:45 PM
2,371
Views
First published on TECHNET on Jan 11, 2018
If anyone has been reading my blog for a while then they may have seen the various Project Server OLAP cube build failure postings over the years – but I hadn’t seen a really tricky one in a while, so thanks to Craig Sheppard from one of our aerospace customers for stumping me with this one – and Jason Eisenzimmer from our SQL team who came to the rescue. I also learned (or re-learned?) along the way a little more about the build process and which server is talking to which server at the various stages. This was 2013 – and in 2016 you wouldn’t have this same issue.
So lets start with the error:
Looking at the Analysis Services server at that point it had created the database ‘Test’ and it had defined the Data Source – but not the Data Source Views.
And drilling in to the Project Reporting data source we can view the connection string and even test the connection.
My PWA database just happens to be called PWAOData as I’m doing some other tests on OData on that instance – but this is nothing to do with OData. In the customer’s environment the Test Connection fails – yet if they change the Provider to the Native Client 11.0 everything works fine. Not a workaround as the cube build will always reset to 10.0 and fail. The failure indicated, like the cube build error, that the existing connection was forcibly closed by the remote host. After trying a different version of the 10.0 native client and not getting anywhere I engaged with the SQL team and Jason – who quickly established that it was a TLS 1.2 problem (the forcibly closed was a clue apparently) and it was confirmed that TLS 1.2 was being enforced in this environment. Jason suggested this patch https://support.microsoft.com/en-us/hotfix/kbhotfix?kbnum=3098860&kbln=en-us which support TLS 1.2 with SQL Native Client 10.0. And yes! The Test Connection worked! But the cube builds still failed…
.
I’d originally thought that this part of the cube database construction was all happening from Analysis Services but when Jason suggested that Project Server was potentially the issue I did a profiler trace on both the database engine and Analysis Services while I built a cube in my environment. This showed me that Project Server is still driving things and making the call to AS to create the Data Source – and then making the call to create the view – but before it can create the view it needs some information from the database – such as the time dimensions – and this was the precise part that was failing. So in fact fixing the ‘Test Connection’ part above was still entirely necessary as later AS would need to read the reporting tables – but we hadn’t got that far in the cube build.
A quick e-mail to Craig and the Project Server was patched with the same patch as above so that it could talk to the server with Native Client 10.0 and cubes were building just fine. As mentioned you wouldn’t see this in 2016 as the default Native Client is 11.0 – which is fine with TLS 1.2.
Thanks again Craig for your patience and being a great customer to work with – and Jason for your super SQL insights.
If anyone has been reading my blog for a while then they may have seen the various Project Server OLAP cube build failure postings over the years – but I hadn’t seen a really tricky one in a while, so thanks to Craig Sheppard from one of our aerospace customers for stumping me with this one – and Jason Eisenzimmer from our SQL team who came to the rescue. I also learned (or re-learned?) along the way a little more about the build process and which server is talking to which server at the various stages. This was 2013 – and in 2016 you wouldn’t have this same issue.
So lets start with the error:
Failed to build the OLAP cubes. Error: Error Adding DataSourceView 'Project Reporting data source' to database 'Test'. Error: Error Adding Table 'MSP_TimeByDay_OlapView' to database. Error: Client unable to establish connection
TCP Provider: An existing connection was forcibly closed by the remote host.
Looking at the Analysis Services server at that point it had created the database ‘Test’ and it had defined the Data Source – but not the Data Source Views.
And drilling in to the Project Reporting data source we can view the connection string and even test the connection.
My PWA database just happens to be called PWAOData as I’m doing some other tests on OData on that instance – but this is nothing to do with OData. In the customer’s environment the Test Connection fails – yet if they change the Provider to the Native Client 11.0 everything works fine. Not a workaround as the cube build will always reset to 10.0 and fail. The failure indicated, like the cube build error, that the existing connection was forcibly closed by the remote host. After trying a different version of the 10.0 native client and not getting anywhere I engaged with the SQL team and Jason – who quickly established that it was a TLS 1.2 problem (the forcibly closed was a clue apparently) and it was confirmed that TLS 1.2 was being enforced in this environment. Jason suggested this patch https://support.microsoft.com/en-us/hotfix/kbhotfix?kbnum=3098860&kbln=en-us which support TLS 1.2 with SQL Native Client 10.0. And yes! The Test Connection worked! But the cube builds still failed…
.
I’d originally thought that this part of the cube database construction was all happening from Analysis Services but when Jason suggested that Project Server was potentially the issue I did a profiler trace on both the database engine and Analysis Services while I built a cube in my environment. This showed me that Project Server is still driving things and making the call to AS to create the Data Source – and then making the call to create the view – but before it can create the view it needs some information from the database – such as the time dimensions – and this was the precise part that was failing. So in fact fixing the ‘Test Connection’ part above was still entirely necessary as later AS would need to read the reporting tables – but we hadn’t got that far in the cube build.
A quick e-mail to Craig and the Project Server was patched with the same patch as above so that it could talk to the server with Native Client 10.0 and cubes were building just fine. As mentioned you wouldn’t see this in 2016 as the default Native Client is 11.0 – which is fine with TLS 1.2.
Thanks again Craig for your patience and being a great customer to work with – and Jason for your super SQL insights.
6 Comments