Sql-server – sql server management studio enable encryption

I was able to move the Java code to Oracle. I am writing this for people who are facing similar issue.

1. Log on to server where Oracle database is installed through Putty (command prompt in case of windows)
2. Place the Java file in server using WinSCP.

3. Find the Java compiler present in oracle home using command

   find / -name javac
   

4. Compile the Java file using Oracle's Java compiler as the Java version used by the database and Java version usually used will be different

   $ORACLE_HOME/jdk/bin/javac /home/vikram/EncrUtil.java
   

   Note: Instead of compiling on the database server, a .class or .jar file can be directly loaded into the database. But make sure to use the same version of Java as used by the database.

   To find the java version used by oracle

   $ORACLE_HOME/jdk/bin/java -version
   java version "1.5.0_17"
   Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_17-b03)
   Java HotSpot(TM) 64-Bit Server VM (build 1.5.0_17-b03, mixed mode)
   

5. Set ORACLE_HOME if it is not set

   export ORACLE_HOME=/database/ora11gr2/product/11.2.0/dbhome_1/
   

6. Add loadjava's directory in the path (that's the database's bin directory)

   export PATH=$PATH:$ORACLE_HOME/bin
   

   Note: In case class not found exception is found go through “loadjava” and check if path present is properly set or variables are defined is not define those

7. Load the class into the database

   loadjava -user <user>/<password>@xxx.xxx.xxx.xxxx:1521:<instance> \
            -thin –resolve /home/vikram/ EncrUtil.class
   

   • -resolve is used so that .class file can be resolved before loading into the database as its easy to debug Java outside database.
   • -thin is used for thin client

8. Once the class is loaded, create a wrapper function on top it, so that it can be used in database

   CREATE OR REPLACE FUNCTION TEST_DECRYPTOR(indata IN VARCHAR2)
   RETURN VARCHAR2 AS
   LANGUAGE JAVA NAME ' EncrUtil. decrypt (java.lang.String) return java.lang.String';
   CREATE OR REPLACE FUNCTION TEST_ENCRYPTOR (indata IN VARCHAR2)
   RETURN VARCHAR2 AS
   LANGUAGE JAVA NAME ' EncrUtil. encrypt (java.lang.String) return java.lang.String';
   

9. This function can then be used just like any other Oracle function:

   SELECT TEST_ENCRYPTOR('Vikram') FROM DUAL;
   

The proper key hierarchy is the Service Master Key protects the Database Master Key, the Database Master Key protects either an asymmetric key or certificate, the asymmetric key or certificate can be used for encryption or can be used to protect a symmetric key which is used for encryption. The keys are created in that order. The system creates and protects the SMK, and the DMK and subsequent keys are created by the user.

The design of the key hierarchy in SQL Server protects both the data and keys from compromise. Remember that with a symmetric key, the same key is used to encrypt and decrypt data, or in this case other keys. The main purpose of introducing an asymmetric key or certificate into the hierarchy protected by the Database Master Key is to prevent an attack on the DMK and SMK from inside of the database. If someone malicious wanted to obtain the DMK unencrypted, then that person would need to do one of two things. Either decrypt the service master key and use it to decrypt the DMK, which is not possible because only the database engine can use the Data Protection API to do this, or attempt to decrypt from the top of the hierarchy down, which would be possible if there were not an asymmetric key or certificate in the way.

All of the signed or encrypted bits of the symmetric keys in SQL Server are in a system table named sys.crypt_properties in each database, including the encryption of the Service Master Key in the master database. There is no system table that contains the private key for either of the asymmetric key types. If all keys in a hierarchy were symmetric keys, then the SMK would encrypt the DMK and the DMK would encrypt the symmetric key that would encrypt the data. Because of the way that symmetric keys work, that would also mean that, if someone opens the symmetric key for the data, then it can theoretically decrypt the DMK and the decrypted DMK can be saved by a malicious user or used to decrypt the SMK because the same key is used for encryption and decryption. This is why an asymmetric key or certificate is required to be an integrated part of the encryption hierarchy.