Home Dashboard Directory Help
Search

SQL Browser cannot connect to named instances of Analysis Services by AaronBertrand


Status: 

Closed
 as Duplicate Help for as Duplicate


6
0
Sign in
to vote
Type: Bug
ID: 679792
Opened: 7/18/2011 9:38:59 AM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

You keep closing these as not reproducible (as least four similar items going back as July 2005), so I'm going to throw another one on the pile, because I'm seeing the exact same issue in Denali CTP3. The difference may be that I installed a named instance first.

Essentially, I can connect just fine to a default instance of Analysis Services, but I cannot connect to a named instance. Regardless of how I try to connect (PowerShell, C#, SSMS, etc), I get a similar error. From PowerShell:

[System.Reflection.Assembly]::LoadWithPartialName( "Microsoft.AnalysisServices")
$server = new-object Microsoft.AnalysisServices.Server
$server.Connect("DataSource=.\InstanceName")

Exception calling "Connect" with "1" argument(s): "A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running."

From SSMS:

------------------------------
TITLE: Connect to Server
------------------------------
Cannot connect to .\NamedInstance.
------------------------------
ADDITIONAL INFORMATION:
A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. (Microsoft.AnalysisServices.AdomdClient)
------------------------------
No connection could be made because the target machine actively refused it 127.0.0.1:2382 (System)
------------------------------
BUTTONS:
OK
------------------------------

I have of course verified that the browser service is running. I've tried turning all levels of Windows Firewall off, and still no go. There is no external firewall. This is all on a single machine. I've tried (at Buck Woody's suggestion) to change the service account to LOCAL SYSTEM. I can't set the InstanceVisible property because that requires a connection via SSMS (catch-22) and there is no such setting in the registry (either not persisted there, or saved as some other name).

I hope that, given the number of people who have reported this issue, you'll start taking it seriously instead of just dismissing it as no repro every time.
Details
Sign in to post a comment.
Posted by Microsoft on 7/20/2011 at 3:08 PM
With VMs popping up, we see this more often. Thanks for you help in helping me confirm my suppositions.

jkh
Posted by AaronBertrand on 7/20/2011 at 3:03 PM
Hmm, previous comment went into the ether. Thanks for your persistence, it seems to be working now. Sorry I failed to recognize that the computer was in fact renamed after it was cloned.
Posted by Microsoft on 7/20/2011 at 8:38 AM
Glad to see we are making progress.

Now give SQLServer2005SQLBrowserUser$WIN-4LAQ25N3QAL write permissions on the asconfig folder, restart the box (probably need only restart browser and AS, but who knows what order to do it in) and see if you can manage your instance.
Posted by AaronBertrand on 7/20/2011 at 6:17 AM
Ah now I see, the computer *was* renamed (long before I used it - it was a cloned VM in our test environment). This seems to have affected the SQLBrowserUser account, as it is using the name Windows pre-generated when the VM was created.
Posted by AaronBertrand on 7/20/2011 at 6:16 AM
asconfig (OI)(CI)(F)
         VM-DENALI\SQLServerMSASUser$VM-DENALI$DENALICTP3:(OI)(CI)(F)
         VM-DENALI\SQLServerMSASUser$VM-DENALI$MSSQLSERVER:(OI)(CI)(F)
         VM-DENALI\SQLServer2005SQLBrowserUser$WIN-4LAQ25N3QAL:(I)(OI)(CI)(RX)
         NT SERVICE\TrustedInstaller:(I)(F)
         NT SERVICE\TrustedInstaller:(I)(CI)(IO)(F)
         NT AUTHORITY\SYSTEM:(I)(F)
         NT AUTHORITY\SYSTEM:(I)(OI)(CI)(IO)(F)
         BUILTIN\Administrators:(I)(F)
         BUILTIN\Administrators:(I)(OI)(CI)(IO)(F)
         BUILTIN\Users:(I)(RX)
         BUILTIN\Users:(I)(OI)(CI)(IO)(GR,GE)
         CREATOR OWNER:(I)(OI)(CI)(IO)(F)
Successfully processed 1 files; Failed processing 0 files
Posted by Microsoft on 7/19/2011 at 2:33 PM
Could you run icacls on your asconfig folder and send me the result? Like this:

c:\Program Files (x86)\Microsoft SQL Server\90\Shared>icacls asconfig
asconfig JHOWEY61\SQLServer2005SQLBrowserUser$JHOWEY61:(OI)(CI)(NP)(Rc,S,WD,AD)
         JHOWEY61\SQLServerMSASUser$JHOWEY61$EVALR2:(OI)(CI)(F)
         JHOWEY61\SQLServer2005SQLBrowserUser$JHOWEY61:(I)(OI)(CI)(RX)
         NT SERVICE\TrustedInstaller:(I)(F)
         NT SERVICE\TrustedInstaller:(I)(CI)(IO)(F)
         NT AUTHORITY\SYSTEM:(I)(F)
         NT AUTHORITY\SYSTEM:(I)(OI)(CI)(IO)(F)
         BUILTIN\Administrators:(I)(F)
         BUILTIN\Administrators:(I)(OI)(CI)(IO)(F)
         BUILTIN\Users:(I)(RX)
         BUILTIN\Users:(I)(OI)(CI)(IO)(GR,GE)
         CREATOR OWNER:(I)(OI)(CI)(IO)(F)

Successfully processed 1 files; Failed processing 0 files

c:\Program Files (x86)\Microsoft SQL Server\90\Shared>
Posted by AaronBertrand on 7/19/2011 at 1:56 PM
The computer was NOT renamed, and the service was running as a domain administrator account.
Posted by Microsoft on 7/19/2011 at 1:47 PM
regarding your problem connecting to the named instance of AS, you indicate that there was a former installation of 2008 on the box. Is it possible that the box was renamed at some point between the 2008 install and the AS Denali install? If so, I believe this has been fixed for RTM.

As a workaround, you can navigate to program files (x86)\microsoft sql server\90\shared\asconfig folder and check permissions. You want to give the sql browser service account write access to the contents of the folder. I would be interested to know if the service account you find includes the present or a previous name for the computer. If former, that would confirm my hypotheses, else, we likely have something else going on.

Keep me posted.

Thanks,

jkh
Posted by Microsoft on 7/19/2011 at 11:31 AM
OK this sounds like a different bug if the redir file is dropped, we will have to take a look. Thanks for all the additional info.

Regards,
Cathy Dumas
Posted by AaronBertrand on 7/18/2011 at 12:27 PM
I am also able to connect to the named instances using the name instead of the port number if I stop the SQL Browser service, then start it from a command line using the -c switch. Hopefully that helps resolve the issue or at least helps you reproduce it.
Posted by AaronBertrand on 7/18/2011 at 11:31 AM
Sorry, I stand corrected. There is such a file in 90, I had restricted my search to the 110 subset. If I've only installed 2008 and Denali on this system, why does anything go to a 2005-based folder structure?

Anyway once I found the ports I was able to connect by using, e.g. from PowerShell:

$server.Connect(".:50560")

Where 50560 was the port being used for the named instance. But I think you must agree that this is a mess. Isn't this what the Browser service is supposed to do for me? I shouldn't have to look up ports in some INI file in order to connect to SSAS.
Posted by AaronBertrand on 7/18/2011 at 11:26 AM
Yes there was a default instance of SQL Server 2008 which I removed to make room for Denali.

SQL Browser service was already running as LOCAL SYSTEM. I changed it to run as the domain administrator account and this did not correct anything.

In Denali, where do I see what port SSAS is listening on? There is no msmdredir.ini file on the system, not did any ASConfig folder get set up. The closest I could find was msdmsrv.ini and every instance has the entry <Port>0</Port>.
Posted by Microsoft on 7/18/2011 at 11:17 AM
Sorry, "Who is the version running the SQL Browser service" should read "Who is the USER running the SQL Browser service".

thanks
Cathy
Posted by Microsoft on 7/18/2011 at 11:16 AM
Hi Aaron,

I think I have seen a similar issue, can you please provide me with some more information to see if it is a duplicate.

Did you have a previous version of SQL installed, including any SQL Express version installed with Visual Studio, before installing Denali?

Is there a file called msmdredir.ini in C:\Program Files (x86)\Microsoft SQL Server\90\Shared\ASConfig?

Who is the version running the SQL Browser service? If you change the user running the SQL Browser service to yourself or to the Local System account and restart the service, does this solve the problem?

Thanks,
Cathy Dumas
Program Manager, Analysis Services

Can you al

Sign in to post a workaround.
File Name Submitted By Submitted On File Size  
SSMS.png 7/18/2011 74 KB
PowerShell.png 7/18/2011 6 KB