locked
Authentication failure for both windows and SQL Server logons unless member of sysadmin RRS feed

  • Question

  • I have a named instance (we use many) that is having an authentication problem. This happens with both Windows and SQL Server authentication. Unless I assign the user to the “sysadmin” role, they cannot be authenticated. Here are the Windows Application Log entries:

    SQL Server 2008 R2 Standard 64-bit running on Windows Server 2008 R2 64-bit

    Event ID: 18456

    Keywords: Classic,Audit Failure

    Task Category: Logon

    SQL Server authentication:

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

    Windows authentication:

    Login failed for user '<domain name>\<user name>'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 192.168.5.56]

    Databases for the following applications were installed, which installation processes also installed databases on this server; the applications themselves are not on this server box. The applications are: Windows SUS, Desktop Authority, SysAid, VMWARE

    Tuesday, May 28, 2013 1:33 PM

Answers

  • I guess that if you drop the ConfigMgrEndpoint, something will stop working.

    Since I'm completely unacquainted with System Center 2012 Configuration Manager, I'm reaching out to my MVP colleagues for advice.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Altawan Wednesday, June 5, 2013 7:52 PM
    Tuesday, June 4, 2013 10:11 PM

All replies

  • Hi,

    What is the error state in the SQL Server error log? Do you have UAC diabled or enabled? please check this article too: http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx

    Janos


    There are 10 type of people. Those who understand binary and those who do not.

    My Blog | Hire Me

    Tuesday, May 28, 2013 3:01 PM
  • Janos,

    Both Windows and SQL Server users are error 18456, severity 14. Windows is status 11 and SQL status 12. From the link that you sent me (thank you), that means: valid login but server access failure.

    UAC is turned on and set to the default level: Notify me only when programs try to make changes to my computer. On this server box, I have, in addition to the initial installation, I have two named instances of SQL Server running. It is the second installed instance that I am having the problem with i.e. the base and first named instance are running correctly. If we change the UAC setting, I would expect it to affect the three sql servers installed on the box.

    Michael

    Tuesday, May 28, 2013 6:09 PM
  • Hello Michael,

    It just looks like your instance is having issue in communicating with the network in order authenticate the users. Can you check if the SQL server Service is running under a domain account or a Local System account. Also please see Alberto Morillo's reply in the following post can help your situation. 

    http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/09511209-4e50-4e32-90e4-bc084f41211c/


    Hima, To help the community better identify relevant thread, please don't forget to mark as answer or vote as helpful if and answer helps.

    Tuesday, May 28, 2013 9:22 PM
  • When you get this error with Windows authentication, it's typically a hiccup
    with the AD, but you also get it with an SQL login. From what I have seen
    this appears to be a permissions problem.

    Let's start with these two queries:

    SELECT * FROM sys.server_permissions WHERE class_desc = 'ENDPOINT'
    SELECT * FROM sys.endpoints

    Can you post the output of these?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Fanny Liu Wednesday, May 29, 2013 1:45 AM
    Tuesday, May 28, 2013 9:46 PM
  • Try this -

    GRANT VIEW ANY DATABASE TO public
    GRANT CONNECT ON ENDPOINT::[TSQL Local Machine] TO public
    GRANT CONNECT ON ENDPOINT::[TSQL Named Pipes] TO public
    GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] TO public
    GRANT CONNECT ON ENDPOINT::[TSQL Default VIA] TO public

    GO

    • Proposed as answer by Fanny Liu Wednesday, May 29, 2013 1:52 AM
    Tuesday, May 28, 2013 10:18 PM
  • Hello,

    Just as Erland post above, the issue may be caused by the premission. For example, the permissions from SQL Server's public role and the CONNECT permission from the database guest accounts had been removed for security. To solve this issue, please try to grant connection privileges to the server and to specific endpoints for specific users or groups.

    Please refer to the following blog:http://dbccpage.wordpress.com/2011/11/19/how-to-solve-error-18456-severity-14-state-12-login-based-server-access-validation-failed-with-an-infrastructure-error-check-for-previous-errors/

    Regards,
    Fanny Liu

    If you have any feedback on our support, please click here.


    Fanny Liu
    TechNet Community Support

    Wednesday, May 29, 2013 1:59 AM
  • Erland,

    I ran the script as you suggested against several of my servers and found them to give the same result, except for the problematic one. The problem server has an entry on the sys.endpoints table named ConfigMgrEndpoint.

    Michael 

    SELECT * FROM sys.server_permissions WHERE class_desc = 'ENDPOINT'
    class	class_desc	major_id	minor_id	grantee_principal_id	grantor_principal_id	type	permission_name	state	state_desc
    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
    
    SELECT * FROM sys.endpoints
    name	endpoint_id	principal_id	protocol	protocol_desc	type	type_desc	state	state_desc	is_admin_endpoint
    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	261	2	TCP	3	SERVICE_BROKER	0	STARTED	0
    

    Tuesday, June 4, 2013 12:57 PM
  • Erland,

    On investigating the "ConfigMgrEndpoint" entry in sys.endpoints in google, I found your name on a similar problem recently http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/ec56412e-4721-461d-abc4-d1e5b89115af . I found ou that we too recently installed System Center 2012 Configuration Manager. If SC does only use port 1433, then that explains the problem, as the SQL Server instance of course uses an alternate port.

    I am reviewing further the other thread. Do you have any additional suggestions? Cam I simply delete the entry for the SERVICE_BROKER?

    Michael

    Tuesday, June 4, 2013 7:07 PM
  • I guess that if you drop the ConfigMgrEndpoint, something will stop working.

    Since I'm completely unacquainted with System Center 2012 Configuration Manager, I'm reaching out to my MVP colleagues for advice.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Altawan Wednesday, June 5, 2013 7:52 PM
    Tuesday, June 4, 2013 10:11 PM
  • Thanks for your help Erland. You pointed me in the right direction and we were able to identify System Center as the cupret. Our network guys are re-evaluating its installation with regard to this new information about it. Once that is done, I'll deal further with clearing the endpoint problem from this instance of SQL Server.

    Michael;

    Wednesday, June 5, 2013 7:55 PM
  • Ah, stupid me. Due to a discussion on the MVP list I did actually look at
    http://technet.microsoft.com/en-us/library/gg682077.aspx#BKMK_SupConfigSQLSrvReq
    just a few days ago. What I did not observe is that it says Supported Configurations for Configuration Manager in the title. I thought it was about System Center in general. (Which I'm entirely unacqauinted with.)

    And the interesting part is this:

    SQL Server instance: You must use a dedicated instance of SQL Server for each site.

    Reasonably this means that you should put Configuration Manager on an instance where there are no other databases. The queue might be one reason for it, but the installation also slaps on a undocumented trace flag on the instance.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, June 5, 2013 9:37 PM