Slashdot Mirror
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."
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.
/^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$/i
will be 12345. Same as the one on Bill Gates' luggage.
Encryption is not security....
...
Encryption is not security....
Encryption is not security....
Encryption is not security....
Go ahead, slashdotters.. they mentioned MSFT!.. .Quick .. get your pitchforks and light the torches!
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.
Be very, very careful what you put into that head, because you will never, ever get it out. -Thomas Cardinal Wolsey
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.
The dangers of knowledge trigger emotional distress in human beings.
I don't have to work in SQL very often so in those times that I do I reply quite heavily on the MSDN API listings of the T-SQL commands. I'm mostly just looking for the syntax and maybe a couple of examples since I'm not doing anything difficult here.
Today I went to look up something and have found that the MSDN has turned into a giant advertisement for SQL server 2005 and if the useful information is still there it's buried.
It's really sad that today I looked up some syntax on the mySQL site and prayed it was the same on MSSQL.
I can completely understand why my customers don't like it when we change the layout of screens now.
future ms sql internet worms will travel encrypted?
The Acme Safe Co. announced today that next year's model will feature a "lock" to prevent unauthorized persons from accessing the contents.
Unknown host pong.
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...
v'ir nyernql penpxrq vg!
vodka, straight up, thank you!
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.
Pedro
----
The Insomniac Coder
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.
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.
Bill Stewart
New Fast-Compression-only CPR http://preview.tinyurl.com/dy575ks
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?
This reminds me a lot of the TruStore project which has been around for about 9 months. They have been working to implement seamless encryption into MS Sql server. I believe they even have betas available.
Natural Selection: self-destruction of the poor and lazy
just what I needed is another resource-sucking feature added to SQL server so I can give even more money to server vendors!
But seriously, can anyone guess if on-the-fly encryption will seriously impact a MS SQL 2K box? Do people see an impact on their MySQL boxes? I know it's not a very valid comparsion but I'm just trying to get an idea for future server scaling.
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.
As seen on Wired: Get a free desktop PC
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.
01100111 01100101 01110100 00100000 01101111 01110101 01110100 00100000 01101101 01101111 01110010 01100101 00101110
so long as the option still exists to use third party security products, i think its a good move. other databases have it, why shouldnt they?
and i seriously doubt microsoft would be able to figure out how to make it so that no third party encryption works with their database, nor would they want to, as their primary agenda right now is clearly security.
01100111 01100101 01110100 00100000 01101111 01110101 01110100 00100000 01101101 01101111 01110010 01100101 00101110
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?
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.
Premature optimization is the root of all evil
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?
-- Don't Tase me, bro!
They say "Common Criteria" - is the encryption also FIPS140-2 ????
Finally!
Sincerely,
Pan Tarhei Hosé, PhD.
"Homo sum et cogito ergo odi profanum vulgus et libido."
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.
Error 408 - Request Timeout
Your transaction was canceled waiting for the server operator to enter the database password. Please try again when the operator is back from lunch.
It does not really matter if the data is encrypted or not. Whatever agent is accessing the data has the password, and if it is compromised, the data is also compromised. Add to that, the encryption credentials must be stored somewhere, in which case it is vulnerable, or entered manually by an operator, in which case rebooting your webserver just got that much more entertaining.
-Hope
The obvious questions are:
- 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?
- 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".
- 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!
"The time is always now" - Victor
How true...
(Note to self: remove encryption ASAP!)
Sincerely,
Pan Tarhei Hosé, PhD.
"Homo sum et cogito ergo odi profanum vulgus et libido."
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
--
I Hit the Karma Cap, and All I Got Was This Lousy
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).
That is certainly a valid point. It would be nice if they could license something from RSA...
Come to think about it, how are they going to get around the law prohibiting the export of encryption out of the states? I suppose they'll need to ship a copy without encryption to overseas customers. In which case, international corporations may have compatibility issues...
01100111 01100101 01110100 00100000 01101111 01110101 01110100 00100000 01101101 01101111 01110010 01100101 00101110
yes, but what is so annoying about his post is that it got modded up to +4 interesting and none of the rebuttals got modded up at all. This means that most people cruising at +3 or above won't see the replies.
Donald 'Duck' Dunn: We had a band powerful enough to turn goat piss into gasoline.
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.
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?
"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.
Bill Stewart
New Fast-Compression-only CPR http://preview.tinyurl.com/dy575ks
Foreign keys are un-American.
Foreign keys are un-American
No they arent, they are just not British
Old COBOL programmers never die. They just code in C.
And Firebird?
e et.html
;)
I must admit it doesn't look like it has encryption (yet?), but it has everything else you mentioned, and it has SPEED.
And, Slashdotters, it's Open Source. So run up the flags for it! Why is everybody, but everybody, talking about MySQL when Firebird is just as free, has a LOT more funtions (the transaction handling is great), and it's FAST.
We use Firebird in a rather serious business environment, and have been very happy with it.
Have a look at http://www.firebirdsql.org/ff/foundation/FBFactsh
Although, I'll admit, encryption? Aaaah, bah, that's just bloat
Free PC version of ChipWits at http://www.breueronline.de/klaus/chipwits/
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.
The application tier, in contrast, is much more scalable. Clustering application components is tantamount to creating lots of digital workers. You can punch out a theoretical unlimitted number of workers, but your bottleneck are the resources used as inputs and outputs in your process... the data. You need one computer to hold the "master copy", or authority, of any piece of data.
It's very likely that since SQL Server doesn't run on the mainframe, and isn't easily scaled in most production environments today, that businesses will use this only for very essential requirements, such as social security, credit card numbers and passwords. In its logical progression, some sort of hardware acceleration option will be required to ensure this can be used on a larger scale without impacting the performance of the database server to do its basic tasks.
Open Standards Portal
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?
One point I haven't seen raised yet is that this is very useful where you send your app out to customers with MSDE (cut-down SQL Server) and a ready-to-use database in the bundle.
Having the whole thing encrypted stops competitors taking your 'business logic' (in your stored procedures) home for bedtime reading. If you keep some stuff unavailable until they buy licenses for it, you can stop them seeing how to 'switch it on' , too.
Rik
... doesn't mean we shouldn't.
"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..."
Kudos to Microsoft for working to secure their products at a lower level. Their "new" strategy is certainly better than their previous habit of releasing swiss cheese and then issuing corks (once a month) to plug the holes. However, their track record in the security arena gives one a reason to consider spending the extra $$ with a proven provider of security products when the situation depends upon it. Even if Microsoft's built-in functionality is stellar, the concept of Defense-in-Depth tells us that we may still need to "...procure security features from a third party..."
In the words of [my hero] Bruce Schneier: "Strong cryptography is very powerful when it is done right, but it is not a panacea. Focusing on the cryptographic algorithms while ignoring other aspects of security is like defending your house not by building a fence around it, but by putting an immense stake into the ground and hoping that the adversary runs right into it."
I am struggling to see the benefit of this level of encryption.
If you are going to deploy the encrypted data into an untrusted location then you have a huge problem. If the data needs to be there in the first place then it must be unencrypted in order to be acted upon and then it is vulnerable anyway.
If you are going to deploy the encrypted data to a trudted location via an untrusted channel then a better solution is to encrypt the channel.
If you are going to store data from third parties in a central location and encrypt it to prevent unauthorised access then just let the third party submit encrypted data, however the RDBMS cannot use its RDBness on the data since it is encrypted.
If you are going to store third party data and act upon it then you have to decrpyt it, therefor have the keys, therefore the database itself is trusted, therefore just use access control rather than encryption. Encryption is 100% overhead.
I think this kind of proposal is 100% buzword compliance with no benefit whatsoever. The occasion where we encrypted rows in a table, we found the performance of the system was slaughtered and we were completely memory resident and used caching to ensure that we minimised the encryptions during a given transaction. Secondly we found that in the circumstances where we had some sensitive data that was needed on the client side to do calculations that are expensive, we had to reveal some aspect of the data in order to make it work and I am sure this will be true in any case. If you need to use the data, you need to decrypt it. We even thought about building an API that would implement a bunch of accessors that would return results based on the hidden data, but it was then that we had to reveal the common attributes of individual instances of the data. So instead we had to do it in the trusted environment.
What do all these experiences show? That if the client isn't trusted then there is no point encrypting. Which perhaps reveals Microsofts motive... to provide another lockout for those who do not subscribe to their trusted computing initiative!
"The first thing to do when you find yourself in a hole is stop digging."
The concept of "abstraction layers" is associated with Windows, but don't forget, non-toy operating systems also have their own forms of abstraction layer, just as a result of their own modularity. For instance, in Linux, the entire file system is effectively abstracted. You can write a kernel module for a new file system type and drop it in seamlessly. The same probably holds true for other OSes, maybe even in Windows if you can get the sources; but I'll concentrate on Linux for now because it's what I know.
/var partition, then you would have encryption for your database -- independent of whatever kind of database you were using. And it's stuff-all use to anyone who steals the drive (unless the decryption key is on the same drive; but it's not, is it. You're smarter than that).
If you used such a kernel module to give you an encrypted file system, and used that fs type to mount your
If you want encryption between client and server, you can use OpenSSL. Of course, if you are accessing the database through a web-based app, then just use an SSL-aware version of Apache. It'll be unencrypted on the client-end PC, but presumably that's inside a locked office building.
Je fume. Tu fumes. Nous fûmes!