How to encrypt database in SQLServer2000, please help me, thanks

I have 2 questions:

1. How to encrypt data of database in SQL Server 2000 ? Someone can't see true data

if without password or something else.

2. If i have 2 database . All of them have same table, structure, ....How to update or insert or change in the second database when i update or insert or change in the first database automatically ?

Thank you very much.

Oops, I didn't realize you were referring to SQL Server 2000

1. Encryption is a new feature for SQL Server 2005. Prior to this, encryption on tables had to be done externally. You can create encrypted procedures, but this only encrypts the procedure itself, not any data. If you upgrade to SQL Server 2005, then you can use symmetric keys and the EncryptByKey function.

2. You can use triggers (syntax: "CREATE TRIGGER"). This has a lot of options so you may want to take a look at the Books-Online entry to create a specific trigger for your needs (this should still work for SQL Server 2000).

Hope this helps!

|||Try the undocumented functions pwdencrypt and pwdcompare in SQL SERVER 2000 for encrypting sensitive data.|||

Sorry, but IMHO this should is not a good suggestion. The pwdencrypt will be deprecated due to the new security enhancements of SQl Server 2005, so if you use it now you′ll have to redesign your code in further version. Second point is that the prcoedure (or in general the security mechanisam) is weak.

http://www.theregister.co.uk/2002/07/08/cracking_ms_sql_server_passwords/

I would suggest (as long as won′t upgrade your sql server version soon) to use an external crypto provider which can be called from your frontend application or your stored procedure) and has a much stronger encryption.

HTH, Jens Suessmeyer.

|||

I completely agree with Jens, you don't want to use the undocumented (and unsupported functions.) There are also quite a few third party, I have used Dbencrypt before: http://www.appsecinc.com/products/dbencrypt/mssql/index.shtml (they have an eval for download as well.) No, I don't work for these guys, just had to use them on a project.

(make sure you test your solution and be mindful of the increased context switches that may be thrown.) I doubt that this will be upgradable to SS2K5 either.

|||

Another reason you should not use pwdencrypt and pwdcompare is because they do not perform encryption, they perform hashing and hash comparisons.

Thanks
Laurentiu

|||

For SQL Server 2000, you can encrypt data either using a third party product or by implementing encryption and decryption functionality in extended procedures.

Thanks
Laurentiu

|||Thanks for your advices, everyone.

How to drop db and create new table

Hi all,

I have 2 questions:
1. How can I drop /remove a complete db from MSDE desktop engine?
2. After exporting my db onto MSDE server and get connected those db tables in FE (adp), how can I create a new table in the same back end db?

I know these are basic questions but since I am new to MSDE I hope the forum would bear me.

With kind regards,
Ashfaque1) DROP DATABASE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_de-dz_82lh.asp)
2) CREATE TABLE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_8g9x.asp)

Note that there are other ways using the MS-Access GUI based tools. These are pure SQL Server, and as such are more portable than other ways.

-PatP|||Thanks Pat,

No doubt the article is useful to use with SQL Server. It may also be useful for MSDE2000A server. But the problem is from where I should enter in SQL area to deal with these syntaxes. Like in Oracle/SQL you can reach to SQL prompt and put "Grant Connection, Resource, DBA To xyz indentified by xyz" something like.

I need to know in MSDE server how can I do this while I am handeling Access Database Project file?

With kind regards,
Ashfaque|||Hi

After you open your access project press CTRL+G and go to Immediate window
you can run your command immediatly from there like this

docmd.runsql "create table somthing(empid varchar(5))"|||Thanks Feras,

I got the idea to start up at least. Bcz I am new to db Project. I know only sql of mdb files.
I used followings with docmds in immediate window
Drop table, Create table, Instert, Update

They can be written in following fashion

docmd.RunSQL "insert into T_dob values(772,'Waheed');

It is ok. What about if I need to delete the record set. Bcz I tried in the same way like:

Docmd.RunSQL "Delete * From T_dob Where Empname='Waheed'';

But it show syntax error. Where might have gone wrong?

Thanks again.

Regards,
Ashfaque|||I got it again.

Thanks.
Ashfaque