Microsoft Security Advisory (961040): Vulnerability in SQL Server Could Allow Remote Code Execution

This has been a hot topic online recently, and Jason Massie brought it up last week, and I have seen it blogged a few different places, but now it has a MSRC Advisory for it, and chances are, that your server is affected and you can't do much about it at the current point in time:

http://www.microsoft.com/technet/security/advisory/961040.mspx

Since these Advisories are constantly updated to reflect the newest information, I am not going to rehash it here, but make sure that you read the Advisory and at least know whether you are affected and what your level of risk is.

Can a DBA function without rights to the Server OS?

I see posts often regarding questions for what rights should be granted and to whom with regards to SQL server.  On this post I am going to address what rights should a DBA have to the Server itself based on the following question recently from the MSDN Forums:

What is a DBA/sa prevented from doing if not a local admin on the SQL Server?

If you read the above post, you will see that my answer is different from start to finish on the thread.  At first I would have said that a DBA needs to be a Local Administrator on their server, but this really doesn't stand up to the whole idea of least necessary permissions, that as a DBA I enforce myself.  I happen to be a Local Administrator on my servers at my job, but that really probably is overkill, and could be reduced.  So what rights would a DBA need to a SQL Server at the OS level?

This is determined and or dictated by the definition of the role of a SQL Server DBA.  As a SQL Server DBA, there are certain things that I expect to be able to see and or do, and that I would argue belong within my realm of responsibility.  A short list of these tasks would be:

1. Monitoring Performance Counters
2. Managing Database Files
3. Managing SQL Server Updates and Service Pack Installations
4. Viewing System Event Logs
5. View active Processes on the Server through PerfMon or TaskManager
6. Start and Stop SQL Services
7. Monitor Available Disk Space

Now arguably, the easiest way to accomplish the above is to make the DBA a member of the Local Administrators group on the SQL Server, and I would never argue against this practice, it is after all the DBA's job on the line if the server goes down from a mistake they made as a local administrator by goofing with something they shouldn't have.  I personally like having the rope to hang myself, but I can also use that same rope to climb out of a valley in the event of an emergency as well.  I personally know my limits, and I know when to involve my server team members with looking at a problem.  This may or may not be due to my past experience as an NT4 and Windows Server 2000 Administrator, it was something close to 8 years ago, but I know where to draw the line of responsibility.

So where do you sit on this subject?  I am interested to know if you are a DBA that doesn't have Administrator Access to the Windows Server, how you created permissions to do the above.  How would one go about granting the individual rights required to be a DBA without being a Local Administrator?  I can only figure out how to grant a subset of the above, but as I said previously, I am not a Windows Administrator, MCSE, or any other sort of subject matter expert when it comes to security outside of the SQL Server database engine itself.

Are you safe without your Keys?

I am not talking about your house keys, or your car keys, I am talking about your SQL Server Keys.  SQL Server 2005 allows you to use keys for protecting objects and data in the server and databases.  It is very likely that if you are doing encryption in SQL Server that you already know that you have keys in SQL, but if you have never touched encryption, you might now know that, you too, have at least one key in SQL, the Service Master Key.  For those not familiar with this, the Service Master Key is the root of encryption key hierarchy in SQL Server 2005, it is by default used to encrypt Database Master Keys, linked server security information, and any credentials created using CREATE CREDENTIAL.  If you don't use any of the above, then read no further, you are probably fine.

Personally, I learned about keys, certificates, and encryption while studying for my MCITP Database Administrator exams at the beginning of this year, and that was enough to add a backup of the SMK into a password protected file to my installation procedures for each SQL Server Instance I have.  I personally have never needed to use the backup to date.  So what sparked this blog posting?  As usual, a post on the MSDN Forums, where a backup of a database using encryption was restored to a different server, and the poster didn't know the password to the database master key.  After goofing the initial answer on it, I had to do some investigating into what could be done in the case that the password to encrypt a DbMK is lost, and the answer isn't all that complex, and it is included in the post referenced above. 

However, it is much easier to just avoid the situation completely and have available backups of the SMK, as well as all the other keys in use in your database server. These can easily be done with DDL commands:

BACKUP SERVICE MASTER KEY (Transact-SQL)
RESTORE SERVICE MASTER KEY (Transact-SQL)

BACKUP MASTER KEY (Transact-SQL)
RESTORE MASTER KEY (Transact-SQL)

BACKUP CERTIFICATE (Transact-SQL)
CREATE CERTIFICATE (Transact-SQL)

One of the best references online is Laurentiu Cristophors blog on MSDN for security and cryptography in SQL Server.  Laurentiu is a frequent answerer in the SQL Security forum on MSDN, and has wealth of information available about this subject.  His blog is definitely a recommended read and is on my personal blog roll.

Since this is such a vast subject, I plan on putting some time into it in the near future, and writing some additional information on the subject.  However, in the mean time, backup those keys and certificates.

EDIT:  One thing I realized while thinking about his blog is I do have two different Certificates implemented in my servers, one for mirroring between two servers not on a domain, and one for a certificate user to provide permissions for a dynamic SQL search procedure that uses parameterized dynamic SQL to perform optimized searching in one of our databases, since application users are required to use stored procedures and have no table level access.  The certificate user has no associated login and is used WITH EXECUTE AS to provide the needed table level SELECT rights to run dynamic SQL code in the stored procedure.  The calling user only has execute rights on the stored procedure but can still make use of its dynamic code due to this type of impersonation.  I do have backups of both of these certificates, as created when they were created.

If you want more information about using Certificates to grant elevated rights with a Certificate User look at Erland Sommarskogs article Giving Permissions through Stored Procedures.

New Virus targeting SQL Servers

A post on the forums about a job that existed that was questionable turned out to be a relatively new virus that is targetted at SQL Server.  If you follow best practice security implementation of SQL, then there is nothing to worry about.  You can read about this virus on the symantec website:

http://www.symantec.com/security_response/writeup.jsp?docid=2008-091215-0809-99

Granting Access to users to View System Data without Granting Access to System Objects.

There are precious few scenarios where non-DBA's in my environment have any business looking at system tables.  However, I have seen questions on the forums where someone needs to allow a developer to select information off specific DMV's or system Views/Tables.  With SQL 2005, you can build wrapper stored procedures that execute under the context of the Database Owner Account.  In my environment this happens to be the sa user account for most databases.  So for a user to get information from sys.databases, you can create a stored procedure as follows:

CREATE PROCEDURE uspGetDatabaseInfo
WITH EXECUTE AS OWNER
AS
BEGIN
SELECT * FROM sys.databases
END
GO

Then all you have to do is grant a user execute rights to this procedure.  They can't run a selects against the system table, but they can view the information inside of them.