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

SQL Server 2005 security Expand / Collapse
Author
Message
Posted Monday, February 1, 2010 3:04 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 4:09 AM
Points: 761, Visits: 1,083
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.

Post #857013
Posted Monday, February 1, 2010 3:13 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, June 18, 2013 6:35 AM
Points: 65, Visits: 312
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.
Post #857016
Posted Monday, February 1, 2010 3:50 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 4:09 AM
Points: 761, Visits: 1,083
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
Post #857034
Posted Monday, February 1, 2010 7:26 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, June 18, 2013 6:35 AM
Points: 65, Visits: 312
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).



Post #857170
Posted Monday, February 1, 2010 7:51 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 4:09 AM
Points: 761, Visits: 1,083
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.
Post #857198
Posted Monday, February 1, 2010 8:42 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, June 18, 2013 6:35 AM
Points: 65, Visits: 312
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.
Post #857238
Posted Monday, February 1, 2010 8:48 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 4:09 AM
Points: 761, Visits: 1,083
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
Post #857245
Posted Monday, February 1, 2010 9:16 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, June 18, 2013 6:35 AM
Points: 65, Visits: 312
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.

Post #857280
Posted Wednesday, February 3, 2010 7:53 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:48 AM
Points: 20,857, Visits: 32,877
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #858695
Posted Wednesday, July 17, 2013 8:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 17, 2013 8:39 PM
Points: 1, Visits: 0
river1 (2/1/2010)
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.


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.
Post #1474863
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse