locked
SQL 2008 R2 - Login Error 18456 Severity: 14, State: 12 Remote SQL Authentication only RRS feed

  • Question

  • I am having trouble logging into SQL server 2008 R2 using SSMS and using SQL Server Authentication, and only using remote connection. If I use SSMS on the local computer, the login works.

    1. Remote SSMS connection

    2. SQL Server Authentication (windows authentication works)

    3. Allow Remote connections is turned on

    4. TCP/IP is Enabled

    5. Named Pipes are not used

    SQL 2008 R2 (SP2) 10.50.40000.0 x64

    Friday, May 3, 2013 8:00 PM

Answers

  • I will have to admit that I'm running out of ideas. The reason that I made my initial suggestion is that I know the revoking the CONNECT permission on the TCP port produces this error. But given it's level of obscurity, this is could be a general catch-all error.

    Anyway, there are a few more DMVs to look in:

    SELECT * FROM sys.tcp_endpoints

    SELECT * FROM sys.service_broker_endpoints


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, May 7, 2013 9:43 PM

All replies

  • It seems like the CONNECT permission for the TCP/IP endpoint has been revoked. Run this:

    SELECT * FROM sys.endpoints
    SELECT * FROM sys.server_permissions WHERE class = 105

    On my machine the first query lists five rows, and the second query lists four rows, one for each endpoint, except for the Dedicated Admin Connection. If my theory is correct, there is now row in the in the latter result set for the TCP endpoints. The rememdy would be:

    GRANT CONNECT ON ENDPOINT::[TSQL Default TCP]

    Assuming that this is the TCP endpoint without permission.

    Also look for explicit DENY, which overrides GRANT.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, May 3, 2013 10:10 PM
  • In order to resolve the issue, please refer to the following steps:

    1. Login to the MSSQL Server Management Studio with Windows Authentication.
    2. In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.
    3. Under the Server Properties, select a page of “Security”.
    4. Select the Server authentication as “SQL Server and Windows Authentication mode” and click Ok.

    5. Restart the SQL Services and then try to login with ‘sa’ details.

    Saturday, May 4, 2013 8:12 AM
  • Hi

    Check with windows firewall also, please go through below link  issue will be resolve.

    @@http://blogs.msdn.com/b/walzenbach/archive/2010/04/14/how-to-enable-remote-connections-in-sql-server-2008.aspx

    Monday, May 6, 2013 8:50 AM
  • This issue is happening on our test server. An IS associate installed Configuration Manager. After running the end point query you provided above, I have 3 TCP protocol_desc, which is different than our live server. I think all the TCP Endpoints have been assigned GRANT (no DENYs) accept for the 3rd one listed below.

    1. Dedicated Admin Connection

    2.  TSQL Default TCP

    3. ConfigMgrEndpoint TCP with a type_desc as Service_Broker. The endpoint_id for ConfigMgrEndpoint does not have a permission record.

    Name---------------endpoint_id--principla_id--protocol--protocol_desc--type--type_desc

    ConfigMgrEndpoint 65536 ------- 271 ----------- 2  --------TCP ----------- 3 ---SERVICE_BROKER 

    Could this be causing the issue? Should I try to remove ConfigMgrEndPoint or try to grant it access.

    On another note:

    If I grant "Control Server" access to my test user under Securables, I can log in as this user.


    Monday, May 6, 2013 3:00 PM
  • Detailed troubleshooting steps on the TechNet SQL Server wiki How to Troubleshoot Connecting to the SQL Server Database Engine

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Monday, May 6, 2013 4:04 PM
  • These are all good suggestions, but still hasn't solved the issue. When logged on the computer, see below for the SSMS logins that work and don't work. Pay attention to the Server Name and the Login name. The won't work ones will be in the following post.

    These Work

    Monday, May 6, 2013 6:47 PM
  • This does not work:

    It will work if I grant "control server" access or make TestUser a sysadmin.

    Monday, May 6, 2013 6:48 PM
  • Anything else in the SQL Server error log or Windows Event log?

    Clicking Options in the screenshot above, is there anything unexpected in the Connection Properties or Additional Connection Parameters tab?


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Monday, May 6, 2013 8:54 PM
  • I don't think the Service Broker endpoint has anything to do with it. The type is not TSQL.

    Can you post the output from

    SELECT * FROM sys.server_permissions WHERE class = 105


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, May 6, 2013 9:33 PM
  • Still working on the issue. Thank you for the help. I would like to figure out the issue instead of just reinstalling the server.

    I checked the Connection Properties earlier and didn't notice anything. I do know when I use the user that doesn't work, I can't pull back database using the browse.

    Outputs.

    name--------------------------ei pi ----p pd --------------- type des -------------------s sd -------iae
    Dedicated Admin Connection 1 1 ----2 TCP---------------  2 ---TSQL---------------- 0 STARTED 1
    TSQL Local Machine---------  2 1 ----4 SHARED_MEMORY 2 ---TSQL--------------- 0 STARTED 0
    TSQL Named Pipes----------  3 1 ----3 NAMED_PIPES -----2 ---TSQL ---------------0 STARTED 0
    TSQL Default TCP-----------  4 1 ----2 TCP  -----------------2 ---TSQL ---------------0 STARTED 0
    TSQL Default VIA ----------- 5 1 ----5 VIA ------------------ 2 ---TSQL --------------0 STARTED 0
    ConfigMgrEndpoint ---- 65536 271- 2 TCP ------------------ 3 --SERVICE_BROKER 0 STARTED 0

    class --class_desc -m -m gp gpid type pn --------state sd
    105 --ENDPOINT --2 --0 -2 --1 --CO   CONNECT G ----GRANT
    105 --ENDPOINT --3 --0 -2 --1 --CO   CONNECT G ----GRANT
    105 --ENDPOINT --4 --0 -2 --1 --CO   CONNECT G ----GRANT
    105 --ENDPOINT --5 --0 -2 --1 --CO   CONNECT G ----GRANT

    Tuesday, May 7, 2013 12:13 PM
  • Log

    Date  5/7/2013 8:21:25 AM
    Log  SQL Server (Current - 5/7/2013 8:04:00 AM)

    Source  Logon

    Message
    Error: 18456, Severity: 14, State: 12.

    Date  5/7/2013 8:21:25 AM
    Log  SQL Server (Current - 5/7/2013 8:04:00 AM)

    Source  Logon

    Message
    Login failed for user 'TestUser'. Reason: Login-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 192.168.45.11]

    Tuesday, May 7, 2013 12:26 PM
  • I ran this (still doesn't work)

    Grant connect sql to Public
    Grant connect on endpoint::[TSQL Default TCP] to Public

    Grant connect sql to TestUser
    Grant connect on endpoint::[TSQL Default TCP] to TestUser

    Tuesday, May 7, 2013 12:43 PM
  • More info.

    GRANT CONNECT TO PUBLIC

    Msg 4627, Level 16, State 1, Line 1

    Cannot grant, deny, or revoke the connect database permission to roles and application roles.

    Tuesday, May 7, 2013 1:01 PM
  • I will have to admit that I'm running out of ideas. The reason that I made my initial suggestion is that I know the revoking the CONNECT permission on the TCP port produces this error. But given it's level of obscurity, this is could be a general catch-all error.

    Anyway, there are a few more DMVs to look in:

    SELECT * FROM sys.tcp_endpoints

    SELECT * FROM sys.service_broker_endpoints


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, May 7, 2013 9:43 PM
  • Your last query helped identify the issue. Now I need to figure out a solution.

    name --------------------------endpoint_id principal_id protocol_desc type_desc ----port
    Dedicated Admin Connection 1 ----------------1 ----------TCP -----TSQL -------------0
    TSQL Default TCP -------------4 ----------------1---------- TCP -----TSQL ---------------0
    ConfigMgrEndpoint------------ 65536 ---------271 --------TCP ---SERVICE_BROKER 1433

    Whatever this ConfigMgrEndpoint is, it using the TCP default port and overriding the port rules. This probably happened when I helped install System Center 2012 Configuration Manager. I just found out the System Center will only use port 1433.

    If I temporarily change the port for the endpoint, alter endpoint [ConfigMgrEndpoint] as TCP (listener_port = 6060), Success, I can connect!!

    However, the port automatically is changed back after about 15 minutes.

    I don't have a lot of knowledge with endpoints, any suggestions?

    Wednesday, May 8, 2013 3:22 PM
  • The script

    SELECT * FROM sys.tcp_endpoints

    SELECT * FROM sys.service_broker_endpoints

    Identified the issue. I ran: drop endpoint ConfigMgrEndpoint. Issue resovled. I don't know how this EndPoint was installed. I will see if another application stops working.

    Wednesday, May 8, 2013 6:53 PM
  • Great to hear that you were able to solve the problem!

    I made an attempt last night with creating a Service Broker endpoint, but when I tried to log in with a low-priv user it worked. I realise now that I had a brainfart - I made my login attempt from the local machine. When I try it from a remote machine, I get the error with state 12 just like you did.

    I haven't worked much with endpoints for Service Broker, but from what I can make out configuring a Service Broker endpoint to use 1433 (when that port is used by a default instance) is incorrect. In fact, I see this message in my errorlog:

    The Service Broker protocol transport cannot listen on port 1433 because it is in use by another process.

    I also note that when I log in remotely over TCP, and view that connection in sys.dm_exec_connections, it lists endpoint_id 65537 and reports the protocol type as Service Broker. Interesting enough the spid is reported as NULL.

    Thanks for reporting back. This way I also learnt something from this problem.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, May 8, 2013 9:57 PM
  • Did your points get assigned? I was unsure how to assign the points.
    Thursday, May 9, 2013 12:08 PM
  • I have no idea how points are assigned. Nor do I care. What I care about is that I am able to help people.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, May 9, 2013 7:20 PM