Connection Problems

[Jonathan Wood]

I'm a long-time programmer new to server administration and SQL. For some
reason, this stuff just doesn't seem to ever work for me.

I'm developing an ASP.NET application in Visual Studio 2008. I recently
upgraded from SQL Server 2005 Express to SQL Server 2005 (with SP2).

It took Microsoft technical support about three hours to figure out why SQL
Server Management Studio was unable to log onto SQL Server, so I can finally
do that. (The proper accounts were not created.) But I am unable to access
SQL Server from Visual Studio.

1. In VS2008 Server Explorer, I was able to modify the database connection
so that it works with SQL Server 2005 instead of SQL Server Express.
However, every time I reload Visual Studio, I have to make these changes
again. Does anyone know how to get VS2008 to remember these settings?

2. When I select the ASP.NET Configuration command, it's horribly slow and
tells me there are no longer any users. (I can see them looking at the
aspnet_Membership table.) It also now calls the application "/<appname>"
whereas before it was just "/".

Under the Security tab, it says:

"There is a problem with your selected data store. This can be caused by an
invalid server name or credentials, or by insufficient permission. It can
also be caused by the role manager feature not being enabled. Click the
button below to be redirected to a page where you can choose a new data
store.

The following message may help in diagnosing the problem: An error has
occurred while establishing a connection to the server. When connecting to
SQL Server 2005, this failure may be caused by the fact that under the
default settings SQL Server does not allow remote connections. (provider:
SQL Network Interfaces, error: 26 - Error Locating Server/Instance
Specified)"

When I click Choose Data Store, I have the option of "Select a single
provider for all site management data" and "Select a different provider for
each feature (advanced)".

I choose the first one and I get a page that says No providers created.

I'm also struggling with the connection string but maybe that's working and
it just doesn't recognize anything because of the problems described above.

Does anyone know what happened here, or how I might try and troubleshoot it?

I'd appreciate any input.

Thanks.

 

[Alec MacLean]

Hi Jonathan,

I would suspect the installation as a whole is not happy after the switch
from Express to Full. I doubt any post-install patching or fiddling will
really get it truly corrected.

While it would be a real pain, I would suggest your best bet would be to
rebuild your dev PC from scratch.

If you have the luxury of access to a spare PC (I know this may not be the
case), you could build a clone of your PC so you're not entirely without a
system while all the installs occur and you can more easily check what
settings you had before.

This does have the advatnage of completely cleaning your registry and
provides an opportunity to avoid installing anything you no longer require,
thus giving you a pristine enironment that operates smoothly (at least until
refilled with daily accumulated grunge).

I was forced to do this full rebuild approach due to a hard-disc failure,
ended up upgrading my disk from PATA to SATA and capacity from 80Gb to
250Gb, very cheaply. Speed differences on system boot and program compiling
were noticably improved using the SATA drive, so there were tangible
benefits there too.

Fortunately I already had a note of the majority of my installed programs.
For my config, this took about a day of disk shovelling. I then imaged my
system in its clean state to DVD using Ghost 2003 (fantastic program!) so
that in the event of any further issues I wouldn't have to sit and shovel
discs for so long. You can extract individual files from the Ghost image
set if required.

I keep my Visual Studio project files backed up on a server (I run a simple
batch file every day to xcopy them over), so restoring my projects wasn't
too painful - just had to remember to recreate the local IIS virtual
directories, which was easy enough. The source control wasn't quite so
simple, but it wasn't too bad either (I'm using an old-ish version of
SourceGear Vault).

It may be worth noting that I don't run a local copy of SQL Server. Instead
I have a DEV instance running on a real server on the network. This has the
benefit of not needing to load all the SQL processes and services, which all
consume resources of a (in my case) pretty heavily loaded PC.

Not a direct solution and perhaps something of a sledgehammer approach, but
it should certainly fix the problem!

Regards,

Alec

 

[Norman Yuan]

One potential, I guess, that causes your VS2008/ASP.NET membership
connection issue, based on your description and your previuos post would be:

You started your ASP.NET website with SQL Server Express, created membership
database. Then you changed to full SQL Server2005. So, it is possible, when
with SQL Server Express, the database is created on SQL Server Express
USERINSTANCE. After you moved to full SQL Server, the USERINSTANCE is not
supported any more. The other possibility is your connection string has
something like "ATTACHDBFile=...;", so each time you start VS or your
ASP.NET website, the origial blank database is attached (so there is no long
users crea ted previously)?

Sorry to say that, but we have been seeing too many ASP.NET developers jump
on ASP.NET membership SQL SERVER provider when creating website without
fully understanding SQL Server, which is very complicated server software
package.

I used MSDE/SQL Server Express or full SQL Server2000/2005 with VS2002 -
2008 on Win98/2000/XP/Vista, and never had installation issue and connection
issue.

It seems the SQL Server is working. There are two things to look into/study:
SQL Server security configuration and ASP.NET security configuration. When
you work with VS, your user account is used to connect to SQL Server (most
likely, you are a local admin, or run as local admin), so thing seems
working well. ASP, as a server side app, may use completely different user
account to run (even it is in your developing computer), depending on your
configuration. So, your app may work well when testing/debuging inside VS,
but may not work one called from web browser, for example, the ASP.NET app
cannot connect to SQL Server (in the same box or in the other box).

So, you really need to seperate the problem into small chunks:

1. Is the SQL Server working? does it allow remote access (if accessed from
other computer)
2. Which user (from the same box or other box/network domain) needs to
access the SQL Server.If your app is web app, which user account is used to
run the app?
3. Is appropriated SQL Server login is created that maps to accessing user
account (local or domain) when using Windows security with SQL Server? Or do
you have to use SQL Server security (specifying username/password in
ConnectionString) due to the need of the application?

If these 3 steps are done correctly, there is not reason to have connection
issue.

Sorry for not being able to offer more detailed solution, unless you have
detailed explanation on each step you did.

 

[Jonathan Wood]

Alec,

I would suspect the installation as a whole is not happy after the switch
from Express to Full. I doubt any post-install patching or fiddling will
really get it truly corrected.

While it would be a real pain, I would suggest your best bet would be to
rebuild your dev PC from scratch.

Reinstall Windows? I think I may just toss the system out the window before
I do that.

For starters, it would take days. In addition, the system is still very new.
What's to say I won't have the exact same problems when I install them this
time. And, beyond that, if MS can't make software that works when installed,
then my problems are bigger than simply installing things in the correct
order.

Not a direct solution and perhaps something of a sledgehammer approach,
but it should certainly fix the problem!

Currently, I'm not at all convinced of that.

But I appreciate your input.

 

[Jonathan Wood]

Norman,

One potential, I guess, that causes your VS2008/ASP.NET membership
connection issue, based on your description and your previuos post would
be:

You started your ASP.NET website with SQL Server Express, created
membership database. Then you changed to full SQL Server2005. So, it is
possible, when with SQL Server Express, the database is created on SQL
Server Express USERINSTANCE. After you moved to full SQL Server, the
USERINSTANCE is not supported any more. The other possibility is your
connection string has something like "ATTACHDBFile=...;", so each time you
start VS or your ASP.NET website, the origial blank database is attached
(so there is no long users crea ted previously)?

It's true that I used USERINSTANCE in my connection string under SQL Server
Express. And it's true that my connection string used AttachDbFilename.

I have already changed my connection string. However, when viewing the data
within the IDE, or accessing settings via the ASP.NET Configuration, it does
not use my connection string so I am focusing on the other issues first.

In addition, I can see the users are still in the database. But it seems to
think the application is now called "/<myappname>" whereas, before, it was
just "/". And I think that is definitely related to the problem.

NOTE: It is okay for me to lose membership data. It only contains test
users.

Sorry to say that, but we have been seeing too many ASP.NET developers
jump on ASP.NET membership SQL SERVER provider when creating website
without fully understanding SQL Server, which is very complicated server
software package.

Indeed. And SQL Server won't even allow me to do a simple import of a
comma-delimited file. Screwed if you do, ...

So, you really need to seperate the problem into small chunks:

1. Is the SQL Server working? does it allow remote access (if accessed
from other computer)

I can connect from SSMS. At no point will I be accessing the local database
from another computer and so I hope it does not allow that.

2. Which user (from the same box or other box/network domain) needs to
access the SQL Server.If your app is web app, which user account is used
to run the app?

My account, and I have been using Windows Authentication.

3. Is appropriated SQL Server login is created that maps to accessing user
account (local or domain) when using Windows security with SQL Server? Or
do you have to use SQL Server security (specifying username/password in
ConnectionString) due to the need of the application?

I think the fact that I'm using Windows Authentication addressed that.

Sorry for not being able to offer more detailed solution, unless you have
detailed explanation on each step you did.

I appreciate your input. Given my replies and what I know about this, my
guess is that you probably still don't have enough information and I may
need to spend another three hours on the phone to MS tech support in India
to resolve this.

I was very reluctant to do this and have spent 4 days already. AFAIC, it's
just a nightmare.

 

[Norman Yuan]

OK, it is good to hear that you can access the SQL Server from SSMS. So,
that indicate your SQL Server installation is OK, and at least your user
account is OK to access it. Now you can start from there. Try this things
one step at a time:

Use SSMS to create a new test database, create some tables. Then within
SSMS, try detach/attach the database from SQL Server, so that you get
comfortable with that operation. It is really an easy thing to do. This also
verify if your account when logging into SQL Server have enough priviledge
to do something or not.

After that, try everything on this link to set up SQL Server for ASP.NET
website:

http://msdn2.microsoft.com/en-us/library/x28wfk74.aspx

and other links provided in it.

Make sure you understand how to configure ASP.NET/IIS to have desired user
account to run your web app. As I stated previously, your app may run on
your develping box, or with VS debugging mode, since you are local admin,
but it is likely not run once you move your web app to production machine,
unless you configure IIS/ASP.NET app correctly (i.e. the running user
account has proper access to the database).