none
Server is set to mixed auth mode but error log reports login is rejected due to server in windows auth mode only.

    Question

  • I am trying to connect remotely to an SQL Server 2008 Express database on a MS Server 2008 R2.

    I can connect using either Windows Authentication (admin login) or SQL Server Authentication (database user login) using Sql Management Studio while I'm in Remote Desktop on the server but cannot connect remotely using any user login.

    I get 'Login failed for user [username]; Error Number: 18456, Severity: 14, State: 1, Line Number: 65536'.

    The SQL Server log reports this: 'Login failed for user (database user login). Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: ip].

    However, the server is set to both Sql Server and Windows Authentication mode.

    Any help would be appreciated. Thanks.

     

     

    Wednesday, January 04, 2012 2:52 AM

Answers

  • Thank you everyone that replied. I solved the problem on my own.

    The correct answer is this:

    If more than one instance of SQL Server is installed and running on a server, the higher version takes precedence and those settings need to be configured for remote connections and mixed mode.

    What I found out was that I had two versions of SQL Server installed. 2008 Express and 2008 Express R2. 2008 Express which comes with Visual Studio Express installs under SERVERNAME\SQLEXPRESS while R2 installs under SERVERNAME. I was working with .\SQLEXPRESS and didn't think to try setting up R2 for remote connection.
    By default both versions install on the same port and settings but only one can be connected, the higher version R2. Once I enabled mixed mode on R2 I connected with no problem. I moved my database to the R2 also.

    You can probably assign different ports for each version but didn't go back to try it. I only need one to work. For others that may need to have multiple installs running simultaneously on the same server try different ports for each instance and post here if that does work.

    Thanks,
    Rafael

    • Marked as answer by Rafael101 Tuesday, February 21, 2012 7:22 AM
    Tuesday, February 21, 2012 7:21 AM

All replies

  • Please restart SQL Server and try again.


    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    MC ID: Microsoft Transcript

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
    • Proposed as answer by RamJaddu Wednesday, January 04, 2012 7:01 PM
    Wednesday, January 04, 2012 3:36 AM
  • you must restart SQL Server service after SQL Autentication mode changes done
    http://uk.linkedin.com/in/ramjaddu
    Wednesday, January 04, 2012 7:03 PM
  • Did anything recently applied sql patch on your SQL, because I have seen some cases after applying the patch the Authentication mode will switch to Windows mode from Mixedmode, hence it is always good practise is that go and check the SQL error log instead on GUI....

    Hence verify from your end did anything recenlty change has been done or try give an SQL server services restart then check the errorlog

    use master

    go

    exec xp_readerrorlog

     

    Best Regards,

    Rama Udaya.K

    ramaudaya.blogspot.com

     

    Thursday, January 05, 2012 8:21 AM
  • I did restart the sql server and re-booted the server and double-checked that the setting is in mixed mode. There were no recent windows updates to the server at that time. Since then, I applied the latest windows updates and checked again the settings and restarted the server and am in mixed mode but I get the same login denied because in Windows auth mode when it's clearly set to mixed mode. There is no firewall issue because it hits the server, logs the attempt and bounces back the login fail immediately.

    The server is a GoDaddy hosted virtual dedicated server.

    Thanks.

    Wednesday, January 25, 2012 7:18 AM
  • Still you are gettting the same error or any other if so please check the SQL errorlog ...

    I get 'Login failed for user [username]; Error Number: 18456, Severity: 14, State: 1, Line Number: 65536'.

    The SQL Server log reports this: 'Login failed for user (database user login). Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: ip].

    Can we know what is the SQL server version you are using?


    Rama Udaya.K ramaudaya.blogspot.com
    Thursday, January 26, 2012 11:25 AM
  •  

    I get 'Login failed for user [username]; Error Number: 18456, Severity: 14, State: 1, Line Number: 65536'.


    Hi Rafael,

    Error information is not available. This state usually means you do not have permission to receive the error details.

    Note that the message is kept fairly nondescript to prevent information disclosure to unauthenticated clients.   In particular, the 'State' will always be shown to be '1' regardless of the nature of the problem.  To determine the true reason for the failure, the administrator can look in the server's error log where a corresponding entry will be written.


    How to troubleshoot: http://msdn.microsoft.com/en-us/library/ms366351(v=sql.100).aspx
                                   http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx.

    Also check this blog: http://blogs.msdn.com/b/sqlsakthi/archive/2011/02/06/how-to-troubleshoot-connectivity-failure-error-with-sql-server.aspx.

     

    Thanks,
    Maggie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.
    Wednesday, February 01, 2012 9:01 AM
  •  'Login failed for user (database user login). Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: ip].' when settings are set to mixed mode.

    Server setup:

    * GoDaddy virtual dedicated server.
    * Windows Server 2008 R2 (Windows NT 6.1 (7601)).
    * Sql Server 2008 Express Edition 10.0.1600.22.
    * Microsoft Visual Web Developer 2008 Express Edition.
    * Microsoft Visual Web Developer 2010 Express Edition.

    Thanks,

    Rafael

    Friday, February 17, 2012 7:19 PM
  • Judging from the error message, SQL Server runs with Windows authentication only. Did you restart SQL Server after you changed the setting?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, February 17, 2012 10:49 PM
  • Thank you everyone that replied. I solved the problem on my own.

    The correct answer is this:

    If more than one instance of SQL Server is installed and running on a server, the higher version takes precedence and those settings need to be configured for remote connections and mixed mode.

    What I found out was that I had two versions of SQL Server installed. 2008 Express and 2008 Express R2. 2008 Express which comes with Visual Studio Express installs under SERVERNAME\SQLEXPRESS while R2 installs under SERVERNAME. I was working with .\SQLEXPRESS and didn't think to try setting up R2 for remote connection.
    By default both versions install on the same port and settings but only one can be connected, the higher version R2. Once I enabled mixed mode on R2 I connected with no problem. I moved my database to the R2 also.

    You can probably assign different ports for each version but didn't go back to try it. I only need one to work. For others that may need to have multiple installs running simultaneously on the same server try different ports for each instance and post here if that does work.

    Thanks,
    Rafael

    • Marked as answer by Rafael101 Tuesday, February 21, 2012 7:22 AM
    Tuesday, February 21, 2012 7:21 AM
  • I think that if you have instances set to listen to the same port, it's the instance that starts the first which wins the port.

    You can use the SQL Server Configuration Utility to change ports. You can also set Dynamic Ports, so that an instance selects a different port, if the port is used to use is busy.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, February 21, 2012 10:47 PM