October 28, 2009 at 6:49 am
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.
October 28, 2009 at 7:18 am
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
October 29, 2009 at 8:41 am
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.
October 30, 2009 at 5:00 am
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: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
October 30, 2009 at 11:40 am
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply