Free Encryption
-
WOW!!! Easy and wonderful. I did not look forward to trying to build something like this. Thanks to all that are responsible. This hit the ball right out of the park for me.
Cody Pack
MCSE Windows 2000
MCDBA SQL Server 2000 -
I wouldn't recommend it. SQL Server will not know how to decrypt and run your stored procedure if you tried to encrypt it using this package. "WITH ENCRYPTION" is the best/only option available on SQL 2000.
-
IMAGE, TEXT and NTEXT data types are stored and accessed differently from other data types. Basically they are stored with a 16-byte binary pointer. This means that access to the actual data in these columns is indirect (and kludgy). Because of this indirect pointer-driven access mechanism, accessing and updating data in IMAGE, TEXT and NTEXT columns/variables is difficult, and I wasn't able to implement that ability into the stored procs.
So to answer the question, I don't have a direct way to encrypt IMAGE columns from within SQL Server. You might consider encrypting the data using an external program. The source code provided can be adapted, or the CryptoAPI library or .NET Security namespace could be used to encrypt/decrypt IMAGE/TEXT or NTEXT data in an external program, like a front-end app.
-
Do you know if SQL Express supports the key management TSQL functions? I couldn't find anything that said it didn't.
And do you think that would be an appropriate way to store the key for implementing something like this with SQL Express that would be distributed to the user, running on the desktop rather than coming off a server?
-
With SQL 2K5, native data encryption and key management are built right into T-SQL. In addition, you can access the CryptoAPI from within .NET Stored Proc code if you want to take it down a level. Those would be the preferred methods of encryption/decryption with SQL 2K5 and SQL Express. If you wanted to encrypt/decrypt strictly client side without using the T-SQL commands, you can access the CryptoAPI from inside your .NET applications as well.
-
That was originally my thought, which was to use the native data encryption, but I could never find anything that explicitly said that SQLExpress would support it. It doesn't support full text search and some other goodeis. Then I saw this 'chat':
http://www.microsoft.com/technet/community/chats/trans/sql/SQL_072804.mspx
And toward the bottom of the page there is:
Host: rajesh (Microsoft)
Q: Will the Express version support data encryption?
A: No
Did they change their minds on that since that was published? That's why I was pleasantly surprised to find your solution, very nice. With CLR support in 2005, could you code the encryption/decryption directly into a UDF without using an xproc?
-
According to Visual Studio Magazine, http://www.ftponline.com/vsm/2005_08/magazine/features/rjennings/, SQL Express supports the same native encryption tools available in SQL 2K5. I can't confirm that myself since I removed Express from my system and installed the full SQL Server 2K5 before I got a chance to test encryption.
Also you should be able to access encryption in Express via the CLR using the DPAPI or .NET Security classes from within UDF's. The main reasons I could think of to do that would be in the event that you installed a 3rd party encryption algorithm or needed to access functionality not available via the T-SQL model.
I'm considering revisiting this topic later with code that utilizes the DPAPI for SQL 2000, since it offers a lot more options and [who knows?] it might be compatible with data encrypted/decrypted via SQL 2K5 (if MS has implemented native SQL encryption like I believe they have anyway...)
-
Question: Does the AES encryption code actually do AES encryption? I have been tasked to encrypt sensative customer information to comply with federal mandates and legal statues. They have decided to do AES 256 because it offers the greates level of security. I am a bit fuzzy on how to reference and call it from stored procedure. Can you help?
-
Hi Ed,
The supplied XP's do perform AES 256 bit encryption/decryption. The code used is based directly on the algorithm published by the authors of AES/Rijndael (Joan Daemen and Vincent Rijmen). If you're using SQL Server 2000, you can call the UDFs or the XPs supplied with the article directly from within a stored procedure.
The sample code download has sample T-SQL scripts that demonstrate how to call the UDFs and the XPs. Basically the UDF's are just wrappers around the extended stored procedure calls. You can call the UDFs just like you would any other SQL Server functions.
If you're using SQL Server 2005, however, you have an advantage since you can use the built in T-SQL encryption functionality to use the AES encryption facilities built into the operating system.
-
Got it loaded and it appears that it is exactly what we were looking for. THANKS!!!!
-
We have had a problem with the encryption tool kit that I did not see in any of the other responses and replys.
For some unknown reason, SQL Server 2000 can no longer "see" or talk to or execute the extended procedures in the "master" database where they are installed. All has been fine for 3 weeks as I have been implementing encyption in the systems I support. This morning, it will not work. The errors in SQL tools say something to the effect that there has been a network problem and/or has been disconnected. I can do any sql command as long as I do not call the functions that reference the extended stored procedures, so, the connection is ok. Any ideas???
-
There was an issue with memory fragmentation for very large data sets (3 million+ rows in testing.) Make sure you have the most recent copy installed (v. 0.9), which fixes this issue. Also, I've changed the AES encryption functions to use VARBINARY data types, since SQL might dump whitespace characters from the end of a VARCHAR value.
-
We also went with the varbinary. It was the logical choice for not dropping characters as you describe. This did present us with a problem in our application which is in PowerBuilder. PB has nothing that correlates to SQL Server varbinary. We ended up doing eveything in stored procedures and calling them form the code using function call wrappers and the like. Thank you very much for the toolkit. We were getting to the point of writing it ourselves (which would have been time consuming and expensive since we were pretty clueless at the itme) or buying a package which is VERY expensive. I have added the encryption/decryption functionality to over 100 objects already and have several more to go! Again, thanks.
-
I have a question on performance. Does anyone know whether making the SQL server do the encryption work is faster than having .NET do it? This is of course assuming that the webserver has the same hardware as the SQL server.
Here is a link to an article on encryption with C#.NET
-
Hi,
I haven't done any actual performance comparisons - but I would venture a guess that the main benefit you'd get from performing the encryption on the server would be that you could cut down traffic over the wire. If you do all the encryption on the front end, then you have to do all the decryption on the front end. I can see cases of partial/pattern matches (LIKE, etc.) or range queries (BETWEEN, <, >= etc.) where querying an encrypted column on a table would need to return every single row across the wire; then it would have to decrypt every single row and compare to find matches.
If you perform encryption/decryption on the server itself, you eliminate that large data transfer for these queries. If you're not querying against encrypted columns, however; or if your queries are exact matches ( =, <> ) on the encrypted columns, then you could optimize the data transfers pretty well and this wouldn't be too much of an issue.
SQL2K5 includes built-in encryption tools, including self-signed certificates, secure symmetric and asymmetric keys and a whole host of other features. They're integrated into T-SQL and are easier to use than even the .NET front-end System.Security namespace. There should be an article on SQL2K5 T-SQL Encryption published here at SSC shortly.
Viewing 15 posts - 46 through 60 (of 153 total)
You must be logged in to reply to this topic. Login to reply