locked
Cannot open user default database. Login failed. RRS feed

  • Question

  • I use Microsoft .NET Framework 3.5 and
    Microsoft ADO.NET to develop an application. It contains a Microsoft SQL
    Server 2005 server. My application retrieve records from a database. The application connects to database by using an instance of the SqlConnection class.

    When the application calls the Open method of the SqlConnection object, it displays the following:
    "Cannot open user default database. Login failed. Login failed for user 'User1'".


    Pls tell me what are the various reasons that made this error to come.
    Thakur
    Tuesday, January 26, 2010 6:32 AM

Answers

  • Whenever a login is created at the SQL server , one of the databases in the same SQL instance is set as its default database. For example , lets take the below example

    USE [master]
    GO
    CREATE LOGIN [Test] WITH PASSWORD=N'test', DEFAULT_DATABASE=[simple1], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
    GO

    -- the test login needs the simple1 db to be online when that login tries to login . This is the importance of default database
    for a login.

    Now as per your error message your def db is offline or suspect , so try to get the db online talking to your DBA or get the default db changed for
    this test login using the following script

    alter LOGIN [Test] with DEFAULT_DATABASE=master

    Now you will be able to login to SQL server and gain access.


    Thanks, Leks
    Tuesday, January 26, 2010 7:18 AM
    Answerer
  • First you need check whether you use the windows integrated security or SQL security in your connection string. If you set the user name and password in your connection string, you need to make sure:
    1 Server authentication of your SQL Server is "SQL Server and Windows Authentication"
       [In SSMS, select server -> right click -> Properties -> Select Security Tab]
    2 Your user has permission to the database in connection string
       [In SSMS, expand the Security node, check whether user has been added and has permissions]
    3 1433 port is allowed to through firewall on the SQL Server machine


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, January 26, 2010 10:01 AM
    Answerer

All replies

  • Whenever a login is created at the SQL server , one of the databases in the same SQL instance is set as its default database. For example , lets take the below example

    USE [master]
    GO
    CREATE LOGIN [Test] WITH PASSWORD=N'test', DEFAULT_DATABASE=[simple1], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
    GO

    -- the test login needs the simple1 db to be online when that login tries to login . This is the importance of default database
    for a login.

    Now as per your error message your def db is offline or suspect , so try to get the db online talking to your DBA or get the default db changed for
    this test login using the following script

    alter LOGIN [Test] with DEFAULT_DATABASE=master

    Now you will be able to login to SQL server and gain access.


    Thanks, Leks
    Tuesday, January 26, 2010 7:18 AM
    Answerer
  • First you need check whether you use the windows integrated security or SQL security in your connection string. If you set the user name and password in your connection string, you need to make sure:
    1 Server authentication of your SQL Server is "SQL Server and Windows Authentication"
       [In SSMS, select server -> right click -> Properties -> Select Security Tab]
    2 Your user has permission to the database in connection string
       [In SSMS, expand the Security node, check whether user has been added and has permissions]
    3 1433 port is allowed to through firewall on the SQL Server machine


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, January 26, 2010 10:01 AM
    Answerer