locked
Unable to login by SQL Server authentication RRS feed

  • Question

  • Hi,

    We're on a local server, and can successfully login with Windows authentication without any issue.

    But for all SQL Server authentication account, we can't login.

    Things we done for the troubleshooting:

    1. Checked and comfirmed it's mixed mode and restarted the SQL Server.

    2. All protocol (Shared Memory, Named Pipes, TCP/IP) enabled and restarted the SQL Server

    3. created new SQL Server account test, can't login

    4. reset the SQL Server accounts (sa, test), and tried to login with manually key in the password

    5. reset the SQL Server account (sa, test) and login by paste the prepared password

    the error message in errorlog is just like normal wrong password failed login:

    Error: 18456, Severity: 14, State: 8.
    Login failed for user 'test'. Reason: Password did not match that for the login provided. [CLIENT: <local machine>]

    Error: 18456, Severity: 14, State: 8.
    Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: <local machine>]

    and I googled a query to find out the errorcode of the failed login

    SELECT dateadd (ms, (a.[Record Time] - sys.ms_ticks), GETDATE()) as [Notification_Time], a.* FROM
    (SELECT
    x.value('(//Record/@id)[1]', 'bigint') AS [Record_ID],
    x.value('(//Record/Error/ErrorCode)[1]', 'varchar(30)') AS [ErrorCode],
    x.value('(//Record/Error/APIName)[1]', 'varchar(255)') AS [APIName],
    x.value('(//Record/Error/CallingAPIName)[1]', 'varchar(255)') AS [CallingAPIName],
    x.value('(//Record/Error/SPID)[1]', 'int') AS [SPID],
    x.value('(//Record/@time)[1]', 'bigint') AS [Record Time]
    FROM (SELECT CAST (record as xml) FROM sys.dm_os_ring_buffers
    WHERE ring_buffer_type = 'RING_BUFFER_SECURITY_ERROR') AS R(x)) a
    CROSS JOIN sys.dm_os_sys_info sys
    ORDER BY a.[Record_ID] DESC

    result:

    ErrorCode APIName CallingAPIName SPID Record Time
    0x139F ImpersonateSecurityContext NLShimImpersonate 61 1140726492
    0x534 LookupAccountSid LookupAccountSidInternal 61 1140190229

    it's really weird...

    and the servers are in our DMZ zone, is there any possibility that if any security policy can overwrite the authentication mode in SQL Server?

    Thanks,

    Yuyu

    Wednesday, October 26, 2016 6:22 AM

Answers

  • Hi Yuyu,

    Have you tried using connection string in SSMS connect to server windows to override the GUI input? Enter whatever username you like in Login text box and put the connection string below by clicking on ‘Additional connection paremeters’ tab.
    Server=myServerAddress;Database=myDataBase;User Id=myUsername;
    Password=myPassword;



    If it works, it could mean that your SSMS installation is somehow corrupted. I would suggest you re-install it see if the issue goes away.

    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support<br/> Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact <a href="mailto:MSDNFSF@microsoft.com">MSDNFSF@microsoft.com</a>.

    • Marked as answer by yuyu_bali Wednesday, October 26, 2016 8:19 AM
    Wednesday, October 26, 2016 7:47 AM

All replies

  • and

    osql works

    D:\MSSQL$MCP_UAT_01\120\Tools\Binn>osql -S ourservername -U test
    Password:
    1> select @@version
    2> go

     -------------------------------------------------------------------------------

            ------------------------------------------------------------------------

            ------------------------------------------------------------------------

            ------------------------------------------------------------------------

            -----
     Microsoft SQL Server 2014 (SP1-CU3) (KB3094221) - 12.0.4427.24 (X64)
            Oct 10 2
            015 17:18:26
            Copyright (c) Microsoft Corporation
            Standard Edition (64
            -bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

    (1 row affected)
    1>

    but using SSMS, we will fail to login.

    Wednesday, October 26, 2016 6:30 AM
  • These error messages would mostly appear when there is a service trying to connect to SQL Server. Please have a look on here: https://support.microsoft.com/en-us/kb/811889
    Wednesday, October 26, 2016 6:41 AM
  • Hi,

    you try 

    https://blogs.msdn.microsoft.com/sqlsakthi/2011/02/06/troubleshoot-connectivitylogin-failures-18456-state-x-with-sql-server/


    Please click Mark As Answer if my post helped.

    Wednesday, October 26, 2016 7:30 AM
  • Hi Yuyu,

    Have you tried using connection string in SSMS connect to server windows to override the GUI input? Enter whatever username you like in Login text box and put the connection string below by clicking on ‘Additional connection paremeters’ tab.
    Server=myServerAddress;Database=myDataBase;User Id=myUsername;
    Password=myPassword;



    If it works, it could mean that your SSMS installation is somehow corrupted. I would suggest you re-install it see if the issue goes away.

    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support<br/> Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact <a href="mailto:MSDNFSF@microsoft.com">MSDNFSF@microsoft.com</a>.

    • Marked as answer by yuyu_bali Wednesday, October 26, 2016 8:19 AM
    Wednesday, October 26, 2016 7:47 AM
  • Hi Lin,

    yes it works!! Thanks

    I will advice them to reinstall the SSMS.

    Previously I just went through the installation logs for ssms, it shows installed successfully.

    Thanks,

    Yuyu

    Wednesday, October 26, 2016 8:12 AM