Another - Cannot connect to Database Issue.

I've read several issues and resolutions related to my problem, but none of the resolutions have worked.

I have Visual Studio 2005 installed on my local machine.

My SQL Server 2005 is on a Member Server.

On a fresh Web application, I have no problem accessing the ASP.NET Configuration File to set up Users and Roles. The reason is obvious, the data is in SQLExpress and Local. You can see the database ASPNETDB.MDF residing under the App_Data Folder of the project.

How do I get this in the SQL Server 2005 System on the Member server?

Whenever I do the Copy Web Site procedure to post my application to the Web Server, I get an error stating that SQLExpress is not installed on the target server. That's correct because my target server has SQL 2005.

I've tried changing the Machine.Config. I've tried changing the Web.Config. Neither solution was successful. Both send me errors that no connection can be made.

I've also seen various recommendations concerning the ASPNET database. One camp says store your members and roles in the same database as your Data. The other camp says keep them seperate. Is there a rhyme or reason for either one? The data housed in this Database is what I'm after but how in the world to I get there?

I am on week #4 trying to get something going. I'm starting to feel like Thomas Edison when he said he discovered 900 different ways his experiment did not work.

Hi,

you will have to copy the database to the remote server, attach it on the (non SQL Server Express) system using the GUI or sp_attachdb. THen you will have to change the connectionstring not to use the UserInstance anymore (because you now will have the instance connected to a server instance rather than a user instance).

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

OK. For my case, I think I found the solution.

Here is the situation:

SQL Server 2005 is on a member Server.

VS 2005 is on Windows XP Work Station.

Development project is on Windows XP Work Station.

When running ASP.NET Configuration, all Users, roles, etc. end up in the App_Data folder in the ASPNETDB database. After testing application, it is copied to Server but the ASPNETDB errors out because it is looking for SQLExpress and not SQL Server 2005.

Here is what I did to get around the issue:

1. Made sure the SQL Server DB had the ASPNETDB.

if it is not there,

run aspnet_regsql.exe

click next

Select Configure SQL server for applications

Click next

Add the server name and LEAVE the database as <default>

Click next

Click next

That will create the ASPNETDB.

2. From inside my application, used Server Explorer to Connect to the SQL ASPNETDB database.

3. Right click on the ASPNETDB, select properties, locate connection string, and copy connection string.

4. Inside my Web.Config file, I created a new connection string as such.

<appSettings/>

<connectionStrings>

<remove name="LocalSqlServer"/>

<add name="LocalSqlServer" connectionString="Data Source=<myServer>;Initial Catalog=aspnetdb;Integrated Security=True;"/>

</connectionStrings>

5. I deleted the ASPNETDB from the app_data folder on my local drive.

Now when I run ASP.NET Configuration or my application, I'm posting data to the SQL Server and not the App_Data folder.

While looking for solutions, I ran across a couple of sites that recommended having users and roles inside the application database. Other recommended using the ASPNETDB database. I never did find a specific answer for one way or the other. All I know, what I have is now working and after 4 weeks of frustration, I'm going to stick with this for a while until someone can show me a better way.

Analysis Services through Management Studio: cannot connect. Possible workaround.

I have a SQL server 2005 developer edition installation on my Windows XP SP2 workstation. SQL Server was installed after Visual Studio 2005 pro, and therefore after SQL Express. From what I recall, SQL Browser was also installed with SQL Express using the Local System account as the Browser service account.

As per best-security practice, I have installed 2005 developer edition with the accounts for the services as local accounts (different account for each service). Obviously, due to the VS2005 install, Browser was installed using the Local System account. I therefore created a local account for the Browser, and put it into the SQLServer2005SQLBrowserUser$MACHINENAME security group. I then switched the Browser service account to the local account using Configuration Manager, which therefore restarted the Browser service.

Next, when accessing Analysis Services (SSAS) through the management studio (SSMS) I got the following error:

Cannot connect to MACHINENAME\INSTANCENAME
|
--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 (System)

Changing the Browser logon account back to Local System allowed me to connect to SSAS through SSMS (after a Browser service restart).

Reading BOL for 2005, it states that the security permissions required for the Browser service account are:
90\shared\msmdlocal.ini
Read
90\shared
Read, Execute
90\shared\Errordumps
Read, Write
Looking at those folders, I could see that this was the set correctly, as implied through the SQLServer2005SQLBrowserUser$MACHINENAME security group membership which SYSTEM (set through the installation of SQL express, presumably) and the local account were members of. msmdlocal.ini does not exist on my installation.

However, the major clue that I could see was that SYSTEM account had Full access to the Shared and ErrorDumps folders by default; so I then elevated the SQLServer2005SQLBrowserUser$MACHINENAME security permissions to Full on the Shared and ErrorDumps folders, set the Browser service back to the local user account and restarted it. Bingo! I can now access the SSAS through SSMS.

Better still, I immediately reduced the SQLServer2005SQLBrowserUser$MACHINENAME to the recommended settings, restarted the Browser service, and I can still access SSAS through SSMS.

My question is ... why? What happened during my restart under elevated permissions that let the Browser service work properly? I suspect it is something along the lines of the local account taking ownership of something :-s, although I really have no idea. Is this a recognised problem? Is there a KB article on it? Do we have to do similar things to this when we change service accounts for other SQL services?

Andy

Did you use SQL Server Configuration Manager to change account or Windows Computer Manager?

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Configuration Manager. Though I take it you mean the Service console snap-in rather than Computer Manager directly? However the answer is still SSCM.

Andy

|||

In this case I dont see any explanation to the behavior you describe.

Can you please try an contact Customer support about this situation.

Thanks.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||I'll set up a virtual machine and try and reproduce it. Once I know I can I will get in touch with customer support.|||

There was something in your original description that may explain what is going on. According to your 1st post, you:

1. Change the account of the SQL Browser

2. Then added the account to the SQL Browser User group.

3. Then updated the account through SQL Computer Manger.

You should have only done step 3.

By doing 1 and 2, the SQL Computer Manger could have been tricked into thinking you only wished to changes the password. SQL Computer Manager needs to do other operation, individual to each service, than change the account and update the user group.

This also fits with the problem "going away" after you "fixed" the problem.

Does this seem like a possibility?

|||

I had the exact problem as you described, SQL Browser started fine, allowed access to a SQL instance, but actively refused connection to a SSAS instance.

Switching to System solved the problem, but the permanent fix was to increase the permissions to the Shared + Error dump folders as described above.

|||

Thanks so much for posting the details of your experience! I had the same problem and was able to fix it because of your post.

Cheers!

Analysis Services through Management Studio: cannot connect. Possible workaround.

I have a SQL server 2005 developer edition installation on my Windows XP SP2 workstation. SQL Server was installed after Visual Studio 2005 pro, and therefore after SQL Express. From what I recall, SQL Browser was also installed with SQL Express using the Local System account as the Browser service account.

As per best-security practice, I have installed 2005 developer edition with the accounts for the services as local accounts (different account for each service). Obviously, due to the VS2005 install, Browser was installed using the Local System account. I therefore created a local account for the Browser, and put it into the SQLServer2005SQLBrowserUser$MACHINENAME security group. I then switched the Browser service account to the local account using Configuration Manager, which therefore restarted the Browser service.

Next, when accessing Analysis Services (SSAS) through the management studio (SSMS) I got the following error:

Cannot connect to MACHINENAME\INSTANCENAME
|
--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 (System)

Changing the Browser logon account back to Local System allowed me to connect to SSAS through SSMS (after a Browser service restart).

Reading BOL for 2005, it states that the security permissions required for the Browser service account are:
90\shared\msmdlocal.ini
Read
90\shared
Read, Execute
90\shared\Errordumps
Read, Write
Looking at those folders, I could see that this was the set correctly, as implied through the SQLServer2005SQLBrowserUser$MACHINENAME security group membership which SYSTEM (set through the installation of SQL express, presumably) and the local account were members of. msmdlocal.ini does not exist on my installation.

However, the major clue that I could see was that SYSTEM account had Full access to the Shared and ErrorDumps folders by default; so I then elevated the SQLServer2005SQLBrowserUser$MACHINENAME security permissions to Full on the Shared and ErrorDumps folders, set the Browser service back to the local user account and restarted it. Bingo! I can now access the SSAS through SSMS.

Better still, I immediately reduced the SQLServer2005SQLBrowserUser$MACHINENAME to the recommended settings, restarted the Browser service, and I can still access SSAS through SSMS.

My question is ... why? What happened during my restart under elevated permissions that let the Browser service work properly? I suspect it is something along the lines of the local account taking ownership of something :-s, although I really have no idea. Is this a recognised problem? Is there a KB article on it? Do we have to do similar things to this when we change service accounts for other SQL services?

Andy

Did you use SQL Server Configuration Manager to change account or Windows Computer Manager?

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Configuration Manager. Though I take it you mean the Service console snap-in rather than Computer Manager directly? However the answer is still SSCM.

Andy

|||

In this case I dont see any explanation to the behavior you describe.

Can you please try an contact Customer support about this situation.

Thanks.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||I'll set up a virtual machine and try and reproduce it. Once I know I can I will get in touch with customer support.|||

There was something in your original description that may explain what is going on. According to your 1st post, you:

1. Change the account of the SQL Browser

2. Then added the account to the SQL Browser User group.

3. Then updated the account through SQL Computer Manger.

You should have only done step 3.

By doing 1 and 2, the SQL Computer Manger could have been tricked into thinking you only wished to changes the password. SQL Computer Manager needs to do other operation, individual to each service, than change the account and update the user group.

This also fits with the problem "going away" after you "fixed" the problem.

Does this seem like a possibility?

|||

I had the exact problem as you described, SQL Browser started fine, allowed access to a SQL instance, but actively refused connection to a SSAS instance.

Switching to System solved the problem, but the permanent fix was to increase the permissions to the Shared + Error dump folders as described above.

|||

Thanks so much for posting the details of your experience! I had the same problem and was able to fix it because of your post.

Cheers!

Analysis Services Cube Editor

How does one create outer joins in the cube editor. The documentation state
s
that by default only inner joins are created and to use Visual Database Tool
s
( a Visual Studio module). Any workarounds for this?
thanks in advanceThe most flexible way to do this is to create a view on the relational
database-side.
Analysis Services loads from a view as well as it can from a base table.
Remember however, that you should explicitly cast NULLs to specific values
from the outer join.
Analysis Services treats NULLs as 0's (in measures) and as unknown values
(as dimension keys).
In SQL Server 2005, we have expanded the semantics for handling NULLs, but
for now in SQL Server 2000, we strongly recommend that you specifically cast
NULLs to some pre-defined value prior to exposing the data to Analysis
Services. You will just be a lot happier if you do this.
Hope that helps.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Homer" <Homer@.discussions.microsoft.com> wrote in message
news:8C2079B0-3156-485E-94B4-86A0A7A1F019@.microsoft.com...
> How does one create outer joins in the cube editor. The documentation
states
> that by default only inner joins are created and to use Visual Database
Tools
> ( a Visual Studio module). Any workarounds for this?
> thanks in advance

Analysis Services Cube Editor

How does one create outer joins in the cube editor. The documentation states
that by default only inner joins are created and to use Visual Database Tools
( a Visual Studio module). Any workarounds for this?
thanks in advance
The most flexible way to do this is to create a view on the relational
database-side.
Analysis Services loads from a view as well as it can from a base table.
Remember however, that you should explicitly cast NULLs to specific values
from the outer join.
Analysis Services treats NULLs as 0's (in measures) and as unknown values
(as dimension keys).
In SQL Server 2005, we have expanded the semantics for handling NULLs, but
for now in SQL Server 2000, we strongly recommend that you specifically cast
NULLs to some pre-defined value prior to exposing the data to Analysis
Services. You will just be a lot happier if you do this.
Hope that helps.
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Homer" <Homer@.discussions.microsoft.com> wrote in message
news:8C2079B0-3156-485E-94B4-86A0A7A1F019@.microsoft.com...
> How does one create outer joins in the cube editor. The documentation
states
> that by default only inner joins are created and to use Visual Database
Tools
> ( a Visual Studio module). Any workarounds for this?
> thanks in advance