none
Cannot open database “” requested by the login. The login failed. Login failed for user 'sa'

    Question

  • Hello I am trying to use SQL Authentication to connect to a database . I am having extreme difficulties and my searches so far couldn't help me solve the problem.

    I Enabled the SQL Authentication through SQL SERVER MANAGEMENT STUDIO and enabled sa and set a simple password for it.(under .\sqlexpress - i mean my server-name is ).
     Now I can Successfully log in to the database by sa and its password from SQL SERVER MANAGEMENT STUDIO but when i try to do so from my application it fails with this error message :

    //Cannot open database "dbNegin" requested by the login. The login failed. Login failed for user 'sa'.

    I need to say that i changed the database owner to sa as well, yet i get this error!!

    How can i solve this now? :(

    By the way this is the connection string used in my application to connect to the database :

        string sqlconstring = @"Data Source=.\sqlexpress;User ID=sa;Password=123456;Initial Catalog=dbNegin";

    My data base is attached by the way (its located in the address below )

       " D:\TestDb\Debug\dbNegin.mdf"

    It is just driving me insane , I have tested all i could find but nothing seems to work :(

    Please somebody help me



    • Edited by Coderx7 Monday, February 25, 2013 6:47 PM
    Monday, February 25, 2013 6:32 PM

Answers

All replies

  • Can you please check SQL error log to see if that give any hints?

    Thanks, Sohail ~Please mark answered if your question is completely answered; vote it as helpful to help others find a solution quicker. This posting is provided AS IS with no warranties.

    Monday, February 25, 2013 7:07 PM
  • Can you please check SQL error log to see if that give any hints?

    Thanks, Sohail ~Please mark answered if your question is completely answered; vote it as helpful to help others find a solution quicker. This posting is provided AS IS with no warranties.

    Hello , Where can i find it?
    Monday, February 25, 2013 7:11 PM
  • Type xp_readerrorlog from SSMS and see if that helps.


    Thanks, Sohail ~Please mark answered if your question is completely answered; vote it as helpful to help others find a solution quicker. This posting is provided AS IS with no warranties.

    Monday, February 25, 2013 7:17 PM
  • Type xp_readerrorlog from SSMS and see if that helps.


    Thanks, Sohail ~Please mark answered if your question is completely answered; vote it as helpful to help others find a solution quicker. This posting is provided AS IS with no warranties.

    This is The whole error message :

    2013-02-25 21:51:11.40 Server      Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)
        Mar 29 2009 10:11:52
        Copyright (c) 1988-2008 Microsoft Corporation
        Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

    2013-02-25 21:51:11.40 Server      (c) 2005 Microsoft Corporation.
    2013-02-25 21:51:11.40 Server      All rights reserved.
    2013-02-25 21:51:11.40 Server      Server process ID is 6748.
    2013-02-25 21:51:11.41 Server      System Manufacturer: 'Acer', System Model: 'Aspire 5742G'.
    2013-02-25 21:51:11.41 Server      Authentication mode is MIXED.
    2013-02-25 21:51:11.41 Server      Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Log\ERRORLOG'.
    2013-02-25 21:51:11.41 Server      This instance of SQL Server last reported using a process ID of 6888 at 2/25/2013 9:51:08 PM (local) 2/25/2013 6:21:08 PM (UTC). This is an informational message only; no user action is required.
    2013-02-25 21:51:11.41 Server      Registry startup parameters:
         -d C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\master.mdf
         -e C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Log\ERRORLOG
         -l C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\mastlog.ldf
    2013-02-25 21:51:11.43 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
    2013-02-25 21:51:11.43 Server      Detected 4 CPUs. This is an informational message; no user action is required.
    2013-02-25 21:51:11.45 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
    2013-02-25 21:51:11.47 Server      Node configuration: node 0: CPU mask: 0x000000000000000f Active CPU mask: 0x000000000000000f. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
    2013-02-25 21:51:11.49 spid7s      Starting up database 'master'.
    2013-02-25 21:51:11.57 spid7s      Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
    2013-02-25 21:51:11.70 spid7s      FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'SQLEXPRESS'.
    2013-02-25 21:51:11.73 spid7s      SQL Trace ID 1 was started by login "sa".
    2013-02-25 21:51:11.73 spid7s      Starting up database 'mssqlsystemresource'.
    2013-02-25 21:51:11.75 spid7s      The resource database build version is 10.00.2531. This is an informational message only. No user action is required.
    2013-02-25 21:51:11.94 spid7s      Server name is 'FRIEND-PC\SQLEXPRESS'. This is an informational message only. No user action is required.
    2013-02-25 21:51:11.94 spid10s     Starting up database 'model'.
    2013-02-25 21:51:11.94 spid7s      Informational: No full-text supported languages found.
    2013-02-25 21:51:11.94 spid7s      Starting up database 'msdb'.
    2013-02-25 21:51:12.08 Server      A self-generated certificate was successfully loaded for encryption.
    2013-02-25 21:51:12.08 Server      Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\SQLEXPRESS ].
    2013-02-25 21:51:12.08 Server      Server local connection provider is ready to accept connection on [ \\.\pipe\MSSQL$SQLEXPRESS\sql\query ].
    2013-02-25 21:51:12.08 Server      Dedicated administrator connection support was not started because it is disabled on this edition of SQL Server. If you want to use a dedicated administrator connection, restart SQL Server using the trace flag 7806. This is an informational message only. No user action is required.
    2013-02-25 21:51:12.09 Server      The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
    2013-02-25 21:51:12.09 Server      SQL Server is now ready for client connections. This is an informational message; no user action is required.
    2013-02-25 21:51:12.22 spid10s     Clearing tempdb database.
    2013-02-25 21:51:12.66 spid10s     Starting up database 'tempdb'.
    2013-02-25 21:51:12.75 spid13s     The Service Broker protocol transport is disabled or not configured.
    2013-02-25 21:51:12.75 spid13s     The Database Mirroring protocol transport is disabled or not configured.
    2013-02-25 21:51:12.76 spid13s     Service Broker manager has started.
    2013-02-25 21:51:12.76 spid7s      Recovery is complete. This is an informational message only. No user action is required.
    2013-02-25 21:51:18.59 spid51      Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required.
    2013-02-25 21:51:18.59 spid51      Using 'xpstar.dll' version '2007.100.1600' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
    2013-02-25 21:51:20.80 spid51      Starting up database 'C:\USERS\FRIEND\DESKTOP\RELEASE\DBNEGIN.MDF'.
    2013-02-25 21:51:21.33 spid51      Starting up database 'mytestdb'.
    2013-02-25 21:51:21.60 spid51      Starting up database 'C:\USERS\FRIEND\DESKTOP\RELEASE\DBNEGIN.MDF'.
    2013-02-25 21:51:35.60 Logon       Error: 18456, Severity: 14, State: 38.
    2013-02-25 21:51:35.60 Logon       Login failed for user 'sa'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]
    2013-02-25 22:11:00.79 Server      Server resumed execution after being idle 246 seconds: user activity awakened the server. This is an informational message only. No user action is required.
    2013-02-25 22:11:00.97 spid51      Attempting to load library 'xplog70.dll' into memory. This is an informational message only. No user action is required.
    2013-02-25 22:11:01.04 spid51      Using 'xplog70.dll' version '2007.100.1600' to execute extended stored procedure 'xp_msver'. This is an informational message only; no user action is required.
    2013-02-25 22:57:29.06 spid53      Starting up database 'C:\USERS\FRIEND\DESKTOP\RELEASE\DBNEGIN.MDF'.
    2013-02-25 22:58:40.51 Logon       Error: 18456, Severity: 14, State: 38.
    2013-02-25 22:58:40.51 Logon       Login failed for user 'sa'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]
    2013-02-25 23:07:09.86 Logon       Error: 18456, Severity: 14, State: 38.
    2013-02-25 23:07:09.86 Logon       Login failed for user 'sa'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]
    2013-02-25 23:07:16.37 Logon       Error: 18456, Severity: 14, State: 38.
    2013-02-25 23:07:16.37 Logon       Login failed for user 'sa'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]


    • Edited by Coderx7 Monday, February 25, 2013 7:39 PM latest log
    Monday, February 25, 2013 7:32 PM
  • Refer to the link below for the correct connection string to use for application.  If you want to attach a database when you connect, you must tell the engine to do so.  Otherwise, it will assume that the specified database name is already defined within the server and will attempt to open it.

    connection strings

    A slightly longer explanation (and perhaps a more important point)

    Is user instance deprecated

    Monday, February 25, 2013 7:35 PM
  • As the error message says: Login failed for user 'sa'. Reason: Failed to open the explicitly specified database. can you create a new login and give it sysadmin access and give a try? There are two possibilities, there might issue with SID of SA login or there may be problem with DB properties. 

    Thanks, Sohail ~Please mark answered if your question is completely answered; vote it as helpful to help others find a solution quicker. This posting is provided AS IS with no warranties.

    Monday, February 25, 2013 7:52 PM
  • Refer to the link below for the correct connection string to use for application.  If you want to attach a database when you connect, you must tell the engine to do so.  Otherwise, it will assume that the specified database name is already defined within the server and will attempt to open it.

    connection strings

    A slightly longer explanation (and perhaps a more important point)

    Is user instance deprecated

    My connection string is fine since its working on my system, but it doesnt work on my brothers ( i am trying to test my app on different systems as if it is the customers machine and get everything going ).
    Monday, February 25, 2013 8:09 PM
  • 1) Check the rights for sa user, sa user must be having sysadmin server roles. SA is default sysadmin user.

    2) Check DB you are trying to use is up & runing fine or not.

    3) I do not there is a issue of SA user SID - 0x01 , as you can not change the SID of sa user. SA user SID remain same in all sql server instance & version.


    Regards,
    Rohit Garg
    (My Blog)
    This posting is provided with no warranties and confers no rights.
    Please remember to click Mark as Answerand Vote as Helpfulon posts that help you. This can be beneficial to other community members reading the thread.


    Monday, February 25, 2013 8:36 PM
  • 1) Check the rights for sa user, sa user must be having sysadmin server roles. SA is default sysadmin user.

    2) Check DB you are trying to use is up & runing fine or not.

    3) I do not there is a issue of SA user SID - 0x01 , as you can not change the SID of sa user. SA user SID remain same in all sql server instance & version.


    Regards,
    Rohit Garg
    (My Blog)
    This posting is provided with no warranties and confers no rights.
    Please remember to click Mark as Answerand Vote as Helpfulon posts that help you. This can be beneficial to other community members reading the thread.


    Did you mean sysadmin in this section to be removed?

    i tried unnchecking it but it failed !:

    How can i make sure the database connection is ok?

    If i go to the database propertise here is the result:

    (connection server name is different from the database server name!! why?

    Tuesday, February 26, 2013 2:44 AM
  • Thanks Everyone.

    The following Topic solved my problem . here it is :

    http://www.sqlservercentral.com/Forums/Topic1423758-391-1.aspx?Update=1

    • Marked as answer by Coderx7 Tuesday, February 26, 2013 7:42 AM
    Tuesday, February 26, 2013 7:42 AM