February 3, 2010 at 8:54 am
Also, a ref: Setting Up Windows Service Accounts.
February 3, 2010 at 9:02 am
I'm running XP (to test this).
IF client agree with taking the domain admins from the builtin\administrators group in the OS (w2k3)
Can they still manage a way (after you have removed them from the builtin\administrators) to be part again of the Builtin\administrators?
February 4, 2010 at 1:18 am
Morning
Another thing to realise , if you are running in a clustered anvironment , that if you remove builtin\Admininstrators you have to ensure that the service account that runs your windows cluster (or the geospan software if you are using geographic cluster) has to be added in as a login to the instance.It doesn't need any special rights (public is sufficient) but does have to be present.
February 4, 2010 at 1:22 am
Not using cluster, but thanks.
Do you know this?
If i remove the Domain admins from the builtin\administrators of my Operatin System can they still put their login back into the builtin\administrators group? or they lose total control of the server?
February 4, 2010 at 3:03 am
As many people have said, it is (probably) not possible to prevent someone with local administrator authority from starting SQL Server in single user mode and then working as a sysadmin.
It may be posible to potect the SQL Server srevices so that local administrators do not have the start service right for SQL Server, and also do not have the Read and execute right on sqlservr.exe. However, if the people you are worried about have domain admin rights they can change all of this if they want.
By far the best way to protect the services is to make it a disiplinary issue.
I don't think that trying to gt the contract changed to have a clause about SQL Server access is worth spending time on - it will take the lawers a long time (read: lots of money!) to get an acceptable form of wording, and anyway this level of micro-management is not what contacts are about.
You should aim to get written agreement with the client that their system admin staff will not seek or use sysadmin rights on SQL Server. If you find that someone is doing this then you have a disiplinary route to follow.
If you have management responsibility for SQL Server, then you already have an implied disiplinary safeguard against client staff using sysadmin rights, as you can argue that any use of those rights is preventing you from meeting your obligations under the contract. I am sure that client management would not want to risk the issues that come from that just because one of their technical staff is exceeding their job responsibilities.
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
February 4, 2010 at 3:09 am
I agree with that, but we are talking about african coutries witch are very complicated... more that you can imagine...
That's why i'm trying to protect my servers.
Tell me one think please, if i can remove the domain admins from the OS (w2k3) of the server can they still connect to the server again and be part of builtin\administrators on this server?
Thank you
February 4, 2010 at 3:13 am
There's nothing special about a domain admin as far as a member server is concerned--they just happen to be in the local Administrators group by default. If you were to remove Domain Admins from that group then they wouldn't have admin access to the local machine.
Obviously that wouldn't apply to a domain controller, but hopefully none of your SQL servers are installed on DCs!
February 4, 2010 at 4:52 am
Ok, then i will talk to the client i think they will accept this.
If they accept i will no longer have to worry about the domain admins can connect in single user mode and add the builin\administrators to SQL Server again.
This way i can garanty that only SQL Server logins can connect to SQL Server.
What about the other two question that i was asking master?
I want to delete this 3 groups from the SQL Server 2005 instance.
NWSRV2\SQLServer2005MSFTEUser$NWSRV2$DESENVOLVIMENTO
NWSRV2\SQLServer2005MSSQLUser$NWSRV2$DESENVOLVIMENTO
NWSRV2\SQLServer2005SQLAgentUser$NWSRV2$DESENVOLVIMENTO
Under the security tab of the SQL Server Instance i want to let only the following logins , with the following permissions:
sa - sysadmin
NT AUTHORITY\SYSTEM - sysadmin
NT AUTHORITY\Service - sysadmin
The SA account is to connect to SQL Server as sysadmin (i will then create my SQL Server login with sysadmin previleges)
The NT AUTHORITY\Service account is to run the SQL Server Agent service.
The SQL Server Service will run with a Domain account that as NO permissions inside my SQL Server.
AS you can see there, i have deleted the builtin\adminstrators and the other groups that are created by default when SQL Server is installed.
We don't need to execute jobs or maintenance plans out of the box, that's why we use the NT AUTHORITY\Service.
1) Do you see any problems that can be caused because of this configuration?
Will i have any problems because of the use of a domain account that as no previlegies inside SQL Server? i think that there's nothing that the service of the SQL Server can't do because of this.
What i'm traing to achieve is that only SQL Server logins can connect to SQL Server. No windows logins can connect to it, not even the Domain admins...
Do you think that i will be limited in any form because i removed those groups from SQL Server???
February 4, 2010 at 8:05 am
First, no one here is named master. I realize that might be confusing, but everyone posting has a separate name. It's a little rude not to refer to someone by their name.
Second, those groups are setup with certain rights for separation of accounts and better security. If you don't add users to them, they won't function, but I wouldn't go removing them. It doesn't make your system more secure.
As far as removing domain admins from the local admin group. That should provide you with some security from the domain. I don't think anything will break, but I'm not sure if any dependencies the server may have. If it's already in the domain, I think you will be fine there.
I believe that the service account needs rights inside SQL Server to perform operations. It is the login that logs into Windows and then executes all the instructions you send to the server. Removing it explicitly might cause issues. What you should do is select the service account using Configuration Manager and then do not adjust any permissions that are given to this account. SQL Server chooses the permission set. You can remove all permissions before you pick it, but do not change them afterward.
February 5, 2010 at 10:43 am
After reading conversation on this topic, I think the biggest issue is trust. It is something that both SQL DBAs and Server Admins need to learn about each other's boundary of work. If you are thinking of blocking Domain Admin from your server, I would ask how many Domain Admin is there in your company?
If you want to know when SQL Server got reboot without notifying you, then you should talk to your server admin person. There are other way to get notification, but it would all result in response a few minutes later than you would ever expect.
--sopheap
February 8, 2010 at 4:22 am
To add to Steve's reply, the groups you refer to are created by the SQL Server install process and are part of the standard Microsoft security configuration.
It is technically possible to remove these groups as long as you provide the equivalent file, registry and SQL rights to the accounts placed in the groups. However, there is no Microsoft documentation about removing these groups, so if you do remove them you will be outside of Microsoft support for SQL Server. IMHO it is best to leave these groups as they are.
The SQL Server service account must have sysadmin access within SQL Server in order for SQL Server to work. If you want to stop people using this account to access SQL Server, then place 'Deny log on locally' and 'Deny remote log on' rights on the account and enforce it with a Group Policy Object (GPO). But be aware that a local admin can remove these restrictions and log on using the service account if they know the password.
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
February 23, 2010 at 1:25 am
Just start your sql server in single user mode using sqlservr -c -m...this will allow you to re-add the Builtin\dminstrator account....
this is a design flaw in sql server which you could help you here 🙂
Regards
nimesh
February 24, 2010 at 10:26 am
Another way you could have accomplished what you wanted (gaining access to SQL) would have been to add your id to one of the groups
NWSRV2\SQLServer2005MSFTEUser$NWSRV2$DESENVOLVIMENTO
NWSRV2\SQLServer2005MSSQLUser$NWSRV2$DESENVOLVIMENTO
NWSRV2\SQLServer2005SQLAgentUser$NWSRV2$DESENVOLVIMENTO
And then log on. Aften gaining access set up security in the way you want , not forgetting to add your id specicically or even better some administartive group you and other DBA belong to and the remove your id from the Microsoft goup above in which you added it.
Francis
Viewing 13 posts - 31 through 42 (of 42 total)
You must be logged in to reply to this topic. Login to reply