builtin\administrators is not resolved

Article
07/31/2019

Question

_{Wednesday, July 31, 2019 3:09 PM}

Hi,

I face a strange problem. First it occured on an RDS instance in AWS, but for deciding in which direction to go I installed an on premise SQL Server 2014 and I tried this also with SQL server 2016

Settings in general: mixed authentication

- one Windows account defined as administrator during setup

- added the "administrators" group

install the server and finished.

Then I log on with a DIFFERENT account with the name SQLADMIN belonging to the domain admins. At server level I can log in and by group membership in the "domain admins" group I get access to the desktop.

However when I try to log on at the SQL server with Windows authentication it fails... why this doesn't work with the "builtin\administrators"? According to some search results the global "domain admins" group becomes member of the local administrators group but why the SQL server doesn't resolve this?

I also ran a check by creating a login called "domain admins" group having the same behavior, it's not the group concatenation, it's (obviously) the inability to resolve AD group memverships to server logins. Or am I missing something?

Then I created a login at my server called "<domain>\SQLADMIN" and gave him the public right only. Even now the group membership is not applied, he effectively has public although he is member of another AD group that has sysadm rights.

I am somehow confused because the resolution of AD group memberships works perfectly with database logins... I can assign the DB_OWNER right to an AD group and all members of that group get the DB_OWNER right if SQL server is 2012 or higher. But at server side it won't work with sysadm .... is this a bug, an inexisting feature or something else?

IT architect - Terminal servers, virtualizations, SQL servers, file servers, WAN networks and closely related to software devleopment (8 years + experience in VB, C++ and script langugaes), MCP for SQL server and CCAA for Xenapp 6.5

All replies (19)

_{Friday, August 2, 2019 5:54 AM ✅Answered}

Win16 is the domain name.

to 1.) I already said that I granted manually the connect permissions, although this is implicitely granted by assigning the public role. BTW this is part of the MCP exam for the SQL Server.

to 2.) you are an MVP... so you should know that builtin groups have a fixed SID. As they are builtin\administrators for example... this is since Windows NT.

I aldready did execute that SQL script with the "builtin\administrators" group as I mention above.

So I now repeated that SQL statement with the win16\domain admins, but still get the same result. The SQL statement just works around a design fault in SSMS which does not search in Windows groups by default.

When I check this thing I am able to do that what the SQL statement does by SQL.

So all of these magic SQL statements are obviously trusted by you people without really knowing what they do... another 360° on my circle.

Found a blog article from Microsoft

https://blogs.msdn.microsoft.com/psssql/2016/07/09/why-do-i-get-the-infrastructure-error-for-login-failures/

where I worked through all of it, but when I look at the comments then I come to the conclusion that I miss something because it is no issue wiht GRANTED rights, it is no issue with DENIED rights,

IT IS AN ISSUE WITH ELEVATION.

That's something I couldn't believe... it's ok when the client side doesn't tell you the reason why a login failed... but the corresponding entry in the SQL server side AND it's documentation are obscure too beacue it is never stated anywhere that this is caused by interpretation of an unset GPO concerning the elevation. After a certain patchlevel the login fails, and I have to set the elevation GPO to "elevate without prompting" although here I can't really see why I need that. It is related to a patch I guess issued in 2017, which is obviously part of the latest Windows 2016 image from MSDN and it is not in the RTM of Windows 2012...

I have to launch the management studio with "run as administrator" then it connects with the SQL server.

_{Wednesday, July 31, 2019 3:37 PM}

The local administrators group is no longer granted rights to SQL Server as a default, since SQL 2008. You need to add that group to SQL Server or preferably add a new group with only the people who should have access.

_{Thursday, August 1, 2019 6:22 AM}

Dear Tom, as I wrote I added the <domain>\domain admins group and this also doesn't work for SQL server login.

And it is useless to speculate about the capabilities of SQL2005, becaue it is a fossule. Unsupported... generally speaking when creating an instance of a managed SQL server programmatically, e.g. through WDS or by Terraform in any cloud enviroment I thought adding a built-in group is the most conveniant way to administer it, since SQL authentication is not easy to handle in any IAM system.

_{Thursday, August 1, 2019 7:06 AM}

Hi AI Hasoob,

As mentioned by Tom, the local administrators group is no longer granted rights to SQL Server as a default, since SQL 2008. Would you please try to add the Local Administrators Group to sql server by the following code:

USE [master] GO CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=[master] GO ALTER SERVER ROLE [sysadmin] ADD MEMBER [BUILTIN\Administrators] GO

For more details, please refer to https://dba.stackexchange.com/questions/155353/sysadmin-rights-to-local-administrator

Best regards,

Dedmon Dai

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

_{Thursday, August 1, 2019 7:34 AM}

Edit:

your SQL statement did NOT fix that, it just adds the group to Sql server in versions where this cannot be performed by the SSMS GUI. Newer versions can do that like the integrated SSMS of SQL server 2014 or the SSMS 17.9 which is the current release.

I will digg into that stackexchange topic a little bit deeper

_{Thursday, August 1, 2019 8:36 AM}

the statement just does add the group as login and grants the sysadm to it, maybe this was intended for SQL server versions where the SSMS couldn't do that in the GUI. But it doens't fix the underlying problem with the inexisting right inheritance by the group, just cheked that on SQL 2014. Login is not possilbe because not even the public role is inherited and that is the requirement to grant a login.

And if I add the <domain>\sqladmin account and grant "public" to it the group role sysadm is NOT inherited from it's group membership. To fix this something else has to be changed.

