SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server 2005 security


SQL Server 2005 security

Author
Message
river1
river1
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4598 Visits: 1366
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.
Shriji
Shriji
Old Hand
Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)

Group: General Forum Members
Points: 313 Visits: 318
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.
river1
river1
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4598 Visits: 1366
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
Shriji
Shriji
Old Hand
Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)

Group: General Forum Members
Points: 313 Visits: 318
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).
river1
river1
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4598 Visits: 1366
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.
Shriji
Shriji
Old Hand
Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)

Group: General Forum Members
Points: 313 Visits: 318
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.
river1
river1
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4598 Visits: 1366
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
Shriji
Shriji
Old Hand
Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)

Group: General Forum Members
Points: 313 Visits: 318
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.
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)

Group: General Forum Members
Points: 98634 Visits: 38996
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.

Cool
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)
chrisfirst
chrisfirst
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search