Wednesday, March 21, 2012

Error: 18456 Severity: 14, State: 11

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.

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

|||

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!

|||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
|||

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

|||

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

|||

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

|||Thanks for the hint. This didn't solve my problem. Do I have to grant any special rights after creating those logins ?|||

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)

No comments:

Post a Comment