Derrick Leggett Blog

Ramblings, grumblings, and other nonintelligible nonsense about SQL Server.

<b>Database Security Initiative</b>

Physical Database Security

·        Move a SQL Server out of DMZ (this one was ticking me off). --Completed.

·        Create new VLAN's for SQL Server and migrate servers.  --Completed.

o       Created four VLAN's to provide separation of database servers on network.

o       Separates production, development, third-party, and back office.

o       Allows separate rules governing activity and access security at a group level.

·        Implement SQL Server Firewall --Not Started

o       Will review security and firewall policy at later date.

o       If current security is not sufficient for business owners, will create database firewall.

·        Server Consolidation --In Progress.

o       Had over 30 servers, which is not manageable.  An environment that can't be managed isn't secure.

§         This is down from over 70 installations when I started.

o       Consolidating to 4 pairs.  Production, development, third-party, and back office.

·        Port/Protocol security --Completed.

o       All SQL Server use non-standard ports with TCP/IP enabled only.

o       Only approved servers are allowed access to specific DB servers on appropriate ports.

·        Named Instances --In Progress.

o       Only named instances are used in environment.

o       All environments have separate instances.

o       Sensitive business units such as accounting and HR housed on own separate instances.

·        Server Lock down --Completed.

o       Only database admin and enterprise admin group allowed in local/Administrators group.

o       No login allowed outside of approved group.

·        Monitoring of network and server performance -- Completed.

o       Look at server closely if unexpected activity levels occur.

·        Server installation policy --Completed.

o       There are to be no installations of SQL Server outside of DBA group.

o       There is to be no purchase of software with a database component without consulting the DBA Group during review.


Logical Database Security

·        Administration Lock down --Completed.

o       The sa password is insanely long and complicated.  Not used.  Only a couple people have access to it.

o       Database Admin group added in; and the BUILT IN/Administrator taken out of all SQL Servers.

o       All server level (System Administrator, etc) membership deleted with exception of Database Admin group.

·        AD Security --Completed.

o       No individual user access to the database server.

o       Everyone is a member of an AD group.

§         Group corresponds to an application or job function.

§         Group defined at time of creation with a real description.

§         A business owner approves permissions and members.

§         Corresponds to a SQL Server role.

·        DBA manages role membership and permissions.  Performs initial setup.

§         Business manages the AD membership and coordinates with helpdesk to add/delete members.

§         HR insures terminated employees are deleted.

o       SQL Server logins only allowed on third-party apps.  Well documented and follow normal procedures.

§         Once initial setup is in place, any new permission must follow corporate Access Control Process.

o       Developers’ access limited.  Only granted access where needed.

§         Have elevated permissions in development only.

§         Release and debug teams have elevated permissions for events.

§         QA Team has elevated access in QA environment.

·        Application Security --In Progress.

o       Externally addressable applications all exist on standalone servers in the DMZ.

§         Not connected to a network.

o       Can only access servers/databases necessary for applications hosted.

o       Each application will have a separate login to allow more granular control of permissions.

§         Implemented in phase two.

·        Source Control --Completed.

o       All dml changes must go through review/approval process including emergency releases.

o       All ddl/dml releases documented, in SourceSafe, and follow rollout process.

·        Data Level Security --In Progress.

o       Minimize access further by having user level access inside of each application.

o       Encrypt data where necessary.


Monitoring --In Progress.

·        Monitor all user connections.

·        Audit any connections outside of server "white list".

·        Monitor and audit all dml changes to insure there are no changes outside of process.

·        Monitor and trigger notification on all failed login attempts.


SOX has placed an unnecessarily large overhead on companies because irresponsible lawmakers did not define the act sufficiently.  Because of this, companies are spending billions of dollars scrambling to meet deadlines and goals that auditors are currently in the process of defining.  Many of these goals will be redefined because it is now in the hands of the judicial system to determine how far-reaching this act will be.


The security of data is important though.  It's many times one of the most overlooked areas of a company.  Many of the items we are now implementing should have been in place years before us.  It's unfortunate it takes a badly defined law to make it happen.


If anyone has anything to add let me know.  I'll try to update the post on a semi-regular basis, as things are added/subtracted from the overall project.




We've made a lot of progress in the SQL Server environment.  All systems are now monitored proactively using standard traces across the enterprise.  Reports are published every day, giving updates on system performance and security.  We are also beginning the final wrap-up of our SOX audit for the data systems group.  So far, it looks like we're passing with flying colors.


We still have a lot of work to do in this area though.  We need to finish the SQL Server consolidation by migrating a few remaining third-party applications to the third party SQL Servers.  In addition, the back office still needs to be consolidated to a clustered pair.  By the end of 2005 Q1, we should have the final architecture in place.


Legacy Comments

Sarah Martin
re: <b>Database Security Initiative</b>
I was curious why you had to move your SQL Server out of the DMZ?

Price Waterhouse is currently auditing me for SOX compliance...not a good time.

Derrick Leggett
re: <b>Database Security Initiative</b>
??? Are you serious? It's the DMZ. Think about it. Let me know if you have any questions after you complete the thought process.

re: <b>Database Security Initiative</b>
Does SOX require the database servers be moved from the DMZ ? We have a few sqlserver on the DMZ that has client data and will eventually carry many more critical data. Should I move it out of DMZ