# Miscellaneous > Ask an Expert >  MSSQL: Second user trying to connect generates: Cannot open user default database.

## codo

Hi, I'm new to MSSQL Express 2005.
I have a website (ASP.NET 2.0) accessing DB, in the mean time Windows Service tries to update some data in the same DB (Service runs as NT AUTHORITY\LOCAL SYSTEM). The second connection is rejected: "Cannot open user default database. Login failed.Login failed for user ....".
Problem occurs only when both: service and website are running at the same time. So service and website are running without problems when they are connecting DB exclusively.
My connection string is:
"Data Source=.\SQLEXPRESS;AttachDbFilename="|DataDirecto  ry|\spider-lab.mdf";Integrated Security=True;User Instance=True"

I would be grateful if you can help.

----------


## rmiao

Is sql2k5 running on same machine? Why use AttachDbFilename in connection string? Try specify db name in the string.

----------


## codo

Yes, everything runs locally. I use AttachDbFilename because I want quick deployment of the programme (it's part of thesis). 

How to specify database name in connection string while using AttachDBfilename?

----------


## codo

hmmm, when I run service as currently logged user - everything is OK. Maybe it's a matter of security arrangements in DB?

----------


## skhanal

Check what is the default database for the service account you are using. From the error message, the default database is set for some other database not the application database it should connect to.

----------


## codo

How do I set default database to 'attacheddbfile' database? There is no my database @ combobox next to 'default database' (why should it be? :Wink: . I added 'initial catalog=my_database' to connection string instead. Now the the second user gets:
"Unable to establish connection with the database: cannot open connection Cannot open database "my_database" requested by the login. The login failed." 
The first user can connect. This is so mental...

----------


## skhanal

You can run this in query analyzer by changing user id and db name from victoria and pubs to your user and db

EXEC sp_defaultdb 'Victoria', 'pubs'

or

you can use enterprise manager to change it, in login properties

----------


## rmiao

Does user have permission in my_database?

----------


## codo

> You can run this in query analyzer by changing user id and db name from victoria and pubs to your user and db
> 
> EXEC sp_defaultdb 'Victoria', 'pubs'
> 
> or
> 
> you can use enterprise manager to change it, in login properties



EXEC sp_defaultdb 'NT AUTHORITY\SYSTEM', 'crawler'
Done. New connection string is :
.\SQLEXPRESS;AttachDbFilename="C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\spider-lab.mdf";Initial Catalog=crawler;Integrated Security=True;Connect Timeout=30;User Instance=True
->
Service gets:
Could not attach file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\spider-lab.mdf' as database 'crawler'.

When I remove 'Initial Catalog=crawler', i get:
An attempt to attach an auto-named database for file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\spider-lab.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

----------


## skhanal

Does spider-lab.mdf file belong to crawler database?

SQL Server Express Edition allows only a single connection to an .mdf file when you connect with a connection string that has User Instance set to true.

See this article which provides examples

http://msdn2.microsoft.com/en-us/lib...57(VS.80).aspx

----------


## rmiao

May need drop db crawler from sql server first.

----------


## codo

Thank you for your answers.
I removed 'user instance=true' from connection string. Now I get:

An attempt to attach an auto-named database for file C:\Documents and Settings\Adam\My Documents\Visual Studio 2005\Projects\spider-lab-2\DB\spider-lab.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.]

Now I can't establish any connection at all.

I used Server Management Studio log into .\SQLEXPRESS and my DB is not attached to databases. Available dbs are: master, model, msdb, tempdb.

Datafiles are not being used (I can rename them). The error persists:/

----------


## skhanal

How does your connection string look like now?

----------


## rmiao

Where is the .mdf file? On sql server's c:\ drive?

----------


## codo

Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Documents and Settings\Adam\My Documents\Visual Studio 2005\Projects\spider-lab-2\DB\spider-lab.mdf;Integrated Security=True;User Instance=False


No, it's not in SQL data directory. In production environment it might be even on different partition (copied by windows installer).

----------


## rmiao

Different partition is fine as long as on sql server's local disk. My question for you is that file spider-lab.mdf is in C:\Documents and Settings\Adam\My Documents\Visual Studio 2005\Projects\spider-lab-2\DB on sql server or not.

----------


## codo

No, SQL server resides here: C:\Program Files\Microsoft SQL Server.

Is it necessary for attached file to be located at server root?

----------


## rmiao

Not necessary in C:\Program Files\Microsoft SQL Server, but has to be on sql server's local disk. You still didn't tell us which machine your .mdf file is on.

----------


## codo

Mdf is on my local disk, as is SQL server.

----------


## skhanal

SQLExpress creates a directory per user in "c:\Documents and Settings\[user name]\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS" to store some information. Delete this directory and try again

----------


## rmiao

So you want to connect to sql instance on your pc? Possible to move the mdf file to other folder with shorter path?

----------


## developer85

Hi all
i have the same Error
i'm working on solution conains  Wendows Services and Wendows Application
when i run the both Applications the Result is the same Error
"Cannot open user default database. "

Regards

----------


## rmiao

What's default db for the user? Is that db online? Does the user have permission in that db?

----------

