February 23, 2005 at 10:05 am
How would I go about denying access to Builtin\Administrators instead of Removing the login. I created a Windows user and gave them access in Enterprise Manager as a sysadmin. I changed the start up accounts to use that new Windows account. That user needs to belong to the Administrators group. What steps can I follow to deny the access but still allow the SQL Server agent to start up and run using this new Windows account?
Thanks in advance!!
February 23, 2005 at 10:25 am
Here's how I'd do what I think you're trying to do:
-- Create Logins for the SQL Server and SQL Agent services. (Could either be be one account for both, or separate accounts for each.)
-- Add these accounts as SQL Logins and grant them SysAdmin rights
-- Configure the services to use these accounts. (Use Enterprise Manager to do this; it will automatically set all the necessary server and registry settings.)
-- Stop and restart the services, to be sure everything's working.
With that done, the services no longer gain access to SQL Server through Builtin/Administrators, so it can be dropped. You could "DENY ACCESS" to Builtin/Administrators, but that may mess up the access rights of all users who are members of that group--and perhaps groups that should have access.
Philip
February 23, 2005 at 10:43 am
Check this article out previously posted:
http://www.sqlservercentral.com/columnists/bkelley/sqlserversecuritysecurityadmins.asp
...hope that helps
February 23, 2005 at 12:49 pm
Thanks for all your input - It's strange that when I deny access to the Builtin\Admins then I can't start the SQL Server Agent. Probably because the user that starts the services is in the Administrator group. However, the SQL Server services (uses same account as the Agent) starts up fine. If I change the connection on the SQL Server Agent to use SQL Server authentication then I can start up the agents fine. I really want to avoid removing the access totally.
Thanks!
February 24, 2005 at 2:37 am
I have had trouble with this as well. If the Agent fails to start, but the main Service runs OK, you might need to check the permissions for the login. If I remember correctly, the permissions should NOT be "Via Group Membership" as this has an effect upon the Agent. If you should change the permissions for the Login to be "Grant Access"; this might solve your problem.
Regards
February 24, 2005 at 7:16 am
Don't forget that you need to add 'NT Authority\System' as an SA also otherwise certain functions that the server is performing will not work with builtin administrators removed. Cluster perform poorly (as in not all) if you don't add this back and full-text indexing is another example.
February 24, 2005 at 7:41 am
Ensure you explicitly add the account that SQL Server is running under and give it SA privileges. Do the same for SQL Server Agent. BTW, this also should be done if you choose to run SQL Server and/or SQL Server Agent with less than administrative rights on the server (always something to consider from the security perspective).
K. Brian Kelley
@kbriankelley
February 24, 2005 at 7:43 am
NT Authority\System should only be needed if you're running SQL Server or SQL Server Agent under the local System account or using something like Full-Text Indexing (which should be configured to run under the local System Account and also needs sysadmin membership).
As far as a cluster is concerned, the Cluster service account MUST be added explicitly and granted membership in the sysadmin role before removing BUILTIN\Administrators.
K. Brian Kelley
@kbriankelley
February 24, 2005 at 7:48 am
Yes, I know the cluster account must be added, but it doesn't work to well (from my experience) without the nt authority\system. Maybe there was another misconfiguration causing this. Maybe it was becuase full text indexing wouldn't start and this was set to cause a group failure.
But if you have trouble with things not runnign properly after you remove local administrators, you can always try adding NT Authority\System and test it.
February 24, 2005 at 7:59 am
It was probably because of FTI. The cluster service and SQL Server themselves do not come in through the local System account. One option here may be to set FTI's failure not to cause the cluster group failure and leave it off-line, especially if you aren't using FTI.
K. Brian Kelley
@kbriankelley
February 24, 2005 at 10:02 am
Here's a script we implement as a site standard during initial server build:
use master
go
exec sp_grantlogin 'DOMAIN\sv-sqlserver' --> each of these domain user accounts
exec sp_grantlogin 'DOMAIN\sv-sqlagent' --> are in tyhe local administrators group
exec sp_grantlogin 'DOMAIN\sqladmin' --> via a domain group on all sql servers
go
checkpoint
go
exec sp_addsrvrolemember @loginame='DOMAIN\sv-sqlserver', @rolename='sysadmin'
exec sp_addsrvrolemember @loginame='DOMAIN\sv-sqlagent', @rolename='sysadmin'
exec sp_addsrvrolemember @loginame='DOMAIN\sqladmin', @rolename='sysadmin'
go
checkpoint
go
exec sp_revokelogin 'BUILTIN\Administrators'
go
checkpoint
go
As you've probably guessed sv-sqlserver is the SQL Server service account and sv-sql-sqlagent is used as the SQL Agent service. These accounts are not permitted interactive logon for security.
We do not use Full text search, nor distributed transactions. However we do perform linked server queries. During our initial server build this script is execfuted after applying service packs/hot fixes and just before modifrications for the service accounts. I've been using this script since v7.0 came out.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
February 24, 2005 at 10:46 am
As Brian said, You need to assign the SQL Server Agent account SA privileges. Actually, SQL Services does not need SA privileges to start and only SQL sever Agent priviliges need SA privileges.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply