ASP.NET SQL not connecting

STS-Tech
STS-Tech used Ask the Experts™
on
I have a VB 2005 ASP site running in IIS 6.0 on a 2003 Server.  It is back-ended to a SQL Express 2005 database.  It was working fine until SQL Express was accidentally uninstalled.  I reinstalled SQL Express but could not get the page to open.  After a lot of troubleshooting and a previous post on EE I found a setting in IIS Web Service Extensions that allowed or prohibited .NET.  I thought all was well, but I found that only pages that didn't require the database would open.  Anything that did call SQL produced an error (don't even remember which one right now).  After much more research and troubleshooting I uninstalled IIS, the .NET SDK, SQL Express 2005, and the SQL Express Management Studio then reinstalled all exactly (as far as I know) as I did when I initially set it up.  I even re-copied the original ASPX and web.config files from my development machine (where it runs fine) back to the server.

As of now, by messing with my web.config file I can get a number of error messages:

1.
  <add name="PublicWorksDB" connectionString="Server=localhost\SQLEXPRESS;AttachDbFilename=C:\WEBDOCS\JOBMANAGER\PUBLICWORKSDB.MDF;Integrated Security=SSPI;User Instance=False"
   providerName="System.Data.SqlClient" />

Gives me:

CREATE DATABASE permission denied in database 'master'.
An attempt to attach an auto-named database for file C:\WEBDOCS\JOBMANAGER\PUBLICWORKSDB.MDF failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

2.
If I change User Instance to True I get:

Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.

3.
If I remove User Instance and Integrated Security and instead provide credentials I get:

Login failed for user 'administrator'. The user is not associated with a trusted SQL Server connection.

Even though I can access the Managment Studio with those credentials.

Through all of this I can open any ASPX pages that don't call the database so I know the issue is SQL- but I'm at a loss.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
assign your database user(myuser in my example) dbowner rights of your database(myDatabase here).
Then use connection string as:-

<add name="PublicWorksDB" connectionString="Server=localhost\SQLEXPRESS;Initial Catalog=MyDatabase; uid=myuser;pwd=mypassword;" providerName="System.Data.SqlClient" />
I ran into a similar issue. One of the app. teams couldn't connect to SQL from the app. although they could connect to SQL using SQL using Management Studio.

In my  case, the collation setting for the database was set to Latin1_General_Bin which is case sensitive. The name of the database was set to AppDatabase instead of Appdatabase. As a result, the database couldn't be recognized by the app. and failed connecting to. They couldn't change the connection string as it's used in many places. Once I renamed the database, all is well.

Author

Commented:
Buggy,
There is a domain user I created, JobManager, to log into IIS for remote users.  I added it as an account in the Mgt. Studio and made its default schema dbo_owner.  I then granted all permissions except "guest".  The result is:

Login failed for user 'JobManager'. The user is not associated with a trusted SQL Server connection.

Rocky,
I was very careful with case sensitivity when setting up the server so it matched my development machine as closely as possible.  I did notice that the database name in the Mgt. Studio was in all caps so I tried it both ways but with no luck.  

This is the only place the database will be hosted from so I can change to a setting that is not case sensitive to make sure though.  Where do I do this?  I see in the properties of my database that it's set to SQL_Latin1_General_CP1_CI_AS but I don't see where to change it.

Thanks to you both for the help!
STS-Tech

The collation setting for your database is already case-insensitive. You do not need to change it.
If you would like to know how to change the database collation, please refer to the link below

http://msdn.microsoft.com/en-us/library/ms175835(v=sql.90).aspx

Author

Commented:
Thanks, but if it's not going to correct the connection issue I'll just leave it at the default.

Author

Commented:
I found one difference- I originally built the website and database on a Windows 7 OS and then moved it to the server (2003).  That worked.  Since then I've removed 7 and installed XP on my development machine so I now have IIS 5, as opposed to the server's IIS 6  and whatever 7 ships with.  Could that make any difference?
Commented:
Work-around:
We set up another Windows XP machine to host the program and database.  It still did not work until I manually granted permissions to the local ASPNET account on the MDF and LDF files.  Once that was done however, it came online.

Despite this- it still would not run on Server 2003.  I know that 2003 uses the local "NETWORK SERVICE" account, but granting permissions for it both in SQL and directly on the files did not work.  

Not so big a deal now as the program is back up and running on its new home, but I'd still like to figure out what changed to break it.

Author

Commented:
Problem was never resolved- Work around (using another system) was the "solution".

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial