February 1, 2010 at 3:04 am
Hi Gurus
I have a problem.
I need that the Domain Admins from a client company can not access the SQL Server 2005 that we have installed there.
As domain Admins they are local administrators of the OS (w2k3) on the box where SQL Server is installed.
If i remove the group "builtin\administrators" from the SQL Server 2005 security, will they lose the access to it?
In fact, what i want is that no windows account as access to SQL Server. Only SQL Logins can have access to It, how can i achieve this?
I have a app that connects to SQL Server through SQL Server authentication, i want that only this app can connect to the SQL Server 2005 instance. No windows account can connect to it, even if they are domain admins.
February 1, 2010 at 3:13 am
Yes you are right you have to remove Built-in Admin account from SQL Server. In fact Microsoft advises to remove it to tighten the security. A few months back we opened a ticket with MS and they guided us how to do that.
BUT BEFORE YOU DO THAT......
Make sure you have SA account password and fully tested. Or any other account that have Sys Admin rights. If you as a DBA accesses the databases using your domain account, make sure you make your account or the group (if you are using a group account) has sys admin rights to the server.
As always, test these changes in dev enviroment before you do that in prod.
February 1, 2010 at 3:50 am
Thank you very mutch for reply.
What i really want is that NO windows account (domain admins or domain users) can access the SQL Server 2005.
CAn they have access to the OS (w2k3) where the SQL Server 2005 is installed but not have acccess to SQL Server?
1) If i delete the builtin\administrators and all windows accounts from the security account will i achieve what i want?
2) What about the SQL Server service and the SQL Server Agent service, this two services i want they they still run under a domain account. Does this account need to have previleges inside the SQL Server?
Thank you
February 1, 2010 at 7:26 am
river1 (2/1/2010)
Thank you very mutch for reply.What i really want is that NO windows account (domain admins or domain users) can access the SQL Server 2005.
CAn they have access to the OS (w2k3) where the SQL Server 2005 is installed but not have acccess to SQL Server?
1) If i delete the builtin\administrators and all windows accounts from the security account will i achieve what i want?
2) What about the SQL Server service and the SQL Server Agent service, this two services i want they they still run under a domain account. Does this account need to have previleges inside the SQL Server?
Thank you
"CAn they have access to the OS (w2k3) where the SQL Server 2005 is installed but not have acccess to SQL Server?"
Yes, by deleting builtin\administrators, they can still discharge their duties regarding server maintanance but they cannot access SQL Server.
"1)"
Ans - Yes. But, as you said, all those Windows domain accounts that have direct permission to the SQL Serve will still be able to access the database. If you dont want them you have to specifically delete them from them from the logins list.
"2)"
Ans - If you have give SQL Server Service and SQL Server Agent Service accounts Sys admin permissions at SQL Server level.
To summerize, before you delete builtin\administrators account, you need to do following:
1) Give sys admin rights to SQL Server Service and SQL Server Agent Service accounts at SQL Server level.
2) Give your self sys admin rights so that you can peform DBA functions.
3) make sure SA account is working. (Test it).
February 1, 2010 at 7:51 am
Master, thank you for reply.
I undesrtud almost every thing, but i have a dought about the SQL Server service and the SQL Server agent service account.
As i told. I don't want that ANY windows login (domin users or domain admins) can access SQL Server. This, you told me how do i achieve, but suppose that i create a user account in the domain controller named SQL1 this account will be the account with wich the SQL Server service and the Agent service will run.
Do i need to give specific permissions on SQL to this account? why?
This user is a domain admin , so it as all the previlegies inside the OS (w2k3) of the box where SQL Server is installed and on the network, why does it need to have permissions inside the SQL Server? does it make some tasks to in the databases?
If it's this way, than i cannot CUT the permissions to ALL windows account in SQL Server, i still have to give permissions to this account (SQL1).
Is really it necessary?
Thank you.
February 1, 2010 at 8:42 am
river1 (2/1/2010)
Master, thank you for reply.I undesrtud almost every thing, but i have a dought about the SQL Server service and the SQL Server agent service account.
As i told. I don't want that ANY windows login (domin users or domain admins) can access SQL Server. This, you told me how do i achieve, but suppose that i create a user account in the domain controller named SQL1 this account will be the account with wich the SQL Server service and the Agent service will run.
Do i need to give specific permissions on SQL to this account? why?
This user is a domain admin , so it as all the previlegies inside the OS (w2k3) of the box where SQL Server is installed and on the network, why does it need to have permissions inside the SQL Server? does it make some tasks to in the databases?
If it's this way, than i cannot CUT the permissions to ALL windows account in SQL Server, i still have to give permissions to this account (SQL1).
Is really it necessary?
Thank you.
Honestly I have not tried this. But I think you dont need to give permission to this account. As long as you have some way to enter SQL Server with sys admin permissions, you can try this out.
We have multiple servers and sql server instance spread across multiple domains. As per our experience, giving these account sys admin previlages resolved some of the issues pertaining to distributed processing.
I will find out more and get back to you.
February 1, 2010 at 8:48 am
Ok, thank you very much Master.
I will trie to know more about that too, and if i discover something i will talk to you.
tks,
Pedro
February 1, 2010 at 9:16 am
river1 (2/1/2010)
Ok, thank you very much Master.I will trie to know more about that too, and if i discover something i will talk to you.
tks,
Pedro
This is what MS suggested below. You MUST have the agent account as sys admin role...
Main concern in removing BUILTIN\Administrators login
--------------------
-If you're in Windows Authentication mode only, the biggest danger in removing the BUILTIN\Administrators login is that you do not insure that there is another Windows account already in existance that has sysadmin role on the SQL Server, or fail to create a new login for a Windows account that has sysadmin role before logging out. If you remove BUILTIN\Administrators login and fail to have another login with sysadmin role, you will no longer be able to administrate your SQL Server.
-If you are in mixed mode and have the sa account enabled, then you will still be able to administrate the SQL server (add/remove logins, etc) under the sa login. You must verify that you can login as the sa account prior to removal of the BUILTIN\Administrators login.
Other considerations
--------------------
-You must add a login account on SQL Server for the domain user account that the SQL Server Agent service is running under. The login account should be added to the sysadmin fixed server role.
-If Full-Text is enabled, add a login account for the Local System account using sp_grantlogin [NT Authority\System] and ensure that the login account is a member of the sysadmin fixed server role. This also applies if you are running the SQL Services as Local System
For cluster:
-Cluster administrator is a member of Administrators group. If this account (the Cluster service LogOnAs account) is not specifically added in SQL Server as a login, then the cluster administrator is not able to communicate with SQL Server and the SQL Services will not start. http://support.microsoft.com/?id=291255
Microsoft does recommend that you remove the BUILTIN\Administrators login.
February 3, 2010 at 7:53 am
If the only logins to your SQL Server instance that you setup are SQL Server logins, then windows accounts won't be able to connect to SQL Server.
July 17, 2013 at 8:58 pm
river1 (2/1/2010)
Hi GurusI have a problem.
I need that the Domain Admins from a client company can not access the SQL Server 2005 that we have installed there.
As domain Admins they are local administrators of the OS (w2k3) on the box where SQL Server is installed.
It is not possible to both have Domain Admins with access to the OS and keep those same users out of the SQL Database.
Having access to the OS allows you to do almost anything. Including creating a SQL SA acct which can then be used to log in and query to your hearts delight. (MSDN recommends this in the case the other posters warned about, getting locked out). http://msdn.microsoft.com/en-us/library/dd207004.aspx
I believe the best you can do is make it auditable by only granting OS permissions to functional accounts that must be checked out by the admins when they need them.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply