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.

Annotations - No Copy, No Undo, No Delete confirmation?

In the Business Intellifgence studio, why can't I copy and paste an annotation, why is there no Delete confirmation, it's very easy to accidentally hit the delete option when you right click, worse still, if you do delete accidentally you can't Edit > Undo ? ..or have I missed something here?OK, i found out I can highlight the text, and use Ctrl+C on the keyboard to copy the text...no undo or delete confirmation is my main issue.

Animation Speed (Auto – Hide) Management Studio

Does anyone know how to set the Animation Speed for SQL Server Management Studio.

I think the setting is stored in the SQLShell settings file in the C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\profiles but adjusting the number up and down didn't seem to have any effect on the Auto-Hide speed.

Thanks

Kevin Haglund

1. Close SQL Server Management Studio.
2. Open this file in a text editor C:\Documents and Settings\<username>\My Documents\SQL Server Management Studio\Settings\CurrentSettings-2006-02-01.vssettings
3. Search for "AnimationSpeed" and replace the existing value (5) with your desired speed; 10 being the fastest choice, I do not know what is the lowest (1?). Save.
4. Open SQL Server Management Studio and it should now animate at your desired speed.

Siric

Angry user #2

I am also angry and frustrated with SQL 2005 Management Studio - here are some of the problems I'm having ...

1. When I look at the jobs on the summary page and click details I see only the name and status of the job - With Enterprise Manager I can see the name, status, category, last run date and next run date. Where's the rest of the info?

2. Occasionally I get a blank summary page with nothing on it - don't ask me how I managed to do it. Without it, I could not select multiple objects in object explorer for scripting for example - and why is that the case?

3. Under Management Studio how do I get access to Integration services and reporting services? Or do I use Visual Studio for those? Or should I be using Visual Studio for writing and debugging stored procs? I thought it was all integrated under Management Studio - but is Management Studio just for querying SQL databases and Business Intelligence Studio for that and other data sources? I'm confused by all the tools.

4. I would like to find a good tutorial for using this interface - the books online tutorial is sparse - but I never felt that I really needed one with EM.

5. On the positive side I like the new interface for modifying individual SQL objects - it's groups of objects that are giving me the most trouble.

regards Richard

1. Limited information on the summary page.
We've heard this from several different sources. The "Job Steps Execution History" report provides some of the information you listed. Other information requires you to select properties for the particular job.

2. Blank summary page.
I haven't heard of this happening. Can you consistently repro this or is it totally hit or miss?

3. Access to SSIS, SSAS, and SSRS from within SSMS
Connections are server specific. From the Object Explorer click on Connect and the drop down will list the different server types (AS, RS, IS, etc) you can connect to. From here the connection dialog will be displayed and you can choose the physical server to connect to. BI Studio is for developing BI applications not for managing the servers.

4. Good tutorial for SSMS
An update to the tutorials will be posted shortly but I don't know if this will address your needs. I haven't done an inventory of third party tutorials, but I'm sure you can find some out on SQL Server sites like SQL Junkies.

5. Groups of objects are giving me the most trouble
Can you elaborate on this?

Cheers,
Dan

|||

Dan -

Thanks for the reply just came back from the holidays

1. Thanks I eventually figured this out - thats ok.

2. Cant repro this.

3. I eventually found the Registered Servers windows which helped me greatly with managing multiple servers.

4. OK.

5. On the groups of objects - this isn't that big of deal - it's just surprising that I cannot highlight groups of objects in the Object Explorer - I must use the summary page for that. Thanks R

|||

#1, please use job activity monitor node under SQLServer Agent node

# 2. I am not sure the sceanrio you are refering to. For eg. If you are selecting Alerts node and there are no alerts, then the summary page is expected to be empty.

# 3. You can connect to Inetgration services and Reporting services from SQL Server Management studio(SSMS). For editing the packages you will need to use SQL BIDS(Business Intelligence Development Studio). VS can be used for debugging, it is not integrated in SSMS.

# 4. BOL has all the answers to your questions you have posted. If there is some "area" that you are looking for does not have answer, please let us know.

Thanks,

Gops Dwarak

Angry user #2

I am also angry and frustrated with SQL 2005 Management Studio - here are some of the problems I'm having ...

1. When I look at the jobs on the summary page and click details I see only the name and status of the job - With Enterprise Manager I can see the name, status, category, last run date and next run date. Where's the rest of the info?

2. Occasionally I get a blank summary page with nothing on it - don't ask me how I managed to do it. Without it, I could not select multiple objects in object explorer for scripting for example - and why is that the case?

3. Under Management Studio how do I get access to Integration services and reporting services? Or do I use Visual Studio for those? Or should I be using Visual Studio for writing and debugging stored procs? I thought it was all integrated under Management Studio - but is Management Studio just for querying SQL databases and Business Intelligence Studio for that and other data sources? I'm confused by all the tools.

4. I would like to find a good tutorial for using this interface - the books online tutorial is sparse - but I never felt that I really needed one with EM.

5. On the positive side I like the new interface for modifying individual SQL objects - it's groups of objects that are giving me the most trouble.

regards Richard

1. Limited information on the summary page.
We've heard this from several different sources. The "Job Steps Execution History" report provides some of the information you listed. Other information requires you to select properties for the particular job.

2. Blank summary page.
I haven't heard of this happening. Can you consistently repro this or is it totally hit or miss?

3. Access to SSIS, SSAS, and SSRS from within SSMS
Connections are server specific. From the Object Explorer click on Connect and the drop down will list the different server types (AS, RS, IS, etc) you can connect to. From here the connection dialog will be displayed and you can choose the physical server to connect to. BI Studio is for developing BI applications not for managing the servers.

4. Good tutorial for SSMS
An update to the tutorials will be posted shortly but I don't know if this will address your needs. I haven't done an inventory of third party tutorials, but I'm sure you can find some out on SQL Server sites like SQL Junkies.

5. Groups of objects are giving me the most trouble
Can you elaborate on this?

Cheers,
Dan

|||

Dan -

Thanks for the reply just came back from the holidays

1. Thanks I eventually figured this out - thats ok.

2. Cant repro this.

3. I eventually found the Registered Servers windows which helped me greatly with managing multiple servers.

4. OK.

5. On the groups of objects - this isn't that big of deal - it's just surprising that I cannot highlight groups of objects in the Object Explorer - I must use the summary page for that. Thanks R

|||

#1, please use job activity monitor node under SQLServer Agent node

# 2. I am not sure the sceanrio you are refering to. For eg. If you are selecting Alerts node and there are no alerts, then the summary page is expected to be empty.

# 3. You can connect to Inetgration services and Reporting services from SQL Server Management studio(SSMS). For editing the packages you will need to use SQL BIDS(Business Intelligence Development Studio). VS can be used for debugging, it is not integrated in SSMS.

# 4. BOL has all the answers to your questions you have posted. If there is some "area" that you are looking for does not have answer, please let us know.

Thanks,

Gops Dwarak

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 stops

Hi guys,

I'm using sql 2005(june ctp) and installed patches.
When I try to browse my processed cube in either management studio or dev't studio, the analysis services stops.

Below is the error message from the event log.
The connection either timed out or was lost.
Unable to read data from the transport connection: An existing connection
was forcibly closed by the remote host. (System)

Any idea. Are there any configuration that I need to do.

Any help will be appreciated.

Thanks,
I have the exact same problem when I try to process a large cube. The processing stops with same error message and the Analysis services i stopped on the server?

I havent found any reason og solution?

Thanks,

Analysis services stops

Hi guys,

I'm using sql 2005(june ctp) and installed patches.
When I try to browse my processed cube in either management studio or dev't studio, the analysis services stops.

Below is the error message from the event log.
The connection either timed out or was lost.
Unable to read data from the transport connection: An existing connection
was forcibly closed by the remote host. (System)

Any idea. Are there any configuration that I need to do.

Any help will be appreciated.

Thanks,
I have the exact same problem when I try to process a large cube. The processing stops with same error message and the Analysis services i stopped on the server?

I havent found any reason og solution?

Thanks,

Analysis Services Developer Studio Question

I had an obsolete dimension on my cube.

I delete the dimension, it disappears from the GUI (I can see all the other dimensions in the dimensions panel, but this one correctly disappears), yet when I try to build my project I get errors on this dimension. It's like the dimension was deleted from the GUI but still exists somewhere and is causing problems.

How do I resolve this without rebuilding my cube entirely? How do I delete this dimension completely?Are you sure that dimension is absolete ?
If it is not - error looks correct ...
What error do you receive ?

Analysis Services Cube Browser utility opening extremely slow

I have a relatively large cube that when I try to open it up via the cube browser utility in SQL Server Management Studio, it takes approximately 25 minutes for the cube browser utility to open up. If I want to query the database via MDX, the MDX writing utility (right-click on the database->New Query->MDX) comes back really fast (within a second).

What does the browser utility do differently that requires it to take so long versus the query utility?

Is there a way to speed it up?

Thanks

Hi there:

This is happening because the default query bein generated by the browser is taking a huge time to execute. Try using a simpler (read:less calculation intensive) measure as your cube default and watch the browser fly.

Hope that helps.

Cheers.

Suranjan Som

Senior BI Architect

Information Managment Group

|||

Unfortunately, this does not solve the problem. I turned on SQL Server Profiler and noticed that for each measure group in the cube (this particular cube has 12) it has to read in each partition. 6 of these measure groups have 48 combined partitions. Why does it need to read in each measure group? Unless there is a setting that I am missing, this is going to be an issue for anybody that has a large cube (billion plus rows) with multiple partitions.

Analysis Services 2005 Deployment Wizard

Hi,
I used the deployment wizard in the analysis services 2005 to convert
the cube into XML script. Using the SQL Management Studio I opened it
as Analysis Server scripts and used the local host as the system in the
connections windows then loaded the XML script into the Queries window
as a XMLA query. What has to be done after this in order to use it as a
production server?
Thanks,
Somu
Hi,
I was able to connect with a local machine but while synchronising the
dev and production servers i am getting an error "peer prematurely
closed the conn" , " error was encountered in the transport layer" Wat
should i do to over come this...
Thanks,
SOMU

Analysis Services 2005 Deployment Wizard

Hi,
I used the deployment wizard in the analysis services 2005 to convert
it into XML script. Using the SQL Management Studio I opened it as
Analysis Server scripts and used the local host as the system in the
connections windows then loaded the XML script into the Queries window
as a XMLA query. What has to be done after this in order to use it as a
production server?
Thanks,
SomuHi,
I was able to connect with a local machine but while synchronising the
dev and production servers i am getting an error "peer prematurely
closed the conn" , " error was encountered in the transport layer" Wat
should i do to over come this...
Thanks,
SOMU|||Hi,
I was able to connect with a local machine but while synchronising the
dev and production servers i am getting an error "peer prematurely
closed the conn" , " error was encountered in the transport layer" Wat
should i do to over come this...
Thanks,
SOMU

Analysis Services 2005 Deployment Wizard

Hi,
I used the deployment wizard in the analysis services 2005 to convert
the cube into XML script. Using the SQL Management Studio I opened it
as Analysis Server scripts and used the local host as the system in the
connections windows then loaded the XML script into the Queries window
as a XMLA query. What has to be done after this in order to use it as a
production server?
Thanks,
SomuHi,
I was able to connect with a local machine but while synchronising the
dev and production servers i am getting an error "peer prematurely
closed the conn" , " error was encountered in the transport layer" Wat
should i do to over come this...
Thanks,
SOMU

Analysis Services 2005 Authntication

Hi,

I have a small problem i.e. in SQL Server 2005 Management Studio i can connect to database engine as "Windows Authentication" as well as "Sql Server Authentication". But for connecting Analysis Services Database, Authentication is disabled and by default it is "Windows Authentication". To Enable the Authentication for logging as "SQL Server Authentication" what is the procedure to do.

Can you please help me in this.

Thanks

Dinesh

Hi,

As far as I was aware you can't enable it, based on the fact that the SQL Server logins are associated with a database engine not the Analysis Services engine.

http://technet.microsoft.com/en-us/library/ms144288.aspx

Near the end of that page

Specifying Analysis Services Authentication Mode

SQL Server 2005 Analysis Services supports only Windows Authentication.

Cheers

Matt

|||

Hello! SSAS2005 and earlier only supports windows authentication. Like Matt told you SQL Server authentication is a feature only in the database engine.

Regards

Thomas Ivarsson

|||How does this affect Reporting Services where Forms authentication is being used? We have changed our SSRS to use Forms Authentication although the report connects to an SSAS cube - I am now having problems connecting to this data source. Is it possible to have SSAS also use Forms Authentication?|||

I am not familiar with the Forms Authentication, but because Analysis Services only supports integrated authentication, would it be possible for you to impersonate the specified Windows user (with the Windows account name and password specified, I assume, in the forms) in the application that connects to SSAS ?