Cannot open database "xxxx" requested by the login

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Cannot open database "xxxx" requested by the login

Author

Topic

R
Constraint Violating Yak Guru

328 Posts

Posted - 2010-01-14 : 07:43:41

I know this has been asked about a hundred times already, but all the solutions I've checked out today have failed to work.

I have an ASP.NET 3.5 site with a SQL 2008 Express database in the \App_Data folder. When I try to run my ASP.NET pages, I get the following error:


 Cannot open database "HA" requested by the login. The login failed.

I installed SQL 2008 Express Management Studio and attached the database. I then created a new Login account and database user called 'new' to see if this would work, but then I get the error:


 Cannot open database "HA" requested by the login. The login failed.
 Login failed for user 'new'.

This problem has generally been caused by a bad connection string, but mine follows the guidelines (I think). Strangely, I can connect to the Management Studio using the 'new' account with the password I set for it, so I am stumped as to why ASP.NET can't cope.

Any help would be exremely appreciated as I am running out of time and need to get this working!

Out of interest, my connection string in web.config is:


<connectionStrings>
    <add name="haConnectionString1" connectionString="Server=.\SQLEXPRESS;Initial Catalog=HA;User ID=new;Password=aaaaaa" providerName="System.Data.SqlClient" />
</connectionStrings>

Kristen
Test

22859 Posts

Posted - 2010-01-14 : 08:14:04

Is your SQL Express set up to allow "SQL Logins", rather than just Windows logins?

Maybe add

Trusted_Connection=False;

to your connection string to explicitly indicate that, in case it gives you a more informative error message.

You could try taking the UserID/Password out and putting

Trusted_Connection=True;

instead

R
Constraint Violating Yak Guru

328 Posts

Posted - 2010-01-14 : 08:24:31

Hi Kristen

quote:
Originally posted by Kristen

Is your SQL Express set up to allow "SQL Logins", rather than just Windows logins?

Yes, under Server Properties I have enabled 'SQL Server and Windows Authentication Mode'.

quote:
Originally posted by Kristen
Maybe add
Trusted_Connection=False;
to your connection string to explicitly indicate that, in case it gives you a more informative error message.

Not luck sadly. It just throws the same error.

quote:
Originally posted by Kristen
You could try taking the UserID/Password out and putting
Trusted_Connection=True;
instead

Okay this time it throws a new error:


  Cannot open database "HA" requested by the login. The login failed.
  Login failed for user 'PC_NAME\ASPNET'.

Kristen
Test

22859 Posts

Posted - 2010-01-14 : 08:57:42

"Login failed for user 'PC_NAME\ASPNET'"

My guess would be that ASP.NET is silently adding a default username if it is absent from the Connection String.

"under Server Properties I have enabled 'SQL Server and Windows Authentication Mode'."

Given that I wouldn't bother messing around further with Trusted Connections.

However, you might like to try explicitly logging in to Management Studio using the UserID / Password in your Connection String. If that opens a Session/Query Window then you know the login credentials are good (you just might have made a typo in the password, or somesuch ...).

After that I'm out of ideas I'm afraid.

www.connectionstrings.com/ is my preferred resource for connection strings, but I couldn't see anything there that would be worth you trying.

R
Constraint Violating Yak Guru

328 Posts

Posted - 2010-01-14 : 10:28:25

quote:
Originally posted by Kristen

"Login failed for user 'PC_NAME\ASPNET'"
My guess would be that ASP.NET is silently adding a default username if it is absent from the Connection String.

Yeah well I'm fit to scream because I have absolutely no idea. The best thing is I've been crafting SQL databases using the proper SQL Server software for 5 years, but this little piece of misery really has me stumped.

quote:
Originally posted by Kristen

However, you might like to try explicitly logging in to Management Studio using the UserID / Password in your Connection String. If that opens a Session/Query Window then you know the login credentials are good (you just might have made a typo in the password, or somesuch ...).

Yes that's the puzzling thing. The details used in the connection string do allow me to connect to Management Studio! Its just when ASP.NET tries to use them, it goes bang!

quote:
Originally posted by Kristen

www.connectionstrings.com/ is my preferred resource for connection strings, but I couldn't see anything there that would be worth you trying.

Yeah I've been there this morning and tried every possible connection string. This problem must be being caused by something so simple...!

Kristen
Test

22859 Posts

