DB is created by scripts/installs and it copied into the users C directory. On that db I enabled TDE with Manual mode. When I try to run that db using prowin32.exe it successfully able to connect without asking for -Passpharse.
On the other hand if I try to run it using proserve commend it is asking me for -Passpharse.
Also If I create db using DataDictinary or prodb those are working fine and asking -Passphrase for manual encryption. I am not sure if TDE has limitations based on how we have created DB?
On QA\PROD environments we use prowin32.exe to run db and application. I am not sure what we are missing here.
Can someone please suggest on this?
If I understand your description correctly, that is how TDE is deigned to work. TDE is meant to protect on-disk data, in the database, transaction logs, and backup files.
When you start a database server or a single-user session, you (the dba) are asked for the passphrase. Once the server has successfully authenticated against the keystore file, it makes the database available for use by clients. Clients do not have to supply the passphrase to connect to the database, only their database user credentials.
In automatic mode (less secure, but more practical for some), the server opens the keystore by itself, without asking for a passphrase.
So the interesting part here is with the installs we haven't provided any -Passphrase as this is still in research and we are planning it for use for database encryption. I have started this by creating db in dev environment and check how tde works there and it works as expected for every scenerio.
But when I tried the same thing for the DB which QA\PROD guys use, I encrypted it using manual TDE and try to connect it , it just connected without -Passphrase which is not expected.It is inside C:\ProgramFiles86. But when I copy the same db files into some other directory and encrypting it, it ask -Passphrase which is STRANGE to me. I don't know what problem we have when DB is inside ProgramFiles86 folder.
Guys, this looks like issue with my Machine or DB. Same scenerio working on other machines.
Just one query how we can pass -Passphrase password with prowin32.exe command. We don't want user to prompt for it we want it to by default provided with prowin32.exe command which user use to run db and application.