March 26, 2009 at 5:12 pm
Hi friends,
I've just taken over an existing SQL Server 2005 based environment. I've been talking to the development team of ten and found a few...erm...local peculiarities, such as
* There is no "DBA" (or, everyone's a DBA...)
* Everyone has the sa password, and the sa password is the same between environments
* Staging is used as QA by internal staff, and UAT by external users
* Only SQL authentication is used, and the SQL usernames used by the applications are assigned to the db_owner role
These are just the things I've found by talking to people, so I'm thinking there is probably a more formal process that people might use to find out about an environment.
So I was wondering, does anyone have a due diligence checklist they use when they walk into an existing environment?
Cheers,
Steve
March 26, 2009 at 5:28 pm
Two words, Management Buy-In.
To change all that you are going to need to convince some senior people in your organisation.
March 26, 2009 at 5:51 pm
Management can change that for you but you could affect productitivity so you first task is to create a copy of the database and make relevant changes and create a structured security plan. You can take this to management and recommend developer edition for the existing developers so their work is moved to their desktop and not your server. When everybody is up and running in their boxes change test and QA also but not before approval by management of your security plan.
The reason total permissions change could affect development time line and you will be blamed and you are new.
Kind regards,
Gift Peddie
March 26, 2009 at 6:48 pm
I agree with the need to get senior management buy-in. I recently took over an environment with some of the issues you mentioned and more. I did a detailed analysis of the environment and put together this checklist.
Phase I
- Clean up SQLAdmins* domain groups (all environments)
- Revoke all System Administrator access (all environments)
- Anyone requiring access to any of the high privileged SQL Server domain groups will need DBA and senior management approval
- All access requests via domain groups using access request forms only
- All access granted to the database via domain groups and database roles only
- Remove MSDE / SQL Express from all laptops
- Standard Baseline Configuration on all Development and QA servers
- Proper separation of Dev and QA environments
Phase II
- Drop BuiltIn\Administrator
- Revoke Guest access
- Revoke privileges from public role to application objects and move those to database roles
- Data Redaction in non-Production systems
- Perform an Entitlement Review of the database environment
Phase III
- Standard Naming Conventions for all objects
- Application Job Categorization
- Disable remote access
- Disable cross database ownership chaining
- Disable Named Pipes
- Change Default Port Number
- Change database compatibility mode to 90 – This is for databases on 2005 running with compatibility mode 2000
- Application Classification / Inventory along with SLA criticality and data sensitivity information for each of these
- Review and eliminate use of Linked Servers
Just a word of advice, even though you get Sr. Management support, you still will need to get the people on the ground on your side since you are still going to be dealing with them.
March 26, 2009 at 10:07 pm
Remove MSDE / SQL Express from all laptops
VS2005/8 installs these if you are not providing Developer edition taking all these away will cost a lot of development time and some one have to pay for it.
Revoke Guest access
The guest account is only revoked per database not the server because if you do SQL Server shut down.
http://msdn.microsoft.com/en-us/library/ms190928.aspx
Kind regards,
Gift Peddie
March 27, 2009 at 5:38 am
This list is definitely NOT a one size fits all solution. This may work in my environment but may not in yours. Neither is this a comprehensive list.
As you may have noticed the list also includes the phases I have grouped these changes. These may or may not be applicable to your environment.
I suggest taking this as a starting point and adding / removing from this as necessary and managing items to best fit your organizational needs.
VS2005/8 installs these if you are not providing Developer edition taking all these away will cost a lot of development time and some one have to pay for it.
In an environment where folks that have access to Production (and shouldn't) data and have MSDE / Express Editions end up restoring Production data, which could be confidential, on laptops. This poses a great security risk. One could argue that it adds development time, but one of the aspects for a DBA is managing a balance between risk and reward. There are work arounds to this however that would require controls around access to both Production databases and their backups.
The guest account is only revoked per database not the server because if you do SQL Server shut down.
In SQL 2000 you could drop guest access from all but the master and tempdb databases. However 2005 upwards, you can only revoke guest connect privileges as the article you point to suggests.
http://msdn.microsoft.com/en-us/library/aa905195(SQL.80).aspx
March 27, 2009 at 5:48 am
In an environment where folks that have access to Production (and shouldn't) data and have MSDE / Express Editions end up restoring Production data, which could be confidential, on laptops. This poses a great security risk. One could argue that it adds development time, but one of the aspects for a DBA is managing a balance between risk and reward. There are work arounds to this however that would require controls around access to both Production databases and their backups.
That is actually not true if some one did their job the only data you need for development is sample data and both test and UAT are usually run by data team or management not developers. The skilled teams I worked in live data is never needed but time line is very important if your rules cost a lot of money depending on the business you will be replaced with one who could help the developers do their work.
Kind regards,
Gift Peddie
March 27, 2009 at 6:05 am
If everyone had a copy of the database with 'sample data', how would you maintain database design across all your developers systems? I have always believed -- SQL engines on desktops = unmanageable environment & security risks.
Also - say someone did lose their notebook.. The database design would be be readily available (including sp defintions, etc) which would open up a whole new attack strategy on your internal applications/production databases, etc.
March 27, 2009 at 6:11 am
The notebooks are not someone property they are company property actually mobile offices of all developers sample data is in the development server not on developers desktop and getting everything in synch is what VS2005/8 database developer edition does and Redgate data comapre does.
Kind regards,
Gift Peddie
March 27, 2009 at 6:16 am
Yea, I see how that could work well for a very large software development team. I'd never let those notebooks out of the office though 🙂
March 27, 2009 at 6:19 am
Then you will never work for a software engineering company because the developers work pay your salary.
Kind regards,
Gift Peddie
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply