SQLServerPedia

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"!

This is a very general question that someone could certainly write a book on but I’ll go at answering it by providing a few options and explaining the pro’s and con’s of each solution.  The following list is by no means comprehensive.

The first question you need to ask yourself before going on this journey is where does the encryption need to occur and who are you protecting the data from.  These two questions will drive the amount of effort and complexity required in building a solution.

1)  In database encryption.  The database is encrypted in the database and is viewable to no-one unless they have access to the encryption key.  The data could be encrypted at the application level by first running the data through an encryption algorithm before inserting into the database or the data can be encrypted using SQL Server 2005’s column level encryption or SQL Server 2008’s transparent data encryption.  Using in database encryption is the most secure option in that it can be leveraged to protect sensitive data from anyone that gains access to the database engine itself (whether valid or invalid access) however it has significant performance and scalability caveats.  Encrypting at the application level basically ensures that SQL Server will not be able properly index the data and therefore query perofmance may be dreadful with large data sets.  Leveraging column level encryption or TDE also will introduce query processing overhead and adversely affects the performance of your queries but not to the same extent.   Another drawback of In Database encrption is that it renders backup compression (whether through SQL Server 2008 or a third party backup product or winzip) obsolete.  Encrypted data is not compressible, therefore the size of your encrypted database will roughly equal the size of your backup files regardless of the compression technology used.

2)  If you do not need to secure data inside of the database but want to secure data once it leaves the database, a good solution would be to leverage a third party backup solution such as LiteSpeed.  These solutions will compress and also encrypt backup data in memory so that it is secured as soon as it leaves the database.  This is an attractive option to companies that store backups on network devices and are worried about internal or external users gaining access to network resources and therefore having direct access to backup files.  If you think that native backup files are secured, try opening a small database backup file in notepad.  You will see that all of the data is stored in plain text.  If you have a copy of pubs lying around from the SQL 2000 days you can back it up, open the backup file in notepad, search for “Smith” and you will see Smith’s social security number right there.  This method of database encryption will not secure the data in the database, so anyone who gains database access has free reign on your data, but thats what DBA’s are for right???

3)  A third option, and the last that we will discuss here, is to leverage encryption at the tape level.  Most organizations take their disk based backups and eventually migrate them to tape, where the backups files are then encrypted and eventually moved to offsite storage.  This should be a bare minimum for any SQL Server as once a tape leaves offsite anything could happen to it.  There have been many major news stories recently discussing companies that have had backup tapes lost or stolen and without leveraging at a minimum the tape systems encryption, that data is available to anyone that may “find” a backup tape.  Tape level encryption does nothing to protect the database info inside of the database or the backup data inside of a companies network, but it does protect the data once it is transported offsite.  The nice advantage of this as well as the previous option is that they impose no overhead on standard database workload, they simply may add some overhead to your backup time depending on the level of encyption that you are performing.

Hopefully this brief rundown of database encryption options has given you enough info to get started decideing which implentation strategy is best for you, and as always if you have any further questions, feel free to submit them.

Q:  We use DB2 Connect to access DB2 from MS Access. Do we need this for SQL Server also or does SQL Server have a built-in interface or do we need something else?

A: No you do not need to use DB2 Connect in able to access DB2 data within SQL Server.  SQL Server supports linked servers which allow you to register a remote data source in SQL Server and execute queries against it.  Linked servers can be remote SQL Server instances or instances of Oracle, DB2, Sybase, MySQL, etc…  As long as there is a supported OLEDB driver for the data source you can add it as a linked server.  There is a blog post you can read here:

http://blogs.msdn.com/dotnetinterop/archive/2006/01/20/defining-a-db2-as-a-linked-server.aspx

that walks you through the process of setting up a linked server to DB2 and you can also look up Linked Servers in books online for more information.

Detective By Accident – that’s what DBA really stands for, not Database Administrator.  Come on, if it was really Database Administrators, we’d be DA’s.  Granted, most of us don’t look like Warrick Brown or Sara Sidle, but when something disappears in the datacenter, people pick up the phone and call us.

On July 24th, Jason Hall and I are doing a webcast called CSI:DBA – Going Back in Time to Understand What Happened to Your Data.  We’re going to cover how to find out who made schema changes, who deleted/truncated tables, and how to get the data back without time-consuming full restores.  I’ll be talking through some of the native SQL Server tools to do it, and Jason will be showing off the Quest tools that make the job easier.

Performance Dashboard Reports

These say Performance in the name, so it would sound like they’re not the right answer to this problem, but it turns out there’s a great report in there to address this very issue.  The Schema Changes History report shows what logins made schema changes in each database, and when the change was made.  Here’s an example:

These reports run off the default server-side trace that runs all the time in SQL Server 2005 and 2008, so there’s no additional performance impact in order to capture this information.  These reports are included with Microsoft SQL Server 2008, and for 2005, they’re a separate download – download the Performance Dashboard Reports.

These reports have been part of the solution in all of my recent webcasts.  I’m a big believer that every DBA needs to have these installed and play around with them because they’re so useful.  I was just giving career advice to a DBA yesterday: he specialized in writing reports, and I said if there’s one type of report to produce, it’s RDLC reports that integrate with SQL Server Management Studio.

That’s a great start – it does tell us who did it and a little bit about what they did – but it only works with SQL 2005 & 2008, and it doesn’t give us exactly what their statement was, and it doesn’t give us anywhere near enough to roll back their devious work.  We need more information, so let’s get the toolbox out of our Hummers and check out our next tool.

Database-Wide DDL Triggers

SQL Server 2005 introduced DDL triggers that can be fired off whenever someone runs Data Definition Language code like altering a table, adding an index, or dropping a view.

People have written DDL triggers that log schema changes to a table – here’s one example of auditing DDL statements in a table, and here’s another.  I’ve used some of this code myself to track schema changes, and I do love the results:

I get a nice table with who the guilty culprit is and I get their exact SQL statement.  That’s a step in the right direction, because I can print it out and take it with me into the interrogation room.  (Every DBA has an interrogation room, right?)

That gets us our confession and our guilty verdict, but it doesn’t get us back to how things used to be.  For that, you’ll need to tune into our next episode – I mean, webcast!

SQL Server 2008 has the encryption key management feature’s you’re looking for…

SQL Server 2008 Extensible Key Management enables the encryption keys that protect the database files to be stored in an off-box device such as a smartcard, USB device, or EKM/HSM module. This also enables data protection from database administrators (except members of the sysadmin group). Data can be encrypted by using encryption keys that only the database user has access to on the external EKM/HSM module.

Extensible Key Management also provides the following benefits:

* Additional authorization check (enabling separation of duties).
* Higher performance for hardware-based encryption/decryption.
* External encryption key generation.
* External encryption key storage (physical separation of data and keys).
* Encryption key retrieval.
* External encryption key retention (enables encryption key rotation).
* Easier encryption key recovery.
* Manageable encryption key distribution.
* Secure encryption key disposal..

See the full article Understanding Extensible Key Management for details.

Q: I need a query that will tell me all objects that a user account has access to in each database on the server. How can I find out what roles a user belongs to and what objects they have permissions to?

Kevin Kline says: You need to get familiar with the free SQL Server Script Library that is available at http://www.microsoft.com/technet/scriptcenter/scripts/sql/sql2005/default.mspx?mfr=true  

However, since you specifically are looking for permission information be sure to check www.sqlsecurity.com for great (and free) scripts related to all things security.

You should take advantage of security groups if you want to keep your current arrangement; add the developers to a single group and simply maintain the group’s authorities.

Still, I should think Database Snapshots are perfect for your situation. A snapshot is a static, read-only view of a database. You can have the developers create their procedures on the snapshot, evaluate the code, then create it on the source database. The developers can have full access to the snapshot database, while you maintain restricted permissions on the source (real) database. Read more <a href=”http://msdn2.microsoft.com/en-us/library/ms190467.aspx”>here</a>.

Q:   How I can handle SQL injection in all over application by using query??

Kevin Kline says:   It’s funny that you mention this since Microsoft UK just had a very public SQL Injection attack on their website. It’s reported on TechWorld here.

In essence, a SQL Injection attack is where a programmer has written a web page that exposes a SQL query to the internet. For example, you might write a query that allows the end user of a web page to enter the name of product that they want to find on the web site. However, the web site is now vulnerable to attack because the programmer placed no string checking onto the values returned by the web page, which some malicious person could, instead of putting the name of product into the open field, substitute something like ‘DELETE FROM products’.

SQL Injections is a broad and complex topic though. So you’d best read what the experts have written. I like what NGS Software puts out, such as this white paper.

I also really like Chip Jones’ website called SQL Security.com, which has extensive information on SQL Injection attacks and defenses. It also has a really good utility available for free called LOCKDOWN.SQL.

Technorati Tags:
SQL injection attack, database security, database vulnerabilities, NGS Software, Chip Jones, www.sqlsecurity.com, LOCKDOWN.SQL
Add to: | Technorati | Digg | del.icio.us | Yahoo | reddit |

Jason Hall says:  There is no Microsoft supported method for retrieving a password from SQL Server. Your best bet is to log in with some other administrator account (windows authentication perhaps using the local administrator account on the SQL machine) and reset the sa password using Management Studio. 

Q:  How can I password protect my database?

Bryan Oliver says:  In SQL Server 2000 you can encyrpt your stored procedures.

If it is data, particular that you want to protect take a look at this article on SQL Server central

For SQL Server 2005 take a look at this article on Database Journal.com

Hope this helps!

Technorati Tags:
SQL Server 2005, SQL Server 2000, encryption, www.databasejournal.com, www.sqlservercentral.com
Add to: | Technorati | Digg | del.icio.us | Yahoo | reddit |