Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sandesh_moghe
Constraint Violating Yak Guru
310 Posts |
 Posted - 2002-01-25 : 04:21:22
|
| Hi,Can I Encrypt the Data from a Table?If Ans = 'Yes' How?Thanx in Advance.---------------------------Sandesh - The Messanger |
|
|
smccreadie
Aged Yak Warrior
505 Posts |
Posted - 2002-01-25 : 06:56:48
|
| SQL does offer an encryption function (see BOL - I can't remember the name) but it is not guarenteed to be the same version to version. There are 3rd party COM or Java tools that can be used as well. |
 |
|
|
sandesh_moghe
Constraint Violating Yak Guru
310 Posts |
Posted - 2002-01-25 : 07:23:32
|
| Yes, you are 100% right,Using Encryption function we can encrypt Store procedure, but my question is can we encrypt the Data from table?---------------------------Sandesh - The Messanger |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-01-25 : 09:11:46
|
| There is an 3rd party extended stored procedure called xp_crypto that you could add to your server... Never used it myself, but sounds like it should do what you need.Check out this site: http://www.informnavigator.com/index.asp |
|
|
|
sandesh_moghe
Constraint Violating Yak Guru
310 Posts |
Posted - 2002-01-25 : 09:43:31
|
| Let me explain the situation.I have an application and we are licensing it on number of users logged in.How can i restrict it.In database i have User table where i have all the users for application.We can restrict it from front end, but if some one directly enters the data, then it failsso i want to encrypt data from User table, means no one can open that table (Even sa also)Is it possible?Thanx in Advance.---------------------------Sandesh - The Messanger |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-01-25 : 10:02:21
|
| Encrypting the entire table would probably be a bad idea for performance reasons. I doubt that you could restrict everyone, including sa, from that table because you'd never been ableto add new users, or even change their password.If your licensing says "You can have X users logged into the system at once" , this is how i'd do it.Have a table that stores who's currently logged in. I'd have another table that has 1 field that stores the encrypted license. I'd put other fields with other bogus data in there, to throw off people trying to "steal" more licenses. The encrypted data, once decrypted, should output the number of users that are allowed to have in the system (like 100). Be sure to put other data in that same stream to make it harder to crack. So, you would run the following through an encryption program / dll"100,I am the greatest programmer the world has ever seen." You cou;d maybe put the number of licenses in the middle of that string someplace to make it a bit stronger.If you encrypt that, it's going to be a big string. If you encrypted 100, it would be pretty easy to crack methinks. Do no distribute a DLL / EXE that will encrypt the data. The only thing you shoud give them is the piece to get "100" out of that field. As far as how to encrypt the data, I'd buy a 3rd party encryption package from http://www.rsasecurity.com/ or some other 3rd party. Strong encryption is key to making this all work. Anything less than 128bit encryption isn't going to cut it.MichaelP |
|
|
|
sandesh_moghe
Constraint Violating Yak Guru
310 Posts |
Posted - 2002-01-25 : 10:27:52
|
| How a Encrypted table affects performance?can Encrypted table's data can be seen by sa?---------------------------Sandesh - The Messanger |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-01-25 : 13:32:51
|
| Encrypting the entire table wouldn't solve the problem...you only need to encrypt one column. Michael's point about performance is that it take a lot of CPU cycles to encrypt and decrypt data, and if this table is hit a lot it'll slow down performance.You can't block the sa login from accessing the table, but if the contents are encrypted then I don't see that much harm can come from it. Can't you just set the sa password to something that no one knows? (please tell me you have a password for sa, right? RIGHT?? Please tell me you're NOT using sa as your application's login, right? RIGHT??) That way no one can log in as sa (which a client should NEVER be able to do anyway) and screw with the table. You should also REVOKE all permissions to that table for all other logins.The two functions for encyption are PWDEncrypt() and PWDCompare(). You use them like this:INSERT INTO PasswordTable (Password) VALUES (PWDEncrypt('myPassword')) -- puts encrypted text into tableSELECT PWDCompare(Password, 'mypassssword') FROM PasswordTable --returns 0, no matchSELECT PWDCompare(Password, 'myPassword') FROM PasswordTable --returns 1, match[/b]You cannot decrypt a PWDEncrypt-ed value, you can only compare a plaintext value to the encrypted one. If you need to actually decrypt it you should follow Michael's advice and get a commercial encryption package like he describes.I can't help thinking that you could do this without encryption, either by using sp_configure to modify the number of user connections, or by monitoring sysprocesses and killing any SPIDs that exceed the maximum number of users allowed. |
|
|
|
|
|
|
|
|
Table Encryption.