Horkay Blog
The postings on this site are my own and do not represent my Employer's positions, advice or strategies.
Tuesday, 01 December 2009

SQL Server security, logins, auditing and reporting still not where it needs to be ?  Maybe someone can help me with ideas, if so email, me or leave me some comments.

I've been through a lot of requirements and implementing everything the information security group wants is like Macgyver with duct tape.  Currently this is for SQL 2005, but I'd like to know how to implement it for SQL 2008 as well.

Currently I'm meeting the following Information Security requirements:

  • Limit Logins by specific IP or IP Ranges in combination with Time of Day [login trigger]
  • Limit Logins by Time of Day [login trigger]
  • Disconnect logins that are connected past there authorized time. [job running every 5 minutes]
  • Keep Record of All Logins (success or Failures) [daily job to scrape error log]
  • Keep Counts of Login Failures [daily job to scrape error log]
  • Keep track of Date and Time Password was changed (sql authenticated only) [Tracing]
  • Provide reporting and alerting that shows counts of failure, by month, by id etc. [Reporting Services and SQL Agent]
  • Limit number of concurrent connections by ID and by Global Count [login trigger], so the max number of connections for an instance may be 1000, and the max for a user maybe 5, and the max for the application id may be 300.

Now they want me to selectively enforce password length and expiration based on the following requirements for SQL Server Authenticated:

  • Service accounts  28 characters, expire yearly  (accounts that are not, or should not be used for general sign on’s and limited by what machine(s) they can login from)
  • Admin accounts 15 characters, expire yearly (accounts that are used by DBA’s)
  • User accounts 8 characters, expire every 90 days (normal user accounts) [this is easy as our Active Directory can enforce this]

It just seems that meeting all these requirements in SQL becomes very difficult to administer; we're running a combination of:

  • Dedicated database for reporting, configuration and capture of statistics
  • Login Trigger (difficult to administer and one little issue everyone is locked out)
  • Tracing (to capture when a user changes their password)
  • SQL Agent jobs (to clean up, scrape and roll over reporting tables)
  • Reporting Services (for reporting)
  • Enabling the DAC
  • Enabling Login Auditing
  • Enabling CCC (Common Criteria Compliance)

My counter-parts in Oracle seem to have an easier time meeting all these requirements, with less overhead, easier to administer and keep running, and less danger of locking everyone out.

SQL Server continues to frustrate me with this, anyone have suggestions or an easier time with SQL Server 2005 or 2008?

All these things should be available out of the box, duct taping all that stuff together in sql server is absolutely brutal, documenting and training someone on it is even worse.

It does provide good job security, as let the suits out source the DBA Staff after implementing all of these requirements and they'd sure have a tough time finding someone to run this stuff easily!

The "Suits" and "Information Security"!

Tuesday, 01 December 2009 13:08:40 (Central Standard Time, UTC-06:00) | Comments [1] | SQL Server |  Security#
Search
Popular Posts
Unpatched Vulnerabiltiy discovered ...
Spring Fornicator brewed...
SQL Server Security, not where it n...
DTA - Failed to initialize MSDB dat...
Check the Uptime of a Windows Serve...
Recent Posts
Archive
June, 2017 (2)
May, 2017 (2)
April, 2017 (2)
March, 2017 (1)
February, 2017 (1)
December, 2016 (2)
October, 2016 (2)
September, 2016 (1)
August, 2016 (1)
July, 2016 (1)
March, 2016 (2)
February, 2016 (3)
December, 2015 (4)
November, 2015 (6)
September, 2015 (1)
August, 2015 (2)
July, 2015 (1)
March, 2015 (2)
January, 2015 (1)
December, 2014 (3)
November, 2014 (1)
July, 2014 (2)
June, 2014 (2)
May, 2014 (3)
April, 2014 (3)
March, 2014 (1)
December, 2013 (1)
October, 2013 (1)
August, 2013 (1)
July, 2013 (1)
June, 2013 (2)
May, 2013 (1)
March, 2013 (3)
February, 2013 (3)
January, 2013 (1)
December, 2012 (3)
November, 2012 (1)
October, 2012 (1)
September, 2012 (1)
August, 2012 (1)
July, 2012 (4)
June, 2012 (3)
April, 2012 (1)
March, 2012 (3)
February, 2012 (3)
January, 2012 (4)
December, 2011 (3)
October, 2011 (2)
September, 2011 (2)
August, 2011 (8)
July, 2011 (4)
June, 2011 (3)
May, 2011 (3)
April, 2011 (1)
March, 2011 (2)
February, 2011 (3)
January, 2011 (1)
September, 2010 (1)
August, 2010 (2)
May, 2010 (2)
April, 2010 (3)
March, 2010 (1)
February, 2010 (4)
January, 2010 (1)
December, 2009 (3)
November, 2009 (2)
October, 2009 (2)
September, 2009 (5)
August, 2009 (4)
July, 2009 (8)
June, 2009 (2)
May, 2009 (3)
April, 2009 (9)
March, 2009 (6)
February, 2009 (3)
January, 2009 (8)
December, 2008 (8)
November, 2008 (4)
October, 2008 (14)
September, 2008 (10)
August, 2008 (7)
July, 2008 (7)
June, 2008 (11)
May, 2008 (14)
April, 2008 (12)
March, 2008 (17)
February, 2008 (10)
January, 2008 (13)
December, 2007 (7)
November, 2007 (8)
Links
Categories
Admin Login
Sign In
Blogroll