locked
Connect to Analysis Services 2008 instance name RRS feed

  • Question

  • Hi,

    I have the following scenario:

    1). One non domain server having Windows Server 2008 R2 x64 edition with Analisys Services 2008 R2 (or 2008) instance name. The service is running under local system service account.

    2). One non domain client having Windows 7 Profesional Edition trying to connect to this instance name from Microsoft Excel 2007 (or Tableau Software client application).

    3).  Firewall is off on both stations.

    4). SQL Browser is running under local system service account.

    5). I have installed on both stations Microsoft® Analysis Services OLE DB Provider for Microsoft® SQL Server® 2008 R2.

    6). No errors are logged in event viewer.

    When I try to connect i get the error "Analisys Services database error 0x80004005: Errors in OLE DB provider. Could not connect to the redirector. Ensure that SQL Browser service is running...."

    Authentication is made through a specific Windows user with local administrator privileges created on the Analisys Services server, not using integrated security. It always works when Analisys Services is not instance named. The only difference is the instance.

    Regards
    Dbaragan

    Wednesday, January 12, 2011 4:14 PM

Answers

  • Hi,

    yes, it should be possible to connect to a named instance  using SQL Browser and "servername\instancename".
    The above questions cover the most commen issues.

    You could try to start SQL Browser with -c parameter from cmd promt to get more information about any possible errors.
    http://msdn.microsoft.com/en-us/library/ms181087.aspx (make sure that only one instance of SQL Browser is running)

    Also you could run ProcessMonitor during reproducing the error to see if the access to some folder or file was denied.

    Additionally when SQLBrowser is running check if the port 2382 is accessable (you can use PortQry tool or netstat -aob)
    What about the SSAS server setting InstanceVisible? http://msdn.microsoft.com/en-us/library/ms174906.aspx

    HTH,
    Orsi


    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by Raymond-Lee Wednesday, January 26, 2011 10:08 AM
    Tuesday, January 18, 2011 9:20 AM
    Answerer
  • Here are a couple of other things to try which ended up helping me at several clients. Once I got it working, I didn't research further to really find the root cause, so I may have worked around the issue with the following instead of making the most straightforward fix. Not sure. Not all of these suggestions apply to your specific setup, but I wanted to post my experience on this somewhere. Anyway...

    1. If you've got Kerberos in your scenario (double hops or maybe even are using EffectiveUserName) then try setting SPNs for SQL Browser in addition to SPNs for the SSAS service.

    2. Obviously make sure SQL Browser is running.

    3. One time changing SQL Browser from using Local System to using the standard domain account (used for all SQL services) solved the problem.

    4. If all else fails, you can edit the Port setting on the SSAS instance and change it from 0 (meaning let SSAS decide) to a specific port. Then you can connect with "servername:port" without specifying the instance name.

    5. Review this good document: http://msdn.microsoft.com/en-us/library/cc917670.aspx

    6. Review this good document (which was on a site that was taken down, thus why we're having to use archive.org's cache): http://web.archive.org/web/20071018035915/http://www.sqljunkies.com/WebLog/edwardm/archive/2006/05/26/21447.aspx

    There's some overlap with Orsolya's troubleshooting steps, but I thought I'd mention my experience with this.

    PLEASE post back with what you find so others can benefit from your experience.


    http://artisconsulting.com/Blogs/GregGalloway
    • Marked as answer by Raymond-Lee Wednesday, January 26, 2011 10:08 AM
    Tuesday, January 18, 2011 2:13 PM

All replies

  • Hi,

    Are you able to connect locally on the SSAS Server?
    Can you connect with port number instead of instance name (servername:port)?
    Do you see any messages for the SQLBrowser in the Application log on the SSAS Server?
    Do you have a C:\Program Files (x86)\Microsoft SQL Server\90\Shared\ASConfig\msmdredir.ini file?
    Does it have the instance and port for this named instance in it?
    Does the local system account have full control to the C:\Program Files (x86)\Microsoft SQL Server\90\Shared\ASConfig folder?

    HTH,
    Orsi


    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Proposed as answer by Raymond-Lee Friday, January 14, 2011 7:41 AM
    Wednesday, January 12, 2011 5:06 PM
    Answerer
  • Thank you Orsi,

    Your ideas were very helpfull. I did have the msmdredir.ini file in the specified location. There were no entries in the Application log and the local system account has full control on ASConfig folder. However, I moved the instance on a specific port and I disabled SQL Browser service. I am able to connect to my instance using servername:port. Shouldn't it worked with "servername\instancename" when SQL Browser service was enabled?

    Regards
    Dbaragan

    Friday, January 14, 2011 12:57 PM
  • Hi,

    yes, it should be possible to connect to a named instance  using SQL Browser and "servername\instancename".
    The above questions cover the most commen issues.

    You could try to start SQL Browser with -c parameter from cmd promt to get more information about any possible errors.
    http://msdn.microsoft.com/en-us/library/ms181087.aspx (make sure that only one instance of SQL Browser is running)

    Also you could run ProcessMonitor during reproducing the error to see if the access to some folder or file was denied.

    Additionally when SQLBrowser is running check if the port 2382 is accessable (you can use PortQry tool or netstat -aob)
    What about the SSAS server setting InstanceVisible? http://msdn.microsoft.com/en-us/library/ms174906.aspx

    HTH,
    Orsi


    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by Raymond-Lee Wednesday, January 26, 2011 10:08 AM
    Tuesday, January 18, 2011 9:20 AM
    Answerer
  • Here are a couple of other things to try which ended up helping me at several clients. Once I got it working, I didn't research further to really find the root cause, so I may have worked around the issue with the following instead of making the most straightforward fix. Not sure. Not all of these suggestions apply to your specific setup, but I wanted to post my experience on this somewhere. Anyway...

    1. If you've got Kerberos in your scenario (double hops or maybe even are using EffectiveUserName) then try setting SPNs for SQL Browser in addition to SPNs for the SSAS service.

    2. Obviously make sure SQL Browser is running.

    3. One time changing SQL Browser from using Local System to using the standard domain account (used for all SQL services) solved the problem.

    4. If all else fails, you can edit the Port setting on the SSAS instance and change it from 0 (meaning let SSAS decide) to a specific port. Then you can connect with "servername:port" without specifying the instance name.

    5. Review this good document: http://msdn.microsoft.com/en-us/library/cc917670.aspx

    6. Review this good document (which was on a site that was taken down, thus why we're having to use archive.org's cache): http://web.archive.org/web/20071018035915/http://www.sqljunkies.com/WebLog/edwardm/archive/2006/05/26/21447.aspx

    There's some overlap with Orsolya's troubleshooting steps, but I thought I'd mention my experience with this.

    PLEASE post back with what you find so others can benefit from your experience.


    http://artisconsulting.com/Blogs/GregGalloway
    • Marked as answer by Raymond-Lee Wednesday, January 26, 2011 10:08 AM
    Tuesday, January 18, 2011 2:13 PM