January 8, 2010 at 11:41 am
I am looking for minimum NTFS security requirements / best practice on an SQL 2005 server with multiple instances. The Windows 2003 AD accounts have remote server login access and MUST be able to completely manage / administer their own instance from within SQL Manager and Windows but MUST NOT be able to view other instances or manipulate the O/S in any way that could possibly adversely affect server functionality or any of the other instances.
I'm not sure if restricting the default instance, O/S drive (whole or part), or registry will cause them problems managing/administering their own instance.
Advice is appreciated. Thanks!
January 13, 2010 at 11:01 pm
What kind of domain accounts? Are they domain admin or user accounts? What local groups on the server are the user added to on this server?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 27, 2011 at 11:10 am
It looks like they just have membership to Domain Users and Remote Desktop Users.
September 27, 2011 at 11:50 am
Hm, so we are talking about a multi-tenant situation. And they can RDP into the box. I've got to say I wouldn't allow the RDP, it is probably not needed and is harder to lock down, you might be able to restrict applications for them based on AD policy. Is xp_cmdshell enabled? I wouldn't allow that at all and I would audit it OFTEN. Each SQL Instance shold probably have its own AD Login and only its directories are given FULL access. It should only be a member of the users group locally. You might look at NTFS quotas to keep them from blowing out your space.
Also keep in mind that all the instances share memory and CPU so you need to set max memory and audit it often, if they can fully administer the SQL they can change that. You might consider looking at processor affinity where you can limit an instance to specific cores on the CPU.
Also keep in mind there are practical limit for the number of servers you can have on a piece of hardware that is substantially lower than the limits MS says. You might be able to have 50 instances per MS, but your hardware is unlikely to support 1/5 that in normal cases.
CEWII
September 27, 2011 at 1:50 pm
RDP is only given to our vendor for SQL support but we have 2 vendors. Everything is locked down pretty tight with AD policies, VPN, etc.
I separated and restricted each vendor's installation and data using instances but my concern was how much to restrict acess to common folders that they both may need access to (i.e. default instance) so that they are still able to support us while attempting to reduce the risk that they can mess each other up.
I thought to give them only read, list, execute & create folder access to the OS drive (except their own profile) and no access outside their instance on the SQL drive.
There are no issues with performance and resources.
September 27, 2011 at 2:38 pm
So it sounds like you have 3 instances. 1 for each and a common instance. It also sounds like you have the individual instances locked down generally appropriately. The common instance is a problem, and realistically you can't provide both with the same high level access and prevent them from stomping each other. At the OS level, I would give them NO additional rights over a user. If they requested additional rights I would research them to see the effects. I don't know that I would change anything on the OS drive, as a user you can't install software and it is difficult to damage the system.
CEWII
September 28, 2011 at 3:38 am
The security permissions documented in the FineBuild reference manual will cover the OS side of your requirements. One of the principals used in FineBuild is that nothing should need local Administrator access apart from installing software. The permissions are not tailored for instance isolation, but they could be a good base to work from.
If you are looking at a multi-tenanted server, then I think it will be very difficult to get complete instance isolation that would protect you from legal action if one tenant thought their data had been accessed by another. The standard best practice for this situation is to isolate each tenant in their own instance of Windows.
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
September 28, 2011 at 7:23 am
Thank you all for your time. Looks like we will just continue to audit until we are moved to VM.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply