none
Error: 18456 Severity: 14, State: 11 RRS feed

  • Question

  • I have a SQL 2005 SP1 test server running Developer Edition on top of Windows Server 2003 SP1.  We're testing out a 3rd party application that is using Windows Authentication to connect to SQL 2005.  The SQL Error Log is saturated with the error message listed in the subject line.  The text accompanying the message alternates between "Login failed for user 'MYDOMAIN\TESTDA1$'. [CLIENT: <named pipe>]" and "Login failed for user 'MYDOMAIN\TESTDA1$'. [CLIENT: xxx.xxx.xxx.xxx]".

    There are two strange parts to this:  1) The application appears to be working fine on the surface, and, 2) The MYDOMAIN\TESTDA1$ account mentioned in the error text is not the user account that we are using to connect the application to SQL 2005.  TESTDA1 is the server name that the 3rd party software is running remotely from.

    I've looked up "error state 11" using the following resource:

    http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx

    It describes error states 11 and 12 as "Valid login but server access failure" but I'm not sure what that means.  Why would SQL care about the computer account hosting the application?

    Thanks in advance for any help.

    Friday, August 4, 2006 2:27 PM

Answers

  •   From your description it seems like the 3rd party software is attempting to connect to SQL Server using the service account credentials, it is most likely running under “Network Service”, “Local System” or another local account and that’s why when trying to access resources (in this case connect to SQL Server) it uses the machine account (<domain>\<machine_name>$), and as this account doesn’t have privileges to connect to SQL Server it fails.

     

      Check the 3rd party product documentation, it may be possible that you need to run it using a domain account (I also recommend verifying with your domain administrator regarding your specific environment policies regarding running services under domain accounts).

     

      Another potential workaround would be to grant permission to connect to this machine account, and granting it the minimum permissions required for the software you are testing. Bear in mind than any other service from the same machine will have the same privileges.

     

       I hope this information helps,

     

    -Raul Garcia

      SDE/T

      SQL Server Engine

     

    Friday, August 4, 2006 6:32 PM
    Moderator
  •     You can use the CREATE LOGIN DDL to create the login for the machine account, for example:

    CREATE LOGIN [DOMAIN\machine_name$] FROM WINDOWS

     

       Let us know if this information helped and/or if you have any additional questions.

     

      Thanks,

    -Raul Garcia

      SDE/T

      SQL Server Engine

    Tuesday, July 10, 2007 5:56 PM
    Moderator

All replies

  •   From your description it seems like the 3rd party software is attempting to connect to SQL Server using the service account credentials, it is most likely running under “Network Service”, “Local System” or another local account and that’s why when trying to access resources (in this case connect to SQL Server) it uses the machine account (<domain>\<machine_name>$), and as this account doesn’t have privileges to connect to SQL Server it fails.

     

      Check the 3rd party product documentation, it may be possible that you need to run it using a domain account (I also recommend verifying with your domain administrator regarding your specific environment policies regarding running services under domain accounts).

     

      Another potential workaround would be to grant permission to connect to this machine account, and granting it the minimum permissions required for the software you are testing. Bear in mind than any other service from the same machine will have the same privileges.

     

       I hope this information helps,

     

    -Raul Garcia

      SDE/T

      SQL Server Engine

     

    Friday, August 4, 2006 6:32 PM
    Moderator
  • Thanks Raul.  You were correct - the software was indeed trying to connect using the "Local System" account.  We changed this to a domain account that had permission to login to SQL and now everything is working well.  No more errors in the SQL Error Log.

    Thanks again!

    Friday, August 4, 2006 10:18 PM
  • I have the same error message in my logs for SQL Server 2005.  I've set up a report, gave both the user and the group she belongs to full access to the reports and Content Manager and Browser.  She can go through the folders, but if she gets the report and clicks on the link to open the report (on http://servername/reports etc) she gets the error message "cannot create connection to data source 'databasename' and in the error logs it says:

     Error: 18456, Severity: 14, State: 11.
     Login failed for user 'DOMAIN\User.Name'. [CLIENT: <local machine>]

    where domain = an active directory (not local box) domain and User.Name is the actual name of the user who is in that domain.

    The error being state 11 means Valid login but server access failure so where do I look to figure out why she can't get into the report?  She's trying to access across the network so she's not physically logging onto that computer.  Any idea what I need to check?

    Thanks!

    Linda
    Tuesday, August 8, 2006 4:45 PM
  •   Connect to SQL server using a sysadmin account and look if the Windows user has access to SQL Server. You can look first in sys.server_principals for example:

     

    SELECT * FROM sys.server_principals

    Go

     

      And look for your Windows principal name directly, or for a group she belongs to and grants permission to connect. If you cannot find the proper entry, you can grant permission to connect to SQL Server by adding a login.

     

      On the other hand, if the login exists, it may be possible that that particular user doesn’t have permissions to connect in the right endpoint. To see all the permissions on the endpoints you can run the following query:

     

    SELECT endpnt.name,

           suser_name(perms.grantee_principal_id) as grantee_principal,

           perms.permission_name, perms.state_desc

        FROM

           sys.server_permissions perms,

           sys.endpoints endpnt

        WHERE

           perms.class = 105

           AND perms.major_id = endpnt.endpoint_id

    go

     

      If there is a permission missing for this principal, just grant the CONNECT permission at the correct endpoint.

     

      -Raul Garcia

      SDE/T

      SQL Server Engine

    Tuesday, August 8, 2006 8:24 PM
    Moderator
  •  Raul Garcia - MS wrote:

      From your description it seems like the 3rd party software is attempting to connect to SQL Server using the service account credentials, it is most likely running under “Network Service”, “Local System” or another local account and that’s why when trying to access resources (in this case connect to SQL Server) it uses the machine account (<domain>\<machine_name>$), and as this account doesn’t have privileges to connect to SQL Server it fails.

     

      Check the 3rd party product documentation, it may be possible that you need to run it using a domain account (I also recommend verifying with your domain administrator regarding your specific environment policies regarding running services under domain accounts).

     

      Another potential workaround would be to grant permission to connect to this machine account, and granting it the minimum permissions required for the software you are testing. Bear in mind than any other service from the same machine will have the same privileges.

     

       I hope this information helps,

     

    -Raul Garcia

      SDE/T

      SQL Server Engine

     

     

    Hello there,

     

    I got exactly the same scenario. Unfortunately I am not able to change the service account to any other thing than local system.

    If I would like to add access to my database for the machine$ accounts, what exactly would I have to do ?

    Could anyone give me a hint please ?

     

    Thanks in advance

     

    Gordon

    • Proposed as answer by impeeza Monday, September 7, 2009 10:09 PM
    Tuesday, July 10, 2007 6:06 AM
  •     You can use the CREATE LOGIN DDL to create the login for the machine account, for example:

    CREATE LOGIN [DOMAIN\machine_name$] FROM WINDOWS

     

       Let us know if this information helped and/or if you have any additional questions.

     

      Thanks,

    -Raul Garcia

      SDE/T

      SQL Server Engine

    Tuesday, July 10, 2007 5:56 PM
    Moderator
  • Thanks for the hint. This didn't solve my problem. Do I have to grant any special rights after creating those logins ?
    Thursday, July 12, 2007 5:20 AM
  • After creating the logins I forgot to map a role to the users for my database.

    I just followed the instructions found in another article here :

    navigate through Security > Logins > Properties > User Mapping > Choose the Database > Map the user to the database (you will see that he will get the public role by default)

    Friday, July 13, 2007 7:54 AM
  •  Raul Garcia - MS wrote:

      From your description it seems like the 3rd party software is attempting to connect to SQL Server using the service account credentials, it is most likely running under “Network Service”, “Local System” or another local account and that’s why when trying to access resources (in this case connect to SQL Server) it uses the machine account (<domain>\<machine_name>$), and as this account doesn’t have privileges to connect to SQL Server it fails.

     

      Check the 3rd party product documentation, it may be possible that you need to run it using a domain account (I also recommend verifying with your domain administrator regarding your specific environment policies regarding running services under domain accounts).

     

      Another potential workaround would be to grant permission to connect to this machine account, and granting it the minimum permissions required for the software you are testing. Bear in mind than any other service from the same machine will have the same privileges.

     

       I hope this information helps,

     

    -Raul Garcia

      SDE/T

      SQL Server Engine

     

     

     

     

    Hello there,

     

    I got exactly the same scenario. Unfortunately I am not able to change the service account to any other thing than local system.

    If I would like to add access to my database for the machine$ accounts, what exactly would I have to do ?

    Could anyone give me a hint please ?

     

    Thanks in advance

     

    Gordon


    Hello, can you use "Network Service"?, by default the services in the "Local Service" and "Local System" account cannot access resources outside the local system. for network access you must use "Network Service" account.  after that you must add the DOMAIN\MACHINE$ account in the SQL Server as an user.
    Monday, September 7, 2009 10:11 PM
  • I'm having the same problem using a domain account that is in the administrators group on the box and is also the account the SQL Server service is running under.

    I have four boxes, supposedly all configured alike and all running Windows Server 2008 R2.  All have the same SQL Server build: 9.0.3042.  Three of them work fine, but I can't attach to SQL Server on the fourth.

    I can log onto the server using that account, but when I try to open Management Studio, trying to connect to SQL Server using Windows authentication, it fails with Error: 18456 Severity: 14 State: 11.  That really confuses me.  I'm logged onto the server using that account, that account is running the SQL Server service, but when I try to log onto Management Studio I get an error that says it's a valid login, but it's a server access failure.

    Thursday, April 14, 2011 4:11 PM
  • I believe that Raul Garcia described a method of investigating this problem up above in this thread in his Tuesday, August 08, 2006 8:24 PM post.  You might need to login explicitly as 'sa' if you cannot otherwise connect, then run his script

    Also, here is a reference from Lekss on 18456 and the meaning of the State of the error.

    http://sql-articles.com/articles/troubleshooting/troubleshooting-login-failed-error-18456

    It appears that although you administer the machine, you login has not been granted rights to the SQL Server.  This is a bit surprising, since it is the same login as the SQL Server service account.  Did someone change the service account outside of using the configuration manager?  Is the account part of builtin\administrators?  If not, is that different from the other servers?

    (It is a best practice not to grant builtin\administrators rights to the SQL Server itself, especially not as administrators.  There are reasons a account might need to be a machine administrator, but it does not follow that that account should also be a SQL Server administrator.)

    By the way, this thread is years old.  It is usually better to start a new post rather than resurrect on old one.

    RLF

    Thursday, April 14, 2011 6:03 PM
  • I am an admin and I have run both of those scripts on all four boxes.  The results are all the same.

    I have read the article you referred to, but saw nothing there that I didn't already know or hadn't already tried.

    I agree that it's very weird that the service account SQL Server is running under can't access SQL Server.  I didn't install SQL Server on any of the four boxes so I can't say with any certainty how the service account was set.  But just to see if it made any difference, I used configuration to change it to a local service, verified that the (former) service account still couldn't get in, used configuration manager to set it back to the service account, and verified that I still couldn't get in under that account.  The account is not in builtin\administrators.

    Let me backup here and say that I did do something it said in the article you referred me to and it did fix rthe problem, but even though it worked, I'm still not satisfied.  I still think something is amiss on this one box.

    What I did (before reading the article) was to explicitly add the service account as a login.  When I do that, I'm able to bring up Management Studio and connect.  What troubles me is that the account has not been explicitly added on any of the other three boxes and does not appear in the list of logins.  Maybe I'm just being anal, but I feel that if I have to explicitly add a login on this box, but not on any of the others, there must be something else out there that I've missed.  Why would it be required on one box, but not on the other three?

    Thursday, April 14, 2011 9:11 PM
  • It would bother me, too.  (And it is not like I have never had "what's going on here" issues with rights, with logins, with SQL Agent job owners, etc.)

    Try running this on all four servers and look for anything that is not alike:

    xp_logininfo 'domain\sqlserviceaccount', 'all'

    The 'all' should list every permission path that gets a login into the server.  I have no particular expectation, but if you find something different, it would be interesting.

    Strictly FWIW,
    RLF

     

    Friday, April 15, 2011 1:28 PM

  • Thanks Raul.  You were correct - the software was indeed trying to connect using the "Local System" account.  We changed this to a domain account that had permission to login to SQL and now everything is working well.  No more errors in the SQL Error Log.

    Thanks again!

    Hi Brett !

    I'm a little on this since its a 5 year old post , but I'm wondering where to look to find if the software is using a local account vs. a domain account . Is this something based on the actual user of that particular machine that is setup on the Active Directory ? if you explain it would be appreciated.

    Thanks

    Tuesday, May 31, 2011 4:18 PM
  • One of the reasons, Can be absent permission to connection with the server!
    We check:

      select name, endpoint_id,e.state_desc,s.permission_name,s.state_desc from sys.endpoints e
      join sys.server_permissions s
      on e.endpoint_id = s.major_id
      where class = 105

    has to look so:


    If there is no permission add it
    Example:

    use [master]
    GO
    GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] TO [public] AS [sa]
    GO
    • Edited by IDimas_ Tuesday, December 22, 2015 12:05 PM
    Tuesday, December 22, 2015 12:03 PM