Page 1 of 2 12 LastLast
Results 1 to 15 of 23

Thread: MSSQL: Second user trying to connect generates: Cannot open user default database.

  1. #1
    Join Date
    Apr 2007
    Posts
    9

    MSSQL: Second user trying to connect generates: Cannot open user default database.

    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.

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Is sql2k5 running on same machine? Why use AttachDbFilename in connection string? Try specify db name in the string.

  3. #3
    Join Date
    Apr 2007
    Posts
    9
    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?

  4. #4
    Join Date
    Apr 2007
    Posts
    9
    hmmm, when I run service as currently logged user - everything is OK. Maybe it's a matter of security arrangements in DB?

  5. #5
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,922
    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.

  6. #6
    Join Date
    Apr 2007
    Posts
    9
    How do I set default database to 'attacheddbfile' database? There is no my database @ combobox next to 'default database' (why should it be?. 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...

  7. #7
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,922
    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

  8. #8
    Join Date
    Sep 2002
    Posts
    5,938
    Does user have permission in my_database?

  9. #9
    Join Date
    Apr 2007
    Posts
    9
    Quote Originally Posted by 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

    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.

  10. #10
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,922
    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

  11. #11
    Join Date
    Sep 2002
    Posts
    5,938
    May need drop db crawler from sql server first.

  12. #12
    Join Date
    Apr 2007
    Posts
    9
    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:/

  13. #13
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,922
    How does your connection string look like now?

  14. #14
    Join Date
    Sep 2002
    Posts
    5,938
    Where is the .mdf file? On sql server's c:\ drive?

  15. #15
    Join Date
    Apr 2007
    Posts
    9
    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).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •