Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL2008 Unable to Add Built-In\Administrators Expand / Collapse
Author
Message
Posted Wednesday, October 28, 2009 6:49 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, January 13, 2014 5:42 AM
Points: 226, Visits: 903
I installed a new SQL2008 instance, the only one, on a VM Win2008 server for development. For reasons beyond my knowledge or control, they set this server up on a segment behind a firewall and put a copy of a DC from the production domain in there with it.
The Win2008 server was part of the domain "copy" before the SQL2008 installation began. When I installed SQL2008 I put the user I was logged in as, and the local admins group into the sysadmins group, as part of the setup process.
Once the install was complete, I tested and successfully logged in to the SQL Server using SSMS and the login I used to install SQL Server.
A new login was created by someone else and made a part of the Domain Admins group for the domain this server resides in. This new login is unable to connect to SQL Server when logged into the local machine and using SSMS. I have been through and found that the account is in the domain admins group, and the domains admins group is in the local administrators group on the SQL Server, and the local administrators group was set up as a SYSAdmin role in SQL 2008. After this verification I removed the local Administrators group from SQL Server and attempted to add it back in. SQL Server was unable to find it, giving me the error:
Windows NT user or group 'Server4\Administrators' not found. Check the name again. (Microsoft SQL Server, Error: 15401)

I tried rebooting the box to see if that would help, but no luck. I successfully added the "Domain Admins" group to the SQL 2008 server as Sysadmins and the user account in question can now connect.
What I am trying to figure out is why the local Administrators group cannot be added to SQL 2008. I know that it is not added by default on installation as an added security feature (finally), but I should be able to add it in later if I want to.

Anyone else run into this behavior?

Thanks,
Chris


Learning something new on every visit to SSC. Hoping to pass it on to someone else.
Post #809861
Posted Wednesday, October 28, 2009 7:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:40 PM
Points: 12,880, Visits: 31,800
i have this snippet saved from SQL 2000, which is supposed to add back Builtin\Administrators if it was removed;

i ran it on my SQL2008 without errors, but that doesn't technically prove anything.
does this help at all?
exec sp_grantlogin N'NT Authority\System'
exec sp_defaultdb N'NT Authority\System', N'master'
exec sp_defaultlanguage N'NT Authority\System','us_english'
exec sp_addsrvrolemember N'NT Authority\System', sysadmin
USE master
go
EXEC sp_grantlogin 'BUILTIN\Administrators'
go
EXEC sp_defaultdb 'BUILTIN\Administrators', 'master'
go
EXEC sp_defaultlanguage 'BUILTIN\Administrators', 'us_english'
go
EXEC sp_addsrvrolemember 'BUILTIN\Administrators', 'sysadmin'
go
IF EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname='BUILTIN\Administrators')
PRINT '<<< CREATED LOGIN BUILTIN\Administrators >>>'
ELSE
PRINT '<<< FAILED CREATING LOGIN BUILTIN\Administrators >>>'
go



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #809892
Posted Thursday, October 29, 2009 8:41 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, January 13, 2014 5:42 AM
Points: 226, Visits: 903
I am trying to get another server for working out this problem set up now. The original server where this problem exists is needed for an important project so I need to lay off that box right now. I a should be able to get a duplicate of that box, as it is a VM, less the SID, name and IP address, that I can test with.

Thanks,
Chris


Learning something new on every visit to SSC. Hoping to pass it on to someone else.
Post #810824
Posted Friday, October 30, 2009 5:00 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 2:50 AM
Points: 2,854, Visits: 3,174
Security has changed in Windows 2008 and above. The problem you are seeing is not caused by SQL Server, but be Windows 2008.

You used to be able to add a domain group to a local group, and have the domain group members inherit the rights of the local group. This has changed with W2008. If you look at the W2008 documentation you will see that many of the local groups are now shown as deprecated, and already the security works in a different way for these groups.

From what I can see, any login that has special rights by way of their local group membership now has two security contexts: a normal User context and a privileged context. Everything that the login does is run under the normal user context unless they specifically request the privileged context (i.e. Run as Administrator).

One impact of this is that if you add Domain Admins to the local admin group, when they connect to SQL Server their security context is no longer 'Local Administrators'.

One way I have found to overcome this issue is to add the relevant groups directly into SQL Server, and not rely on inherited permissions from the local server groups.


Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 29 May 2014: now over 29,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #811459
Posted Friday, October 30, 2009 11:40 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, January 13, 2014 5:42 AM
Points: 226, Visits: 903
Ed, I do believe you are on to something, but I experienced the same problem with adding "Domain Admins" to the server with the SysAdmin role, the next day. I mean that my original band-aid stopped working the next day. The new band-aid I put on it, until I can fully investigate and understand the problem/resolution, was to create a new local group on Win2008 called "SQLAdmins", add the "Domain Admins" to it then add that local group to the SQL Server with SysAdmin role.
I am hoping I will get the copy of the server soon so I can mess with it a bit and try different thing to fully understand what's happening.
Some of my other research on the Internet has suggested it's UAC (user access control) causing the problem, and this would be closely related to what you describe.

Thanks,
Chris


Learning something new on every visit to SSC. Hoping to pass it on to someone else.
Post #811764
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse