Linked Servers and Stand alone Analysis Services 2000 Servers

I had an interesting experience this afternoon which I thought I would share in the hopes that it may save someone else the pain I went through.

Today I moved a production Analysis Services server off a machine which was running both SQL Server and Analysis Services onto it's own machine for one of my clients.

The repository was in SQL Server, so after installing AS we simply copied the data files off the old server, changed the repository connection string and we had our new server up and running with all the databases off the old server.

The first issue we had was that we wanted to setup the server using a DNS alias (CNAME) rather than the name of the new server. This should allow us to upgrade the machine in future without having to change any connection strings or reports, we could simple change where the alias was pointing. The only issue with this was that it did not work! Well, not the at first. We came across KB article 888526 on Technet which seemed to describe our problem exactly. However after following all the steps we still could not get the server working, it turns out they left out the last step - reboot the machine.

Then we changed all the places where the old server name was referenced. The following is a list of all the things we had to touch:

  • Connections strings in various applications
  • Reporting Services data sources
  • DTS Process Olap Objects Tasks
  • DTS ActiveX scripting tasks that used the DSO library
  • Excel PivotTable reports
  • SQL Server Linked Servers

The last item Linked Servers in SQL Server was the actual issue that prompted this post. One or two of the more complex Reporting Services reports use stored procedures and the OPENQUERY() function to build complicated, parameterized MDX queries, often linked back to relational data. The reports that use this technique were all tested from Reporting Services and worked fine, but when one of the developers when to work on one of the stored procedures we were getting “... database does not exist” errors.

Now this kept me scratching my head for a while, the reports would run, but if we ran the underlying stored procedure from Query Analyzer it failed where it had been working before we moved Analysis Services. I tracked it down to the fact that the RS data source was using a SQL login. Sure enough, when we connected to the server in Query Analyzer using a SQL login, the stored procedures worked.

I think that what was happening with the Windows authentication was that by moving the Analysis Services service we had introduced a “second hop”. It is a security limitation of the NTLM (Windows) authentication, that it will not do more than one hop. That is, Machine A can connect to Machine B (one hop), but Machine B cannot then connect to Machine C on behalf of the user on Machine A (second hop).

When SQL and Analysis Services were on the same machine this was not an issue. The client on Machine A connected to the SQL Server on Machine B which connected to Analysis Services which was still on Machine B. Now that we had introduced Machine C the user credentials were not being passed between Machine B and Machine C.

So, why did SQL logins work at all? Especially given the fact that Analysis Services uses Windows users/groups exclusively to control it's access permissions. Well when you connect using a SQL login the SQL Server then connects to Analysis Services using the account that the SQL Server services is running under. So the user connects to the SQL Server using a SQL Login and the SQL Server then issues the query to the linked server using it's own identity.

I could not find any other security settings on the Linked Server that would work. The only way we could get things working was to login using a SQL Login with the SQL Server service is set up to use a domain account that has access to query the OLAP cubes. So if you are ever stuck in the same situation hopefully the same setup should work for you.

Print | posted on Tuesday, January 31, 2006 7:44 PM