Security
It's everybody's job, you know.
Two of the cool new features that we talk about in Essential SQL Server 2005 and Guerrilla SQL Server 2005 are:
- Data Definition Language (DDL) Triggers, which allow us to
catalog code that gets fired synchronously with event that trigger it. This is
useful in cases where you want to hook into the processing sequence for
commands like CREATE TABLE, ALTER VIEW or DROP ASSEMBLY, potentially logging
who did what or issuing a ROLLBACK to cancel that work.
- EVENT NOTIFICATIONS, which route information about events
asynchronously to Service Broker queue after the event has been processed.
EVENT NOTIFICATIONS are available for 193 specific events, including the events
we can use for DDL triggers.
This processing behavior difference is critical. EVENT NOTIFICATIONS
cannot easily be used to cancel work before it gets done, which a DDL trigger
can do. On the other hand, DDL triggers can’t be written for all 193 possible
events. So there are times we find ourselves wanting to use a synchronous
trigger for non-DDL events. For example, consider the routine situation where --
at the end of the month -- a given SQL Server needs to be dedicated to running
long-running and demanding batches and procedures to close out the financial
records. Normally, a "black-out" period is set by management for some interval (like
8:00 PM on the last day of the month) until work resumes the next day. The
question becomes: how can we easily automate the enforcement of this policy?
Well, if we could inject code into the logon process, it
would be easy. However, SQL Server hasn’t really made it easy to do that. At
least before Service Pack Two for SQL Server 2005. In this service pack a new
type of trigger is introduced: the LOGON TRIGGER. Let’s see how they work by
creating one.
What I’d like to do for auditing and troubleshooting
purposes is first create a database with just one table in it that allows me to
record logon activity. There’s nothing new about coding that up:
use master
go
if not db_id('audit') is null
drop database audit
go
create database audit
go
use audit
go
create schema admin
go
create table admin.LogonAudit(eventData xml,denied bit default 0)
go
use master
go
Let’s also create a user to test with:
if not suser_sid('userLogin') is null
drop login userLogin
go
create login userLogin with password = 'someEasyPassword'
go
Our trigger is actually fairly simple to write:
-- Drop our trigger just in case an older version exists...
if exists(select * from master.sys.server_triggers where name like 'noUserLogins%')
drop trigger noUserLoginsDuringMonthEnd on all server
go
-- Name it
create trigger noUserLoginsDuringMonthEnd
-- Applied at the instance level
on all server
-- Execute this code as the person who cataloged it
with execute as self
-- New, fires when a logon is attempted
for logon
as begin
-- Get the range of the backout period
-- starts on the last day of the month at 8PM
-- and ends at 9 hours later
declare @bob datetime,@boe datetime
select @bob = dateadd(m,1,getdate())
select @bob = convert( datetime,
cast(datepart(yyyy,@bob) as char(4))+'-'+
cast(datepart(MM,@bob) as char(2))+'-'+
'01 00:00:00'+' ', 120)
set @bob = dateadd(hh,-4,@bob)
set @boe = dateadd(hh,9,@bob)
-- If its currently in the back period and
-- a disallowed login is tried...
if (getdate() between @bob and @boe) and
(original_login() = 'userLogin')
begin
-- Stop the login
rollback
-- Record the attempt
insert into audit.admin.logonAudit values (eventData(),1)
-- See what happens to messages...
raisError('Attempted user login during month-end blackout',16,1) with log
end else
-- Record the attempt
insert into audit.admin.logonAudit values (eventData(),0)
end
go
So what happens if somebody tries to logon using the
userLogin we created during the black-out period? They get a message like this:
Personally, I have mixed feelings about this. It is great
because it works and does a nice job NOT displaying my potentially
security-sensitive error message (from RaisError) to the user. However, the
error message shown gives away two pieces of information it doesn’t need to:
the fact that a trigger nixed the log-on ("due to trigger execution") and that
there might very well be some database on the system called "master." I’m not
really sure why the SQL Server team decided to have this dialogue show those,
so I filled a bug about it. If you agree that this isn’t good, you can show
your support by voting for my bug by voting in it at https://connect.microsoft.com/SQLServer/feedback/Vote.aspx?FeedbackID=237008
That said there’s no doubt that this is a very nice addition
to SQL Server 2005 and your bag of tricks.
A few months ago, I published a step-by-step procedure for using certificate to sign an assembly so an external access or unsafe assembly could be run without having to set the hosting database to trustworthy. The major problem with that process was its weight: a lot of steps and some still requirements in terms of certificate management. Turns out that yes, there is an easier way to make it work.
Part one -- preparing the master database
- As needed, start a series of queries against the MASTER database.
- Create the target database (meaning execute a CREATE DATABASE query).
- Code and compile the assembly you want to deploy. Make sure you sign the assembly with a strong name key file. This can be done with a PFX file generated by Visual Studio.
- Create an asymmetric key from from the compiled assembly using a statement like this: create asymmetric key <key_name> from executable file = '<path_to_dll_file>'
- Create a login based on that asymmetric key using a statement like this: create login <login_name> from asymmetric key <key_name>
- Grant that login the right to create either or both an unsafe or external access assembly (as needed) using: grant unsafe assembly to <login_name>
Part two -- preparing the hosting database
- As needed, start a series of queries against the desired user database.
- Create a user in that database mapped to the login created in part, step 5. (e.g. create user <db_user> from login <login_name>)
- Give that user the right to catalog an assembly, e.g.: grant create assembly to <db_user>
- Catalog the desired assembly using the now trusted asymmetric key with a statement like: create assembly <assembly_name> authorization <db_user> from '<path_to_dll_file>' with permission_set = unsafe
From there on, its just a matter of mapping your methods and classes to the desired T-SQL objects.
Note: the following is just to help Google find this post.
is not authorized for PERMISSION_SET = UNSAFE. The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission. RegistryReader
A special shout-out to David Carrico who got me started on this instead of watching SpaceBalls tonight... It really should go without saying but it is espeically true for SQLCLR Assemblies: Be careful who you grant view defintion of an assembly to -- least you unintentially expose more than you might expect. Consider some seemingly harmless code where you've hard-coded some secret into source code:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
public const string _SECRET = "12345";
[SqlFunction]
public static SqlString SomeSecretsArent(SqlString Parm)
{
return new SqlString(Parm.Value.ToUpper());
}
};
Well, Dark Helmet won't have hard time cracking Druida's defenses if you're writing that code! If you grew up on Good-Old-Fashioned C (like me), you might think that _SECRET would simply be either discarded as usused or emited as literal string into the code where it was used. Sorry to tell you otherwise, but no, that's not what .NET does. Don't believe me? A little experiment might help see the light. Compile that code and catalog it as an Assembly into some database. I've done so into a database called Scratch. Then create some login and give it access to your test database. I've called my login and user Jack (my favorite troublemaker, you know). Grant that user VIEW DEFINITION on the Assembly in the appropriate schema, ala:
grant view definition on assembly::SomeSecretsArent to jack
Note that you've not granted any other permissions to Jack, other than connect and login your test database, and the right to see the defintion of the assembly. So exactly what does that mean? Interestingly enough, it means that Jack can execute this query:
select * from sys.assembly_files
At first, that might seem pretty harmless. If you deployed the assembly for yourself, likely all Jack is going to see is the first line of the table below. If you used Visual Studio to deploy, you'll see at least the files shown below:
So, what's the danger here? Afterall, all we're seeing is a bunch of hex bytes. Try this query as Jack:
select cast(content as XML) from sys.assembly_files where name like '%.cs'
Oh... well, that won't do. So okay, you might think, that makes sense and you just won't use Visual Studio to deploy so the .CS file won't be there. And you'll be safe, right? Um... No, but seeing why isn't quite as easy. Go back to Visual Studio and write a little console application like this.
using System;
using System.IO;
using System.Data.SqlClient;
namespace Downloader
{
class Program
{
static void Main(string[] args)
{
using(SqlConnection conn = new SqlConnection("[omited]"))
using (SqlCommand cmd = new SqlCommand("select name,len(content),content from sys.assembly_files where file_id=1 group by assembly_id,name,content",conn))
{
conn.Open();
string name;
long size;
using (SqlDataReader r = cmd.ExecuteReader())
{
while(r.Read())
{
name = r.GetSqlString(0).Value;
size = r.GetSqlInt64(1).Value;
Console.Write("Downloading {0} {1}...", name, size);
using(MemoryStream ms = new MemoryStream((int)size))
{
byte[] buffer = new byte[size];
r.GetBytes(2, 0, buffer, 0, (int)size);
using(FileStream fs = new FileStream(@"c:\"+name + @".dll",FileMode.Create))
{
fs.Write(buffer,0,(int)size);
fs.Flush();
}
}
Console.WriteLine(" done");
}
}
Console.ReadLine();
}
}
}
}
Then go out and get yourself a copy of Lutz Roeder's Reflector and crack open the DLL files our last little bit of code downloaded. Specifically, drill into the class, derived types.
Hopefully my point is clear. Jack can now hijack your code and see your secrets. His Schwartz might be bigger than yours afterall. Mind you, the only permission Jack has in scratch is other than connect and login is VIEW DEFINITION on the assembly file.
"So, Lone Star, now you see that evil will always triumph because good is dumb." -- Dark Helmet
When Ken Henderson asks, I listen. He's asked this question and there's been some good responses. My favorite is from Jeff Parker because its probably the most honest:
Most DBA's are not .NET programmers.
I'm not so sure that most DBA's aren't programmers at some level, though. Most DBAs do write code in T-SQL. Many of them are versed in scripting. For them programming means creating tools to help them do their job, not building applications. And that's why Jeff's statement is the most correct: .NET isn't about building tools, its about building applications. So while they get ideas embodied in the code, its a bit like asking a blacksmith how to build a skyscraper when you ask them about .NET.
And this is precisely why I think off by default isn't just a good security practice, its a good configuration practice. Many of other posters to this thread have noted that in and of itself, the hosted runtime isn't any more dangerous in its typical application and say, T-SQL code. SQLCLR used right doesn't try to much more than extend what SQL Server 2005 is already good at: processing T-SQL Queries. I've long believed that the best use case for SQLCLR is write functions that do complex calculations or that leverage something in the .NET Foundation Class Libraries that T-SQL is missing or is poor at. That includes things like financial functions, regular expressions and XML parsing. The idea is that we're just extending T-SQL via the CLR, not subverting or replacing it. Stored Procedures based on CLR are useful when you need to perform some CRUD operations as a result of procedural logic that's either easier to write and maintain in a .NET language or, again, you need to leverage some functionality in the framework like compression or Web Services. User Defined Types certainly have a place, but its no bridging business objects into in-proc environment. The best use for a UDT I've seen so far is to build composite scalar types, like complex numbers, geographical co-ordinates and so on. CLR triggers simply don't seem to do much for me. I'm hard pressed to think of cases where I wouldn't be better served to write T-SQL triggers that called SQLCLR UDFs when needed instead. Aggregators have some uses: obviously needed for UDTs and I've some other use cases for them.
Where I suspect we'll see the biggest security issues with SQLCLR is that folks won't take the "Extend T-SQL" message to heart. They've move business logic into the server and just assume that things work the they do in a non-hosted runtime. That may or may not be the case. For example, I've heard lots of people who got excited that they'd now be able to call SOAP Services from within SQL Server. And, sure, that's cool. But how do you know if you really trust the data your getting from that service? Or that the service is still the service you thought it was? My concern runs deeper too. Suppose you have a good chunk of business logic lodged in the server and you know its got holes. But fixing them means not only taking the application off-line for a while, but also the whole database. Maybe not so much of a problem for databases that just support a single application. Pretty much "no way" for a busy OLTP data warehouse.
But lets not forget on the best use cases for SQLCLR is to avoid writing something as an Extended Stored Procedure (XP). XPs are typically far more of a security and reliability concern than most developers and DBAs expected. At least SQLCLR gives SQL Server better control over the code and runtime while being nicely meshed into the current security model for other objects. I'd much rather tell somebody to blindly enable SQLCLR before I'd encourage them to use an XP.
This sort of answers Ken's third question: If you see SQLCLR as a security threat, what specific issues are you worried about? How do I answer Ken's second question: Do you think it makes sense for security-conscious users to disable SQLCLR and avoid apps that use it? The answer is yes and no. Yes, I do think that unless you have a good reason to enable CLR support in the first place, leave it off as it is by default (so logically, no, they shouldn't disable it, it's already disabled). Should it be avoided? That depends on the specific case. I'd say that the unless the DBA knows, understands and can support the code that coming onto the server, he or she might be well served to avoid it. But that's not specific to SQLCLR. The same holds true of T-SQL objects and Extended Stored Procedures. The best way that most DBAs will "grok" SQLCLR is if its presented as an simply extending T-SQL.
Keith Brown has a meaty posting on his blog talking about channel security for SQL Server. The highlight, at least for me, is this:
Why am I bugged by this? Why don't I just use SSL and get in line with everyone else? Because setting up SSL connections is considerably slower than setting up Kerberos connections (think public key operations in SSL versus purely symmetrical operations with Kerb), and it requires certificates and PKI where Kerberos is already built right in to my domain infrastructure.
I really like the post, but there is something that bugs me about it. It feels like he's complaining about the glass being half-empty.
Sure, I'll go along with his contention that in your in a fully Active Directory integrated environment that using the Kerberos would be a great solution and for exactly the reason he gives. But frankly, I don't see Microsoft changing their point-of-view that SSL is a better choice, and no, I don't expect the story for Yukon (SQL Server 2005) will make Keith any happier. These are best demonstrated when you think about situations where identity isn't shared, but trust explicitly is. And what kinds of situations are those?
- SQL Server Replication of data between business partners who may or may not have their own Active Directories. In this case, the need to exchange data securely is easily understandable, but the sharing of an identity as the basis of key and ticket exchange becomes problematic. However, a good PKI implementation partial solves this problem by enabling the easy exchange and validation of certificates.
- Data exchange between Compact Device applications and domain member SQL Servers. The typical Compact Device usually lacks the ability to participate in the Active Directory and thus cannot drive the Windows Integrated context needed for such a login. However, these devices typically do support SSL out-of-the box.
- SQL Server 2005 Service Broker applications distributed over many hosting domains. One of the reasons that Service Broker appeals to me is that in integrated business scenarios, different parts of an application can be hosted on servers in mutually non-trusting domains. For example, a TeleServices company may "own" and host service programs that deal with the generation of sales orders, while a second company "owns" the service programs for fulfillment and third "owns" the service programs for executing the financial aspects of an order. Sharing an Active Directory between these three parties might prove to be both expensive and difficult to manage correctly. However, since Service Broker uses certificate authentication of endpoints, this should be much easier and less expensive to achieve.
These cases demonstrate how SQL Server transcends many of Microsoft's other products in terms of secured inter-operation by specifically avoiding being overly dependent on the Network Operation System for security. So am I saying that Keith's desire better integration with Kerberos security is invalid? Not at all -- it has obvious value in many cases. But I would say that I, for one, am glad that the SQL Servet Team embraces SSL and puts their focus on that technology. Its a great example of how flexible SQL Server can be, albeit with some performance hit. Seems like a small price to pay to be able to achieve this kind of flexibility with a minimally shared surface area. I'd rather have Microsoft work on enhancing the reach and performance of SSL rather than expend that effort on making Kerberos work better, at least as far as SQL Server goes.
Then again, maybe I'm just used to seeing the glass as half-full instead of half-empty.
The aforementioned two-part series looking at simple data protection with symmetric key encryption in SQL Server 2005 is now up on SSWUG (registration may be required but is free.)
Just a reminder that the Greater Omaha SQL Server User's Group meeting is tonight at 6:30PM at Vatterrott College at 11818 “I” Street. One of the my SQL Server mentors, Luke Schollmeyer, has a great presentation about the T-SQL enhancements in SQL Server 2005 for us.
Speaking of User's Group, it looks like I'll doing a short presentation for the Omaha/FunWith.NET folks next week. Topic is using the Reporting Services Web Service. Remember, this is also the day of the next MSDN event at the AMC24. The next MSDN event has been announced for June, you can register for it here.
On a more personal note, I had a really interesting chat with from Romania yesterday. Cristian is a new SQL Server MVP from Romania. He had some really good questions about SQLCLR and SQL+XML for a presentation he's doing an MVP gathering in Europe. What really struck me though was just how bad I've been about saying thanks to the folks who have been helping me about so much as a Newbie myself. So, in no particular order, here's yet another public shout out in appreciation to: Bob Beauchmin, Hilary Cotter, Stephen Dybing, Ben Miller, Erland Sommersag, Michael Rys, Roger Wolter, Euan Garden, Niel Bergland, Dan Sullivan, Aaron Bertand, Don Demsak, Jeff Julian and Adam Machanic.
Okay, so I might just be one of these Caveman users who doesn't "get" tabbed browsing. If it looks like Chrome and its used like chrome then it must... anyway, I though it would be interesting to see what you wanted in IE8 since I'm really starting to wonder if we're going to get much more than "more security" and some "chrome" out of IE7. My list fairly short today:
- CSS3, hopefully.
- XLink for "suggested related content" exploration and link linage.
- XForms support in-situ. Don't get me wrong, I like InfoPath the idea, but the client... not so much.
- HTTP header tracing as a pane
- Flight path/Macro recording to an XML play-back file.
- Save/Load/Add favorites to a Web Service so they go with me freak'n everywhere.
- Define custom security zones managed by GPO.
After a long break,
Alter View Create Function is returning to
SSWUG.ORG on February 15th with a two-part series on Cryptography in SQL Server 2005.
Looks like its going to be a busy month for the IT Communities in the Greater Omaha area:
- First up is OLUG's monthly meeting on the 1st. The topic of discussion? Asterisk!
- Omaha SPIN should be having a meeting on the 15th, but I've not heard a topic yet.
- NebraskaCERT CyberSecurityForum will be meeting on the 16th for breakfast and a general disucssion of Security Trends.
- Bummer! The Omaha Macromedia group is having the renowned Kevin Towes come speak about Flash Communication Server on the 17th. Not that this is a bad thing but...
- Also on the 17th, the Greater Omaha SQL Server's Users Group will be meeting. Group Co-Founder and SQL Guru Luke Schollmeyer will be talking about the T-SQL enhancements in SQL Server 2005.
- Monday the 21st, the Omaha Java User's Group will having a presentation by Dave Burchell about the PayPal API.
- Thursday the 24th is a .NET-DoubleStack. In the afternoon Mike Benkovich will be visiting town to present the quarterly MSDN event. Following that, FunWith.NET should be meeting at Creighton West. This session will be a bit different -- it's show and tell night!
- Bummer! That's the same night that the Omaha PerlMongers meet up.