Hello,
is there a way to say to SQL Server to make the data not readable?
Regards
Markus
hi Markus,
what do you mean by that?
SQL Server database are not flat files that can be "opened" with notepad or the like to see what's in the database..
or.. you can open it with notepad, but what you get is not what you usually get in a standard text file.. you can sometime "guess" what's in it, for character based data ...
SQL Server zeroes out the file when creating it so that it does not containt file system trash, but it then dumps to the file all required file information to define it's proprietary format... binary data is difficult to be found, but character based data can be scanned...
if you require such a level of protection at NTFS level, you should consider encrypting your data..
regards
|||
Hello Andrea,
i mean that every private data which a user stores with my program can be seen by every other user of the computer. Even if i secure the access to the program with a password. Others just need to open the .mdf file with Notepad and can see all data when they scroll down a bit.
For example: A home user stores private data with my program and his childs can see the data with notepad, even if they don't know the password of my software. Or a hardware technician to whom the user brings the computer to repair something.
I don't understand the information in the link. What must i do exactly to encrypt the data? I would like to see a property of the database, like encrypted, so that character based data cannot be read any more. So it would be no more afford than setting a property. I really don't know why microsoft don't give us such a property. Even better would it be if it is encrypted by default.
I think in Access it is that way, when you add a password to the database, then automaticly the data of the mdb file cannot be read with notepad. When SQL Server Express should replace Access.mdbs then this feature should be added to SQL Server Express.
Regards, Markus
|||
hi Markus,
as in the "old days", usually "databases" are not "reserved" that way... yes, they have to be secure, and they are as long as the server hosting the database is secure, and SQL Server complies the same as Postgres and the like.. if you have a server "patrolled" in a security checked room, where only reliable IT persons can access the "physical files", you are ok...
now databases are everywhere, even on laptops and I understand the problem... and SQL Server now introduced the encripting support at the engine level.. you have to read and study a bit about that, becouse it's not like Access where you just have a "password" to protect the access to the data (even if you can open a JET database with notepad as well, and you'll find the data is not obfuscated)..
the encription hyerarchy is a set of tools you can use to protect your data.. read further at http://blogs.msdn.com/lcris/default.aspx
regards
|||
Hello Andrea,
yes you are right. The data in a .mdb file is not obfuscated even if a password is used. But it is in Word Files. And in Outlook encryption can be chosen as a property of the outlook file.
I have only read a few lines of your link. I don't think that this is what i want. Because it seems to have bad effect on performance and i don't know what effect it has on queries. It is simply to much work cost.
I think the encryption or obfuscation should be a task of the database and not a task of the programmer. That encryption can be choosen as a property, like in Outlook, is just missing in SQL Server. It's a mistake. Perhaps the administrator of the fourm can give this as a suggestion to the SQL Server development team. I think many users woud appreciate this.
Regards Markus
|||
And as if by magic...
We are investigating various ways to extend encryption for the next release of SQL Server. I don't think it will be as easy as "check a box and it's done" but it will be another step in the right direction. Encryption will always have a performance impact, you don't get one without the other. Suggesting that you don't want to use the current encryption becuase of performance problems, but then saying that you'd rather just have the whole database encrypted is a conflicting statement. If you're concerned about the perf impact of encrypting a few columns, I can't imagine you'll be happy about the whole database.
Now having said that we are researching encryption, I'll also have to say that it's possible that the work being done won't show up in SQL Express. It's a topic under discussion, but encryption is generally considered a significant value add to the product, which you may have to pay for.
As for Jet, it supports something called "encoding" which isn't as strong as encryption. When you encode a database, it does obfuscate the data so that it is not readable in notepad, but the encoding algorithm isn't very complex and a motivated hacker would be able to break it. (How do you think all the data recovery services recover your data from an encoded database?) I don't know for certain, but I'm fairly confident that Word files are obscured in a similar way. I've no idea for Outlook, but since you don't supply a certificate or encryption key, it must always be handled using a common algorithm, which probaly makes it easier to break.
As many threads have indicated, encryption is a complex topic with many different sets of requirements from different users. We're continuing to work on it.
Mike
|||
Hello Mike,
you speak about a very safe security mechanism which costs much work. I speak about, that it would be good to prevent that everyone is able to read the data with notepad. With no extra work.
I think there are more than 2 possibilities: Nothing (as it is now) or the strong encryption mechanism you describe.
I don't mean a security mechanism which is 99 Percent safe. It is enough if it is 80 Percent safe. Means obfuscation is enough. This should be a option that should be in every SQL Server Version and should not cost extra. And it is good when the user must supply an encryption key. For this encoding mechanism it is ok, when a motivated hacker can brake it. But he must do the work.
Your strong encryption mechanism could cost extra. But this are DIFFERENT things.
I don't think that obfuscation has much effect on performance. If SQL Server works internally with the obfuscated data. Means it only converts the data in the SQL statement to the internal obfuscated data and the output data to the readable format.
Even if a NTFS partition is used to restrict direct access of physical files there are backups. For example a detached .mdf on a cd or in the internet. If no obfuscation is used, everyone who gets access to this backup can read the data with notepad.
And i don't know: What is if someone takes out the harddisk on a computer. Installs it as a secondary harddisk on his own computer where he works as administrator. Can he then read the data on the NTFS partition with notepad?
Another advantage of obfuscation would be to prevent that unauthorized users that get a .mdf file or a backup can attach or restore this at their own server if they don't know the encryption key used.
Regards Markus
|||
hi Markus,
I understand your topic, but security is a whole "platform" consideration...
security is a set of implementations, first of all restricted access to the file as well... so the physical file should not be arbitrarly available on cd or (
) internet... but I understand the issues, where a notebook can be stolen or the like, and sensible information are that way compromised... but again, obfuscation/encription and performance are, as Mike already stated, not good friends and you obviously get both..
regards
|||
Hello Andrea,
> I understand your topic, but security is a whole "platform" consideration...
> security is a set of implementations, first of all restricted access to the file as well...
Yes. But I'm speaking also for the home user or for small companies. They mostly don't have pleasure, time or money to invest in a security platform. They just want to install the software. Work with it and want the certainty that their private, secret data is stored in a SUFFICIANT secure manner. And not in such a unsecure manner as it is today.
> so the physical file should not be arbitrarly available on cd or () internet...
ahem. Where else should a home user store the backup files? Ok, he can use a software to encrypt the backup files. But this is a extra step which is not liked and would not be done very often.
And i think the internet (some space on an own home page) is not such a bad place to store a backup. It is safe when there is a fire at home or water or some other accident. And there is no cd at home which can be stolen.
> but again, obfuscation/encription and performance are, as Mike already stated, not good friends and you obviously get both..
Ok. But let's say that today new computers are so fast, that they can give us both. And obfuscation can be an option. So that it can be taken off, when full performance is needed.
I think this obfuscation would be an good option for SQL Server Express. Which makes it a better and more secure product for many purposes.
Regards Markus
|||
hi Markus,
 Markus Sch. wrote: |