_{Thursday, August 1, 2019 8:47 AM}

Dedmond brought me on the wrong track... the stackexcchange article is misleading - it is just replacement for a GUI action. Nothing more. Nothing less. Maybe for people who don't have the SSMS, because the article was for SQL 2014 and it's management studio can do that action in the GUI, if installed. If not sqlcmd client is needed to perform that...

I am afraid that took me in a circle where I just performed 360° and I am stil at the beginning.

Question is still: how do I activate AD group evaluation to allow/disallow logins for group memvers WITHOUT creating a login for each single AD account?

_{Thursday, August 1, 2019 9:43 AM}

Question is still: how do I activate AD group evaluation to allow/disallow logins for group memvers WITHOUT creating a login for each single AD account?

You add the AD group as a login to SQL Server.

I read your post last night, but I was not really sure what you did.

Could you post a screen shot of the output from

SELECT * FROM sys.server_principals WHERE type IN ('U', 'G')

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

_{Thursday, August 1, 2019 11:42 AM}

I executed the sQL script to create the login and assign sysadm to it with success.

But it did nothing different than creating such a login through the GUI.

your script shows that what I have expected:

When I try to log in with manamgement Studio using the windows authentication with the account win16\sqladmin having membership of domain admins I get the errir message

cannot connect to <server name> , login failed for user win16\sqladmin SQL server error 18456

SQL server logs this event

_{Thursday, August 1, 2019 9:10 PM}

In this context, is WIN16 a domain or a machine? I assume it is a domain, but just checking.

The message about "infrastructure error" usually mean one of two things:

1) Lack of CONNECT permission on the server or on the endpoint. (Which should be an issue if the group is sysadmin.)

2) SID mismatch between SQL Server and the AD.

I would try dropping both WIN16\Domain Admins and BUILTIN\ADMINISTRATORS and then run

CREATE LOGIN [WIN16\Domain Admins] FROM WNDOWS
ALTER SERVER ROLE sysadmin ADD MEMBER [WIN16\Domain Admins]

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

_{Friday, August 2, 2019 8:21 AM}

I don't know what is going on, but it get the feeling that there is some issue in your Active Directory (which is none of my competence). Since there are several Microsoft components involved, it may be better to open a case with Microsoft, even if that is far more expensive than getting help in a forum.

I still would like to commment on this:

So all of these magic SQL statements are obviously trusted by you people without really knowing what they do... another 360° on my circle.

I suggest SQL statements because that is what I think you should use. And, well, it is the only thing you can use. You may use SSMS on top of it, but all SSMS does is to produce SQL statements. That is after all the only way to communicate with SQL Server.

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

_{Friday, August 2, 2019 9:19 AM}

let's say with 21 years hands-on experience on all SQL server versions between 4.3 and the previews of 2019 it took me "only" two hours to find a solution.

_{Friday, August 2, 2019 9:46 PM}

let's say with 21 years hands-on experience on all SQL server versions between 4.3 and the previews of 2019 it took me "only" two hours to find a solution.

And the solution was?

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

_{Monday, August 5, 2019 7:00 AM}

"run as Administrator" with the SSMS / SQLCMD or creating a compatibility setting in the lnk file to require administrator privilegues.... becasue obviously the SSMS can't.

_{Monday, August 5, 2019 11:13 AM}

Yes, SSMS does not run as Administrator by default. If you only get the Admin role when you request it, then you need to run SSMS as admin. This is nothing particular for SQL Server /SSMS, but generally how it is set up in Windows for you.

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

_{Monday, August 5, 2019 11:43 AM}

It is particular for MS SQL server that the elevation is not claimed although necessary thereby leaving admins to sort out what exactly is the meaning of the login error 18456.14.11 in SQL server's log file.

_{Monday, August 5, 2019 9:37 PM}

It is particular for MS SQL server that the elevation is not claimed although necessary thereby leaving admins to sort out what exactly is the meaning of the login error 18456.14.11 in SQL server's log file.

Not sure what you mean here. Elevation to admin on Windows level is not needed to be sysadmin in SQL Server in the general case. It is your case, because you have tied it to domain admins. That may be an arrangement which fits in your shop, but it is of no interest in a shop where the Windows team administers Windows and the DBA administers SQL Server.

The lack of a UAC prompt when you started SSMS should have been a clue to the Windows part of you.

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

_{Tuesday, August 6, 2019 7:20 AM}

Not sure what you mean here. Elevation to admin on Windows level is not needed to be sysadmin in SQL Server in the general case. It is your case, because you have tied it to domain admins.

yep, but you didn't tell me. Instead leading me on useless sideways. Is that how you do your work? then I won't hire you and won't propose you for other problems where "we" might need external assistance.

I have posted this in a different forum in a difrernt language, and imagine:

6:35 hi people I have a problem with ssms and admin group in Windows.....

7:01 look here there is an article that says check for elevation

7:04 omg that was it , now it's working. thanks

_{Tuesday, August 6, 2019 9:24 PM}

When I work I make the effort to get correct input and preferrably access to the site.

When I answer questions here it is in my free time at my leisure. And I try help where I can. But my effort is certainly less than when I charge a customer for my work.

I'm sorry that I did not make the connection you needed elevated permissions in SSMS. To my defese I like to raise that I am not a Windows expert. But in the light of this, the error message of "infrastructure error" makes sense. I have learnt something new.

Finally, I like to remind you that in these forums you never get less help than you pay for.

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

Connect, code, and grow

Share via

Question

All replies (19)

Share via

builtin\administrators is not resolved

Question

All replies (19)

Additional resources