locked
Debug SQL Server Login RRS feed

  • Question

  • Hi,

    I have strange login behavior on an SQL Server 2005. I can connect and manage the database with the Sql Management Studio. But when starting our application(using Entity Framework with Sql Client), I get "Login failed for User ...." error message.

    I really don't understand, why I can access the database with the management studio but not with the application.

    Connection String:
    <add name="EntitiesConnection" connectionString="metadata=res://*/OURDATABASE.csdl|res://*/OURDATABASE.ssdl|res://*/OURDATABASE.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=OURSERVER;Initial Catalog=OURDATABASE;Integrated Security=True;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient"/>
    
    
    

    Is there a possibility to debug this behaviour?

    From where could the "Login failed" exception be risen?

    Any help is appreciated.

    Best regards
    Yannik 

    Monday, January 3, 2011 11:49 AM

Answers

  • Who are you being when you login to the database throuh the application?  Are you running as (for example) DOMAIN\YSTEIGER or as some ApplicationLogin?

    Since you login successfully through SSMS, but not through the application, the first thing I would check is that the connection string for the application has Data Source=OURSERVER;Initial Catalog=OURDATABASE correct  (It is easy enough to miss something in this when making changes.)

    Since your connection string mentions Integrated Security, so I would expect that the DOMAIN\YSTEIGER equivalent to be in use, not some other login.  Your default database can be selected from sys.server_principals as:

      select name, default_database_name from sys.server_principals
      where name = 'yourloginname'

    If you only get server rights through  Windows group logins, then you will not find 'yourloginname' in server_principals, but the name(s) of the groups of which you are a part will be there.  Note that each group could have a different default database, which means it would not be clearly defined which default database will be used when you login.  To see the possibilities:

      select name, default_database_name from sys.server_principals
      where is_member(name) = 1
      order by default_database_name, name

    However, your default database is probably OK since that is what you probably use when you login through SSMS.  (But it is possible to set up SSMS with a coded default database that is different from the server definition.)

    So, look at your connection string carefully, then at the default database(s) you have defined. 

    RLF

    • Proposed as answer by Yannik Steiger Monday, January 3, 2011 4:59 PM
    • Marked as answer by WeiLin Qiao Tuesday, January 11, 2011 10:47 AM
    Monday, January 3, 2011 4:21 PM

All replies

  • Yannik 

    Please post exact message you are getting (state....severity...)


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, January 3, 2011 12:11 PM
  • The error message is:

    Cannot open database "OURDATABASE" requested by the login. The login failed.
    Login failed for user 'MYUSER'.
        Source=.Net SqlClient Data Provider
        ErrorCode=-2146232060
        Class=11
        LineNumber=65536
        Number=4060
        Procedure=""
        Server=OURSERVER
        State=1
        StackTrace:
    Monday, January 3, 2011 1:01 PM
  • I meant  do  you have any entries in ERROR.LOG

    http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/85ef8cbc-4235-499a-bd64-bf424336d1a5

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, January 3, 2011 1:17 PM
  • I meant  do  you have any entries in ERROR.LOG

    http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/85ef8cbc-4235-499a-bd64-bf424336d1a5

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, January 3, 2011 1:17 PM
  • The ERRORLOG says:

    2011-01-03 13:58:03.64 Logon       Error: 18456, Severity: 14, State: 16.
    2011-01-03 13:58:03.64 Logon       Login failed for user 'MAIN\BBV_STY'. [CLIENT: 159.232.27.70]

    Monday, January 3, 2011 2:26 PM
  • http://sql-articles.com/articles/troubleshooting/troubleshooting-login-failed-error-18456

    Apparently the login does not have rights to use the startup database, which is probably the login's default database. 

    This could also have been caused by dropping, taking offline, or renaming that database.

    RLF

    Monday, January 3, 2011 2:34 PM
  • Hi Russel,

    thanks for your hint. Where can I check wich database is setup as startup / default database for a domain user?

    Best regards

    Monday, January 3, 2011 3:20 PM
  • Who are you being when you login to the database throuh the application?  Are you running as (for example) DOMAIN\YSTEIGER or as some ApplicationLogin?

    Since you login successfully through SSMS, but not through the application, the first thing I would check is that the connection string for the application has Data Source=OURSERVER;Initial Catalog=OURDATABASE correct  (It is easy enough to miss something in this when making changes.)

    Since your connection string mentions Integrated Security, so I would expect that the DOMAIN\YSTEIGER equivalent to be in use, not some other login.  Your default database can be selected from sys.server_principals as:

      select name, default_database_name from sys.server_principals
      where name = 'yourloginname'

    If you only get server rights through  Windows group logins, then you will not find 'yourloginname' in server_principals, but the name(s) of the groups of which you are a part will be there.  Note that each group could have a different default database, which means it would not be clearly defined which default database will be used when you login.  To see the possibilities:

      select name, default_database_name from sys.server_principals
      where is_member(name) = 1
      order by default_database_name, name

    However, your default database is probably OK since that is what you probably use when you login through SSMS.  (But it is possible to set up SSMS with a coded default database that is different from the server definition.)

    So, look at your connection string carefully, then at the default database(s) you have defined. 

    RLF

    • Proposed as answer by Yannik Steiger Monday, January 3, 2011 4:59 PM
    • Marked as answer by WeiLin Qiao Tuesday, January 11, 2011 10:47 AM
    Monday, January 3, 2011 4:21 PM
  • Thanks a lot for your help. I have seen that my user does not have a default database defined. This was the problem.

    Best regards

    Monday, January 3, 2011 5:04 PM