Posted - 2010-01-14 : 10:43:56

Might it be a problem getting ANY sort of connection? A connection failure of any sort tends to come back "Login failed".

If it comes back instantly its a userid/password. If it takes a while its a DNS/connection issue. Although I expect, on a local machine, both types of failure will be "instant".

This is all local on your machine, given your "Server=.\SQLEXPRESS" setting, right?.

Have you got any other know-to-work-well noddy ASP.NET toolkit stuff that you could try connecting?

Also try connecting as SA using the SA password (which will get rid of spurious errors like it trying to connect to a default database that "UserID=new" has no rights to - which may behave differently when logging in direct from Management Studio)

Fire up ~~Performance Monitor~~ Profiler and see if you are getting any login attempt at SQL end?

if that isn't easy / clear you could try turning on logging for both Failed and Successful logins (same options screen as allowing SQL + Windows logins I think) and then check if you get anything useful in the SQL Error Log. Don;t forget to turn them (well, Successful Logins at least) back off again afterwards!

Edit Correct dyslexic-brain-fade

R
Constraint Violating Yak Guru

328 Posts

Posted - 2010-01-14 : 10:49:19

Well I really don't believe it. This mystery just gets stranger and stranger. I have (kind of) solved the problem by doing this:

A SOLUTION TO THE ERROR: 'Cannot open database 'xxxx' requested by the login. The login failed'

1) Detach the database from Management Studio, and delete any previously created login accounts for this database

2) Move the MDF/LDF files out of the web directory they are in (e.g. from APP_DATA to the parent directory of the web site). The whole point here is to get the database out of app_data! I put them 'above' the web

2) Then re-attach the database into Management Studio

3) Create a new server Login account and database user which maps to that account

4) Change the connection string to this:


  <connectionStrings>
    <add name="myConnString1" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=myDataBaseNameWithNoExtension;User Id=myUserName;Password=myPassword;" providerName="System.Data.SqlClient" />
  </connectionStrings>

It staggers me why it should not work in Visual Studio 2010 from the APP_DATA directory, when the whole point of VS is to perform all operations in one IDE. Microsoft should be ashamed at the number of people struggling with very similar problems.

R
Constraint Violating Yak Guru

328 Posts

Posted - 2010-01-14 : 10:52:01

I tried logging in as SA within Management Studio and that failed too! Everything is local at present, and although its working now by avoiding the app_data directory, it does concern me what might happen when I try to put this thing into a live environment...!

Again like I said though, it is very bad practice on Microsoft's behalf to allow these painful problems to crop up so often. No wonder everyone is heading for WordPress...!

Kristen
Test

22859 Posts

Posted - 2010-01-14 : 10:57:55

No idea how you can connect from Management Studio, and then not from your app (regardless of where the DB files are placed).

Not being able to open the DB at all I could understand - file permissions being unsuitable in the APP folder.

Is the DB set to AUTOCLOSE = TRUE? That could be causing some sort of timeout I suppose (and is a ghastly setting anyway!)

But, yeah!, I've read through the steps you made a couple of times and nothing occurs to me why that would make any difference.

But if you've got it fixed you can start putting some billable time on your timesheet!!

R
Constraint Violating Yak Guru

328 Posts

Posted - 2010-01-14 : 11:30:11

AUTOCLOSE is SET to TRUE, yes. Not sure what that does as I've never read into it.

quote:

But if you've got it fixed you can start putting some billable time on your timesheet!!

LOL the fun part...! Thank for your help btw. Much appreciated.

Kristen
Test

22859 Posts

Posted - 2010-01-14 : 11:37:49

"AUTOCLOSE is SET to TRUE, yes. Not sure what that does as I've never read into it."

Now you mention that I believe it is the default for Express.

When the last connection closes then SQL server closes the database - which is much the same as putting it OFFLINE.

When you next connect it has to open it back up, just the same as when the server reboots.

If you Connect/Disconnect a lot (as a web site would) then the "cost" of Close/Re-open is huge, but maybe in your application you hold the connection open for as long as the application is open.

Anyway, the cost of "re-open" MIGHT be causing a timeout. (I doubt it on a local machine with one database active).

Either way, I doubt you actually want it set like that, and that setting will remain if you don't change it and Backup/Restore, or Detach/Attach, to move it to a live server

Suggest you turn AutoClose OFF

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources