none
SQL authentication problem, error 18456

    Question

  • I've installed SQL express. I've added some user to AD with all permissions. In the cliconfg.exe I've enabled TCP and PIPEs. Win firewall is off.

    SQL is in mixed mode auth. Windows authentication is successful, but I can not login under the user I've added to AD. Error message appears:

    Cannot connect to TUG\SQLEXPRESS.

    ===================================

    Login failed for user 'TUR\sql'. (.Net SqlClient Data Provider)

    ------------------------------
    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

    ------------------------------
    Server Name: TUG\SQLEXPRESS
    Error Number: 18456
    Severity: 14
    State: 1
    Line Number: 65536

    -----

    And, sqlcmd with no arguments shows:

    C:\Users\>sqlcmd

    HResult 0x2, Level 16, State 1
    Named Pipes Provider: Could not open a connection to SQL Server [2].
    Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or in
    stance-specific error has occurred while establishing a connection to SQL Server
    . Server is not found or not accessible. Check if instance name is correct and i
    f SQL Server is configured to allow remote connections. For more information see
     SQL Server Books Online..
    Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.

     

    • Edited by MIET3000 Wednesday, December 29, 2010 10:29 AM err in theme
    Wednesday, December 29, 2010 10:28 AM

All replies

  • http://sqlserver-qa.net/blogs/tools/archive/2007/05/18/msg-18456-level-14-state-1-server-servername-line-1-login-failed-for-user-name.aspx

    http://support.microsoft.com/kb/925744


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, December 29, 2010 10:33 AM
    Answerer
  • Are you sure you are using the right server\instance name ? can you cross check by using the windows authentication...Also the actual state and severity will be shown in the errorlogs ...State 1 is the default state shown to hide the actual state from the users who do nto have authority to see it.


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Wednesday, December 29, 2010 11:18 AM
  • There is no audit error. "osql -L" does not show our instance. Service "SQL Server(SQLExpress)" is on.

    Windows authentication is passed. SQL Server Authentication still fails.

    User is in groups Administrators, Domain admins, Enterprise admin.

    Wednesday, December 29, 2010 11:57 AM
  • Hi

    Have you look into the links  I posted before?


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, December 29, 2010 11:59 AM
    Answerer
  • Yes, I've looked, but I do not have any audit failures. I'm using sql express 2008 r2 btw.
    Wednesday, December 29, 2010 12:11 PM
  • 1) In the configuration manager can you enable shared memory and see if its working

    2) what does it shows in the SERVICE NAME when you double click the service in the services console .

    3) can you try to connect using this string : TCP:server\instance,port name (e.g. : TCP:abhay\bug,1110)

    4) Post the actual 18456 error from the error log ..


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Wednesday, December 29, 2010 2:08 PM
  • Hi,

    When you install SQL Server Express it takes a name in the form [SERVERNAME\SQLEXPRESS], actually a named instance that will use a randomly generated (TCP Dinamic) port, by default the client applications including SQL Server Management Studio try to connect to a SQL Server through port 1433 which is not the port of you instance is using, to make it transparent to client tools the SQL Server Browser Service must be started.

    First open SQL Server Conrfiguration Manager: Start -> All Programs -> Microsoft SQL Server R2-> Configuration Tools -> SQL Server Configuration Manager

    To verify SQL Server Browser Service is started: \ SQL Server Configuration Manager "Machine Name" \ SQL Server Services \ SQL Server Browser

    You will find addtional information here: http://blogs.msdn.com/b/sqlexpress/archive/2005/05/05/415084.aspx

    Thanks,

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    Alfredo Arias MCITP, MCTS, MCDBA, MCSD, MCAD, MCSA, Server+, Network+, A+

    Wednesday, March 07, 2012 5:58 PM