|
| > so the physical file should not be arbitrarly available on cd or () internet... ahem. Where else should a home user store the backup files? Ok, he can use a software to encrypt the backup files. But this is a extra step which is not liked and would not be done very often.
And i think the internet (some space on an own home page) is not such a bad place to store a backup. It is safe when there is a fire at home or water or some other accident. And there is no cd at home which can be stolen. |
mmhhhh... not quite sure an internet space grants that required privateness... obviously it depends on the actual data, but for sure, regarding personal data (and not sensitive like medical, politicians and the like, but just an addresses database) it's for sure not legitimated by italian laws about privacy... european laws are very "heavy" as well, and I do think US government is not that permissive too..
|
> but again, obfuscation/encription and performance are, as Mike already stated, not good friends and you obviously get both.. Ok. But let's say that today new computers are so fast, that they can give us both. And obfuscation can be an option. So that it can be taken off, when full performance is needed. I think this obfuscation would be an good option for SQL Server Express. Which makes it a better and more secure product for many purposes. |
a kind of path actually exists... it's not simple as clicking a checkbox, as Mike stated... but it's reliable and robust... and performance considerations should be not an option if you require that kind of security.. but you can usually get decent performance considering the scenario SQLExpress and your environment description are designed to... in this case quite single access...
regards
|||
To a certain level, SQL Express will continue to have a "Server-centric" view of the world, including security. The simple fact is that SQL Express is SQL Server, and organizationally we have to continue to develop for the community of SQL Server users. Some of the things that would make sense to do for the Desktop environment don't always make sense for Servers, and I'm often constrained by what needs to be done for the Server.
That said, I always pay attention to the ideas and requests from this form and use them to make plans for future versions. (That's why I created the forum in the first place, so I could hear from you folks.) This is a great discussion and I'm taking notes.
One of the ways that we're trying to address some of the needs of the Desktop application is with SQL Compact Edition. SQL CE uses a different security model that is based on a file level password. Since the password can be compiled into your application, you never have to give it to your users, so the file can not be accessed outside of an application. (OK, there are always hackers, but that's a separate issue) SQL CE is directly targeted for use in storing local data on the desktop, it is not designed for use as a server for multi-user applications, you would still use SQL Server for that. I don't know what, if any, levels of obfuscation of data is offered by SQL CE, but I'm sure the guys in the SQL CE forum would know. There are some functionality differences, but often you don't need the extra server functionality for a local data application anyway.
It's certainly worth looking into. Check out the forum for more information.
Mike
|||
Hello Mike,
CE is not a solution for me because i need a database designed for multi-user access.
Markus
|||
| Mike Wachal - MSFT wrote: |
|
| To a certain level, SQL Express will continue to have a "Server-centric" view of the world, including security. The simple fact is that SQL Express is SQL Server, and organizationally we have to continue to develop for the community of SQL Server users. Some of the things that would make sense to do for the Desktop environment don't always make sense for Servers, and I'm often constrained by what needs to be done for the Server.
|
thank you, Mike 
regards
|||
But SQL Server Express is for Desktop Environment?!?!!!!
And why should an optional obfuscation (that may be set off by Default and must be explicitly set on) be bad for the community?
Markus
|||
hi Markus,
I will obviously answer for me and not for Microsoft, so all considerations are IMVHO...
and... not to flame, but just considerations...
SQLExpress is not for Desktop Environment..
quoting from http://www.microsoft.com/sql/editions/express/default.mspx,
"SQL Server 2005 Express Edition is the next version of MSDE and is a free, easy-to-use, lightweight, and embeddable version of SQL Server 2005. Free to download, free to redistribute, free to embed, and easy for new developers to use immediately, SQL Server Express Edition includes powerful features such as SQL Server 2005 Reporting Services, a server-based platform for creating and delivering traditional and interactive reports, and a graphical management tool, SQL Server 2005 Management Studio Express, for easily managing your database. Best of all, as your needs grow, your applications will seamlessly work with the rest of the SQL Server product family."
it can be (obviously?) be target to Desktop environments but this is not it's only destination of use, so it's just a different edition of SQL Server, starting form Enterprise edition, down to Express... and they all share the same core binaries... modifying the Express edition with "personal stuffs" will make it no longer adressable that way as "based on SQL Server xxxx binaries"...
the "upgrade" path from edition to edition should be smooth as it currently is.. no "technical changes" between editions but major features support (more memory, more cpus, clustering features, additonal tools, ..)
there's plenty of current customers with Express based solutions that will be sometime migrate to Standard..
on the contrary CE or Mobile edition is a complete different "beast"... again, quoting Microsoft web site:
"SQL Server 2005 Compact Edition is the next version of SQL Server Mobile, adding the desktop platform to enable users with a new generation of occasionally-connected dynamic applications. SQL Server 2005 Compact Edition Release Candidate 1 will be made available with the official release in December 2006"
here I explicitely see "desktop" word...
Even if I understand your requirements, personally I'd not like to see the Express edition, which has been vastly used in little scenarios, small business, and the like, to be downsized to a pure "desktop" engine... and you shold not either, as you previously stated
>CE is not a solution for me because i need a database designed for multi-user access.
this requirement seems to me little contradictory with the pure "desktop" design... you need a "full" multiuser database engine as SQLExpress is..
so my "expectation" is: SQLExpress should maintain the current path, aligned with the "full blown" editions... if they all will sometime include a different storage defition, that's all right with me.. but I'd not like to see a split between editions...
again, all this not to flame and IMVHO
regards
) internet... 
Markus Sch. wrote:
and you will find). It is probably not suppo