MS SQL Server 2005 Adds Security Features

nycsubway writes "Microsoft is planning to add in its own encryption and decryption to its newest version of SQL Server. From the article: 'The company is writing complex encryption and decryption functionality directly into the product so customers don't have to procure security features from a third party, or roll their own when the product becomes generally available next year.' I would also hope the default sa/password will no longer be there."

Comments

Good Thing

[DarkHelmet]

In the case of encrypted connections:
Mysql has this already In the case of AES Encryption
Mysql has this already

But in the case of having something asymmetric, something like this would be *incredibly* nice. I'd looove for a free software package to integrate something like OpenSSL in, so that I could encode a column using a certificate variable.

Still, Microsoft is doing a good thing overall.

Re:Good Thing

[Anonymous Coward]

Microsoft is encrypting the data that SQL Server stores so that WHEN someone breaks in to the server they cannot steal the data. Technically the interesting thing is that they have to encrypt the data keys in the indexes as well.

For everyone else, the notable thing is that Microsoft has decided that unencrypted data is not secure on a server running their software.

Re:Good Thing

[bob_dinosaur]

SQL Server 2000 SP3 supports SSL connections.

This announcment refers to the encryption of columns (which, yes, mySQL has).

That said, Microsoft are correct in stating that the hard part is key management. It's a pain in the arse to make sure everything is kept where it needs to be, and is available for recoveries etc.

Re:Good Thing

[TracerRX]

Doesnt MySQL do this already? http://dev.mysql.com/doc/mysql/en/SSL_options.html

Microsofts default encryption pass

[NightWulf]

will be 12345. Same as the one on Bill Gates' luggage.

Re:Microsofts default encryption pass

[armyofone]

So, if the default password is stoopid, (or even if it's smart), you should change it right away!!, duh! Otherwise, you're just asking to be bent over the barrel.

I remember when I first installed WinNT on a computer, (shudder - glad I won't be doing that again). First thing I did was change the administrator account to another name and disabled the 'guest' account. Same thing with that stupid '1234' default password on my RT314 router from Netgear - changed it immediately. Even if the default password was 'X87z2gh9', why would you not change it?

I mean, c'mon. It's called taking a little responsibility for yourself.

Repeat after me..

[Anonymous Coward]

Encryption is not security....
Encryption is not security....

Encryption is not security....

Encryption is not security.... ...

Re:Repeat after me..

[John Hurliman]

But it does help when you store sensitive customer data on a box you don't own, hosting hundreds of other websites. Many websites have been cracked or user databases stolen from someone setting up an account with the same hosting provider as the target site and gleaning information from the shared database server.

Re:Repeat after me..

[jea6]

Mostly, because Bruce Schneier says it isn't.

Its MSFT bashing time...

[Anonymous Coward]

Go ahead, slashdotters.. they mentioned MSFT!.. .Quick .. get your pitchforks and light the torches!

Nice, but...

[seanmcelroy]

The problem almost always lies in insecure middleware that connects to the database, not the database itself. Once information is decrypted by an ADO/ADO.NET data provider, if the accessing application is insecure, this won't do you much good. And by far, that's the largest problem.

Re:Nice, but...

[mingot]

All encryption is ever good for is to make it more costly(for an attacker) to retrieve than the data than the data itself is worth.

Because really, a person with enough determination can break any form of encryption by simply heading to the hardware store, buying a pair of pliers, and using them on the testicles of whatever unlucky bastard happens to know the 'key'.

Am I the only one this bothers?

[mfh]

I think it bothers me that MS SQL will have its own security, because I think that third party security, at least in the case of Microsoft products, dramatically increases the overall security. It never pays to have a false sense of security, and with all Microsoft products, we must beware of their security strategy. At least with getting more people involved with third party security, you get a new perspective on things. MySQL is open source so it has this added perspective by default.

I guess what I'm saying is that you can't compare closed source with Open Source. It would be dangerous to.

Re:Am I the only one this bothers?

[whiteranger99x]

And this is stopping you from using third-party security, how...?

Re:Am I the only one this bothers?

[man_ls]

I think this is a good thing.

Since Windows XP, Microsoft has done almost a 180 (well...maybe like a 135........but still) in terms of security. They've put extensive security-related features into XPSP2 assuming it ever comes out, their newest server is locked down as tight as anything can be out of the box (although enabling stuff isn't difficult, it's not online by default) and they generally use standards-based encryption.

I think that MSSQL 2005 security will probably be very good. Or at least, *good enough* The government probably can read everything anyways -- but the point is, if Joe Hacker (or Jaing Hackerong) can't read it without expenditure of time and money beyond anything he would have access to, then the mission is accomplsihed.

The whole point of cryptography is not to keep people from reading what you're saying. It's to raise the cost of figuring it out so high that it's not worth it to most people to break.

Re:Am I the only one this bothers?

[WaterBottle]

http://www.microsoft.com/technet/prodtechnol/winxp pro/sp2preview.mspx Enjoy.

So then...

[gmuslera]

future ms sql internet worms will travel encrypted?

Re:So then...

[whiteranger99x]

Of course, I would be worried if those worms traveled across the internet unencrypted. I wouldn't want a worm to be full of exploits!

In Related News...

[k4_pacific]

The Acme Safe Co. announced today that next year's model will feature a "lock" to prevent unauthorized persons from accessing the contents.

sa/password

[djwavelength]

SQL Server 2000 allows you to set the level of authentication to Windows Only (uses the Windows Domain security) or Mixed Mode. You have to specify a password for the sa account. You can have a blank password, but this requires an extra check box that says having a blank password is not recommended.

There is no default sa password...

onu!

[Triumph The Insult C]

v'ir nyernql penpxrq vg!

Misleading

[pvera]

SQL Server has not had a default password since SQL Server 7.

In SQL Server 2000 you would have to explicitly request "sa" to have a blank password, there is no way you can do this by accident. It even warns you in the installer that it is not recommended to leave "sa" with a blank password.

BTW, this behavior is present from version 1.0, it is not the result of a service pack or last minute security update.

Re:Misleading

[stratjakt]

Who cares if it sets a default password. Any DBA with a brain changes it, and it's the first thing they do.

The ones who didnt lost their jobs to india and have nothing to do but post on slashdot about how great mysql's security and encryption model is (actually, does it even have one?)

A DBA at one of my sites proudly called to tell me I can access the server over the internet. I thought he finally set up a VPN. Nope, a fixed internet IP on the database server. No sa password. Sheesh. He's unemployed, and deservedly so.

An SSL tunnel on port 1423 (maybe the wrong port I'm tired) has served me well when people dont want plain data being sniffed on the wire.

Authentication in a 2k+ domain is already more than solid enough for my liking (Kerberos + LDAP = better than any out of the box PAM setup I've ever seen). But oh yeah, microsoft sucks only open source is secure! Mod me up doubleplus groupthink.

sa account

[enkafan]

even now, the sa account is disabled by default and books online states it is there for backwards compatability only.

While it was long over due, SQL Server 2000 already complains quite heavily if you try to set a blank password for sa. It allows it, but there are (unfortunately) applications that have been written with a hard coded connectionstring of sa with a blank password.

Hope they do a better job

[billstewart]

Microsoft's past experience with encryption was consistently dreadful - things like PPTP having seven obvious bugs, some of which were password handling and some of which were violations of the one basic rule for RC4 (never encrypt the same stuff twice with the same key.) Hopefully they've gotten better.

Encryption algorithms are hard to design well, but if you've got a good algorithm and understand the conditions for using it, you can use almost anybody's code for it, and most people these days understand that you need to use academically vetted stuff and not just roll your own snake oil. But encryption protocols and other forms of packaging for algorithms can be just as hard, and something as pervasive as Microsoft database programs will be very widely used by people who don't Read The Free Manual, which means that even if they design it very very well there'll still be people who use it for things it wasn't designed to do securely, because they're trying to do a much broader range of things.

This is a harder problem than basic SSL-for-Credit-Card-Numbers, which is trying to let the client enter some bits on an unprotected Windows box hanging off the Internet, pack them in an armored box, and ship them to a usually-almost-as-badly-protected server on a well-advertised Internet connection, and optionally do some validation on whether one or both ends are really the machines Verisign thinks they are. That's a pretty well-solved problem, though it took a while to iron out the design issues early on an iron out all the bugs in the code, but general-purpose solutions to "database security" are pretty hard.

Re:Hope they do a better job

[Nintendork]

Microsoft addressed the major concerns of PPTP in 1998 with a post NT4 SP3 hotfix and DUN 1.3 for Windows 9x. The RC4 key blunder was one of the problems fixed. Check out this informative article.

There's still some minor issues, but unless you're protecting something that multiple, highly technical government spies with uber elite access are trying to get at, PPTP is good enough. Hell, if someone were that determined, I doubt they would choose PPTP as their point of attack. The odds that everything else is more secure are pretty freaking slim.

I disagree that Microsoft can't implement encryption techniques these days. I'm confident that since Microsoft first coded their implementation of PPTP, they've learned to pay more attention to security related features. Back then, vulnerabilities weren't nearly as big of an issue as they are today. Windows Server 2003 is proof that they're making a sincere effort now that the desire for "Secure out of the box" is high on the average customer's list of features. And what about L2TP (Another VPN protocol introduced with Windows 2000)? Know of any weaknesses in it? I can't find any articles with complaints about it and it's been around for several years.

How would you like it if you made a mistake 9 years ago, fixed it, and people still referenced it when arguing why you suck today?

-Lucas

Re:MS SQL Server 2005 Adds Security Features.

[archen]

That depends. This is talking about data encryption which as far as I know Postgresql doesn't do either. Postgresql does do SSL connection encryption and can use MD5 hashes for passwords, so if your server is secure, and your passwords are as well, then your data should be secure. The thing to be concerned about is "the company is writing complex encryption and decryption functionality directly into the product ". That's great and all, but who exactly is going to vouch for Microsoft correctly implementing this complex encryption? Are we going to have to take MS's word that it's secure because they told us so? Is it going to be possible for non MS (open source) stuff to connect to an MS SQL database with this stuff turned on?

Re:Should have known something was up

[CaptainSuperBoy]

Books online.. don't you have books online? You can install it off the server disk. It's indispensable.

I give it four weeks...

[drdreff]

That's the patch cycle now right? once a month? Either the IIS plugin or some "bugfix" will contain a flaw that exposes the private key to anyone with a .net passport.

thats a foolish statement

[mgoodman]

saying encryption is not security is just foolish. any reasonable security administrator realizes that there are different aspects of security -- and encryption is one of them.

security is about defense, in depth, of your data. simply putting out "bug-free" software will help, but it is not the be all and end all of security. there are other layers that your software relies upon that can be compromised.

strong encryption is a good way to *help* secure your data. sure, it is essentially security through obscurity, but even that has a bad rep.

realize this: if someone wants your data, they CAN get it. you might as well make them jump through some hurdles to get to it. hopefully by the time they crack your encryption the data would be useless anyhow.

also, security through obscurity does help ward off casual hackers. i know i certainly dont want to wait 4 weeks for john the ripper to crack some passwords. id just move on to easier targets.

Re:Its MSFT bashing time...

[Canberra Bob]

Dont forget to continuously keep bringing up MySQL. If SQL Server offers something, either reply with "MySQL already has that", or if MySQL does not, then reply with "who needs that anyway? Thats just bloat". After all, who needs foreign keys, views, triggers, procedures etc?

db encryption == pointless (usually)

[cheezit]

Having your db engine do encryption is great...if your biggest threat is an attacker reading the binary db image from disk. Much more likely threats to data are associated with compromised accounts, and this won't help you then.

If your web app needs to read credit cards out of the database, the account it runs under sees them in the clear, even if your db did super fancy encryption. If you don't want other users to see that data...GIVE THEM SEPARATE ACCOUNTS AND USE ACLS!!!!! Db encryption sounds good but is pointless in most scenarios.

ODBC

[Col. Klink (retired)]

Does this mean that the unix Sybase/FreeTDS ODBC drivers under Unix will no longer be able to connect to MS SQL? Will MS offer any unix/linux ODBC drivers?

pgp on top of sql server

[jdkane]

I've been involved with using PGP to encrypt data before storing it to a Sql Server db. PGP allows us to ensur the data is secure, even if the database password is compromised. We don't keep the PGP private key on the server, but only the public key used to encrypt the data before storing it (the data is also protected by SSL while in transit and never touches the disk until after it's been encrypted). The customer unlocks the data with the private key after downloading it from the server. It's very secure, but also very hard to work with. For example, we have to leave the db Primary Key (and various other miscellaneous fields) unencrypted to be able to target individual records later (e.g. after a payment gateway returns a transaction status to the server). So it's equally a pain in the butt and lengthens development time. I would like to see some sort of public/private key scheme be integrated into Sql Server. How that would look exactly, I'm unsure.

What's the threat model, and other questions

[BritGeek]

I hate to sound like a harpy about this, but basically we have no idea if this will add any real security at all. "The Devil's in the details"TM.

The obvious questions are:

1. Are they trying to protect against a bad guy who has hacked the database server, and has disk level access to that box, but who has no application level credentials to accessing the data via the database?
2. Or, are they trying to protect against a bad guy who has hacked an application server? In which case, said BadGuy presumably has a valid userid/password to retrieve data using boring but powerful queries such as "SELECT * FROM CUST-TABLE".
3. Or, are they doing some nifty code signing thingy so that, unless the query is executed from a previously signed application, the query won't return plain text data.

Of course, there are other interesting questions here. Do they propose to encrypt the data on a row-by-row basis, in which case multi-row operations become exceedingly "interesting"? Do they propose to simply encrypt an entire table? How many keys will there be? Will you be able to rotate keys? If you can rotate keys, what happens to data encrypted under the old keys?

So many questions, so few answers!

Re:MS is ahead of Open Source on encryption

[Hiro Antagonist]

Wow, you certainly haven't kept up with the times. First off, insulting the guys at GPG because they want fair use of THEIR work isn't much of a point; after all, it's not like Microsoft lets anybody in the world use their code under any terms. Second, GPG does provide a highly secure way of encrypting email and documents which is compatible, by and large, with traditional PGP. It's a good system.

Linux has supported encrypted filesystems for some time now; I've been using them for about a year. You can even encrypt your swapspace if you like.

Both MySQL and PostgreSQL can support encryption; it takes extra work, but so does any secure system.

Linux doesn't yet support encrypted binaries, true, but this is probably due to an overall lack-of-need rather than a lack-of-capability; Windows needs signed binaries because it tends to let anybody run software on a system, thanks to security holes. But then again, the source is there, so if you don't like the situation, write a patch. Or pay someone to write a patch. I'll wager that it'd be cheaper than a SQL server license.

Linux does support encrypted authentication; ever heard of 'Kerberos'? It's the same system that Windows 2000 started using a few years back, and Unix has had it for decades. IPsec on linux is a bit of a pain to set up with FreeS/WAN, yes, but 2.6 uses Kame, which is easier, and the Linux implementations have much better debugging features.

Linux does have its faults, don't get me wrong; the FS encryption could be better, and I wouldn't mind seeing encrypted binaries myself. But it's still far better than anything Microsoft has to offer.

Linux

Re:MS is ahead of Open Source on encryption

[pc486]

I don't know much about databases, VPNs, encrypted filesytems and such, but this post is plain blither.

The Open Source movement loves to talk about encryption and security, but it's all talk. Is there an open source email encryption protocol, which is implemented under a license which allows it to be linked in to all kinds of software? No, there's gpg, which is under GPL, which means it can only be used in other GPL software. Anyway, the author, Werner Koch, is so confused about security that he thinks that making it as a linkable library would somehow compromise security. D'ohh! Do any of the standard Linux filesystems (ext2, ext3, ReiserFS) support encryption? No. There are clunky loopback kludges you can wrap over them, but they have the drawback of being clunky kludge wrappers. If you want encryption, it needs to be done at the application layer. Given that this thread is about databases, how do Postgres and MySQL fare in that department? Can either of them produce PGP-signed database results? No (that gpg again). Can either Postgres or MySQL store data in encrypted formats? No again, unless it is implemented at the application layer.

1. Loopbacks can be "clunky" but they allow seperation of the encryption and the filesystem. I don't care about encrypting my discs, but that doesn't make it so encryption is unavailable for others to use. Plus, there is no way a new encrypted filesystem should get into the main Linux trunk any time soon. Why? Filesystems are critical to system stability. If the filesystem gets corrupted, the system is gone. Any new filesystem, encrypted or not, should have much testing done before it gets including in the main trunk.

2. MySQL support AES for table encryption and SSL for link encryption. This is far more than good enough for a database, considering that encryption isn't security (google for SQL insertion attacks). Besides, table data signing should belong at the application layer.

Ok, how about encryption on the network? Here we have some things to look up to. We have OpenSSL which is perfectly integrated into the Apache 2 server and a bunch of other places. That's good. We have OpenSSH which is effective, but somewhat brain-dead in that it provides a tunnel mechanism, but only so long as you keep a console open! D'ohh! Mercifully, Linux does have good ipsec support for tunneling.

OpenSSL is BSD, so your previous GPL argument goes out the window. It serves us well. Also, SSH for tunneling should be used for just that. There are many ways to make this work (look at the -N option) and there are a few applications where it is stupid or overkill to use SSH for tunneling. Use Stunnel instead (a generic SSL wrapper for TCP applications). Use the right tool for the job, silly.

Now let's look at other features in the Linux kernel. It has modes for running signed or encrypted ELF files, right? Wrong! Plain old plain-text should be good enough! Did someone forget support for accessing encrypted files? Guess so.

Accessing encrypted files is at the filesystem layer (which we already visited). Encrypted executables make no sense. Signed ones do, though, and that seems like a cool feature. I do not know if Linux can do executable signing at runtime or not.

Ok, but we must be doing better at the authentication level, right? Wrong! You get your choice of plain old passwords, s/key or RSA keys, and that's it. Tokens? We don't need no stinking tokens apparently.

Last I checked, Linux has support for many authentication models. I believe the authentication application is called Pluggable Authentication Modules (PAM).

Re:MS is ahead of Open Source on encryption

[imroy]

• Loop-back encryption is kinda clunky. dm-crypt looks to be a cleaner way to do encrypted devices. And pam_mount can mount encrypted home directories on login.
• As for doing encryption in the filsystem, several people are at working at it.
• Your notion that OpenSSH only creates a tunnel while the "console" is open, is little more than FUD. Oh no! The console!. That's the whole point. SSH is largely interactive by its very nature.
• It's quite easy to setup OpenSSL in inetd mode for SSL'd services.
• Encrypted executables? Are you joking? WTF would that achieve? If someone has physical access to your machine, you're screwed anyway. And if someone has broken into your machine remotely then your executables are probably the last thing to worry about. On Unix/Linux systems you need root access to write to system executables. If an intruder has root access, they can do anything and don't need to modify your executable to screw around. This is a straw-man argument.
• Linux is very good as a VPN router. Not only do we have IPsec/IPV6 from the KAME project, there's also the (abandoned) FreeS/WAN project and the spin-off Openswan. But don't forget OpenVPN (available for quite a few platforms, not just Unix/Linux). If you're really desperate, you can always combine SSH and PPP to make a VPN.
• Tokens? You have heard of Kerberos haven't you?
  BTW, here's a good LDAPv3+SASL+KerberosV HowTo

My god you are a troll. Oh, and as others have pointed out, encryption does not instantly make something secure.

Embrace.. extend..

[RenHoek]

Am I the only one that thinks this is just a method to lock out open source software? Is anybody keeping an eye open at the pattent office site for any new trivial encryption patents?

Much more than just a patch

[billstewart]

Sorry, but you haven't stopped the smart ass remarks yet :-)

"Security" isn't just something you fix with a bandaid, unlike "Security holes" which can often be fixed that way. Right now if you don't want crackers cracking into your databases, don't let them onto your database server box. SSL is a bit of a step up, because it gives you more granularity about who can do what once they're there, but it's still not the issue here. Storing the *entire* database encrypted with a single key that is known by the object that lets people access data is a bit more than a bandaid -- maybe it's an arm sling, but it's still an external issue.

Real database security is a major redesign - protecting against people who ask nicely is one thing, but designing the database system so that each data item owner's private data is encrypted with their own keys and shared fields are encrypted with shared keys and reading the raw disk instead of using the DBMS interfaces just gets you cyphertext is much more than external patches. Furthermore, it affects the users' interaction with the database, because now they've got to define which items should be visible to which users and manage the keys they use for that access.

Re:Its MSFT bashing time...

[pikkumyy]

Foreign keys are un-American.

The future's so bright, I avert my eyes

[Zoop]

XPSP2 assuming it ever comes out

MSSQL 2005 security will probably be very good

I think you can understand why longtime Microsoft watchers will be kind of unimpressed by this sort of thing. We've heard it before. Sure, this may be the time (pretty much the first) that MS actually does what it says to the level that reasonable people expect, but positive statements about Microsoft products have historically been in the future tense.

"Windows 3.0 will make the Mac look hard to use."

"Windows 94 will be modern and stable and make the Mac look hard to use."

"Windows 97 will be modern and stable and integrate the Internet, and it will be as easy to use as a Mac."

"NT will be stable and crashless."

"NT 3 will be stable and crashless."

"NT 5 will be stable, secure, and crashless, and will be as easy to use as a Mac."

"XP will be stable, secure, and as easy to use as a Mac."

Every time I hear "but this time, Microsoft will get X right," the consensus after it comes out that Microsoft got X about 50% or not at all, and there are really serious drawbacks.

It's not like Linux ("KDE/Gnome/Eazel's new release will be as easy to use as Windows" or "the new Debian/Red Hat/Mandrake will be as easy to install as Windows") or Apple ("Mac OS X will be a gamer's dream platform" or "Copland will be modern, stable, and crashless, and will actually ship" or "Security update 05-24-2004 fixes the URL vulnerability") are immune, but they do get to point out areas where they excel currently rather than continually point at the next release.

It may be that this time, Microsoft will Get It about security. But you'll forgive the rest of us if we don't get too excited until we actually see the things in operation.

No need for SQL to be exposed to the web

[JazzXP]

I'm sorry, but you're an idiot if you expose your SQL server to the web, there's no need that I can think of to do it. Your webserver should be on a seperate box that is exposed to the web, whereas the SQL server should only be visable to internal networks... is this new encryption really necessary?