Connecting to SQL Server with JDBC

All you need is 3 jar files in your classpath

• Msbase.jar
• Msutil.jar
• Mssqlserver.jar

When you install the Microsoft drivers, by default, they go to the following folder:
c:\program files\Microsoft SQL Server 2000 Driver for JDBC

So, going by this, you can set your CLASSPATH the following way:

CLASSPATH=.;c:\program files\Microsoft SQL Server 2000 Driver for JDBC\lib\msbase.jar;c:\program files\Microsoft SQL Server 2000 Driver for JDBC\lib\msutil.jar;c:\program files\Microsoft SQL Server 2000 Driver for JDBC\lib\mssqlserver

Code to connect SQL Server

import java.*;
public class Connect{
private java.sql.Connection con = null;
private final String url = "jdbc:microsoft:sqlserver://";
private final String serverName= "localhost";
private final String portNumber = "1433";
private final String databaseName= "pubs";
private final String userName = "user";
private final String password = "password";
// Informs the driver to use server a side-cursor,
// which permits more than one active statement
// on a connection.
private final String selectMethod = "cursor";

// Constructor
public Connect(){}

private String getConnectionUrl(){
return url+serverName+":"+portNumber+";databaseName="+databaseName+";selectMethod="+selectMethod+";";
}

private java.sql.Connection getConnection(){
try{
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
con = java.sql.DriverManager.getConnection(getConnectionUrl(),userName,password);
if(con!=null) System.out.println("Connection Successful!");
}catch(Exception e){
e.printStackTrace();
System.out.println("Error Trace in getConnection() : " + e.getMessage());
}
return con;
}

/*
Display the driver properties, database details
*/

public void displayDbProperties(){
java.sql.DatabaseMetaData dm = null;
java.sql.ResultSet rs = null;
try{
con= this.getConnection();
if(con!=null){
dm = con.getMetaData();
System.out.println("Driver Information");
System.out.println("\tDriver Name: "+ dm.getDriverName());
System.out.println("\tDriver Version: "+ dm.getDriverVersion ());
System.out.println("\nDatabase Information ");
System.out.println("\tDatabase Name: "+ dm.getDatabaseProductName());
System.out.println("\tDatabase Version: "+ dm.getDatabaseProductVersion());
System.out.println("Avalilable Catalogs ");
rs = dm.getCatalogs();
while(rs.next()){
System.out.println("\tcatalog: "+ rs.getString(1));
}
rs.close();
rs = null;
closeConnection();
}else System.out.println("Error: No active Connection");
}catch(Exception e){
e.printStackTrace();
}
dm=null;
}

private void closeConnection(){
try{
if(con!=null)
con.close();
con=null;
}catch(Exception e){
e.printStackTrace();
}
}
public static void main(String[] args) throws Exception
{
Connect myDbTest = new Connect();
myDbTest.displayDbProperties();
}
}

Output

If this code is successful, the output is similar to the following:

Connection Successful!
Driver Information
Driver Name: SQLServer
Driver Version: 2.2.0022

Database Information
Database Name: Microsoft SQL Server
Database Version: Microsoft SQL Server 2000 - 8.00.384 (Intel X86)
May 23 2001 00:02:52
Copyright (c) 1988-2000 Microsoft Corporation
Desktop Engine on Windows NT 5.1 (Build 2600: )

Avalilable Catalogs
catalog: master
catalog: msdb
catalog: pubs
catalog: tempdb

This is an excerpt from microsoft article. Here is the full article:

http://support.microsoft.com/kb/313100

Backup / Restore OIM Oracle DB

I am showing you the statements to backup and import an OIM Database. Most of the statements are on a linux box. Windows should be same or nearly same. I'll also provide with a few useful OIM / Oracle statements (besides Backup/Restore) that will be handy all the time.

OIM Backup
I always recommended that whenever you reach a logical step in implementing OIM, you should take a backup. By logical step, I mean when your first clean install is completed take one backup. Once your connectors are loaded, take another backup. So on and so forth. Coming straight to the point of backup, here are the statements:
Assuming you have a linux machine, connect with the user that has the privileges to do an export of oracle. Usually user is oracle, but if you have a different one - go with that user.

[oracle@idm ~]$mkdir exports
[oracle@idm ~]$cd exports
[oracle@idm exports]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

Here is the syntax we are going to use to export the database:
exp <schemaOwner>/<schemaOwnerPassword>@<DatabaseSID> file=<filename>.dmp log=<logname>.log full=y

In this example assume:
Schema owner is xladm
Password is xladmpwd
databaseSID is IDM
filename is base_clean_install.dmp
logname is base_clean_install_log.log

So, issue the command like this:

[oracle@aelidm1 exports]$ exp xladm/xladmpwd@IDM file=base_clean_install.dmp log=base_clean_install_log.log full=y

If you need to, you may set your environment variables as follows:
export ORACLE_HOME=/ora/oim_infra
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=IDM

or in windows as follows:
set ORACLE_HOME=C:\ora\oim_infra
set PATH=%PATH%;%ORACLE_HOME%;
set ORACLE_SID=IDM

Note: Please change values according to your environment.

And your backup is done.

OIM Restore

Step 1

Firstly, delete user/schema owner & associated datafiles from Enterprise Manager. You may also use the following scripts to achieve it:

sqlplus sys/password@IDM as sysdba
SQL>drop user xladm cascade;
SQL>drop tablespace oim_dev including datafiles and contents;
SQL>drop tablespace oim_dev including contents;
SQL>quit

Note, we are logging in as sys user (with sysdba privilege) to delete the xladm schema owner / user and drop oim_dev tablespace completely.

Step 2

====================================
To Re-Prepare the OIM database:
====================================
log in as oracle (or login root and su - oracle)
cd /oim903/OIM903/installServer/Xellerate/db/oracle
sh ./prepare_xl_db.sh IDM /oracle/10_2 xladm xladmpwd OIM_DEV /oracle/OIM OIM_DEV_D01 TEMP sys_user_password

Here is the description of the parameters you need send to the script:
# Arguments : $1 --> ORACLE_SID
# $2 --> ORACLE_HOME
# $3 --> Oracle Identity Manager User Name
# $4 --> Password for the Oracle Identity Manager user
# $5 --> Name of the Tablespace to be created
# $6 --> Directory to store the datafile for the tablespace
# $7 --> Name of the datafile
# $8 --> Temporary tablespace for Oracle Identity Manager User
# $9 --> Password of the SYS user


Step 3

cd ~/exports
imp xladm/xladmpwd@IDM

Specify the filename from where the dump has to be imported. This will be the .dmp file name you specified when you took the backup. Also, specify xladm as the user when asked and press enter on all the other options presented.

Step 4

Next, we will recompile all invalid objects.

To recompile as user, save this file and run it from sqlplus:
Go to the folder that holds recompile_as_user.sql file. If you do not have this file, copy the text from below and save it one of the folder. Please note, you might have to run this twice to make sure all invalid objects are compiled.

sqlplus xladm/xladmpwd@IDM
SQL>@recompile_as_user.sql;
SQL>@recompile_as_user.sql;
SQL>quit

Then you can quit from sqlplus. If you are one of those who are intrigued to know which objects are rendered invalid, here is the sql you can use to find out this.

SQL>select object_name from user_objects where status='INVALID';

Here is the file:

----------------------------------------------------------
-- recompile_as_user.sql
-- connected as the SCHEMA_OWNER
----------------------------------------------------------
set feedback off;
set heading off;
set linesize 100;
set pagesize 1000;

spool recompile_as_user.lst;
prompt spool recompile_as_user.log;

select 'alter '||
DECODE(object_type,'PACKAGE BODY','PACKAGE',object_type)||' '||
object_name||' compile '||
DECODE(object_type,'PACKAGE BODY','BODY','PACKAGE','PACKAGE',' ')||';',
'show errors;'
from user_objects
where status = 'INVALID'
order by created,
DECODE(object_type, 'PACKAGE BODY', 'AAA', 'PACKAGE', 'AAB', substr(object_type, 1, 3)) DESC,
object_name;

prompt spool off;
spool off;

@@recompile_as_user.lst;
----------------------------------------------------------

Step 5

All set with the import. Restart your application server and you should be all set.

cd /opt/oracle/xellerate/bin
./xlStartServer.sh &
or ./xlStartServer.sh | tee /tmp/oim.log | less
or however you start your application server.

If you need to kill your application server, just do a ps -ef | grep java
and then you can kill the process.


Solving Oracle Issues if it is not up
===========================================

Check all the services are up (started) in Control Panel ->services

C:\oracle\product\10.2.0\db_1\BIN>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Dec 27 16:01:09 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250452 bytes
Variable Size 188746604 bytes
Database Buffers 419430400 bytes
Redo Buffers 2940928 bytes
Database mounted.
Database opened.
SQL>quit


----------------------------------
Other DB Related Operations
----------------------------------
To ping TNS - tnsping idm
To start listener - lnsrctrl start
To start enterprise manager - emctl start dbconsole
To login to enterprise manager webconsole - http://server:1158/em
To startup database in sqlplus - startup
To shutdown in sqlplus - shutdown
To reach sqlplus address - http://server:5560/isqlplus
To initiate sqlplus - sqlplus

Another Backup Variant:
exp system/systempwd file=C:\DB_backup.dmp owner=xladm

AD SSL Handshake / Certificate Expired Error

If you have a certificate in Active Directory that is manually generated and expired, your OIM connection might fail with SSL Handshake error or Certificate Expired Error. Even though you see the correct certificate in Active Directory, still you might recieve SSL Handshake Errors or Certificate Expired Errors. This happens mostly when its a manually generated certificate.

Here is the error that you might face:
java.security.cert.CertificateExpiredException: NotAfter: Thu Apr 17 13:56:25 EDT 2008
at sun.security.x509.CertificateValidity.valid(CertificateValidity.java:268)
at sun.security.x509.X509CertImpl.checkValidity(X509CertImpl.java:564)
at sun.security.validator.SimpleValidator.engineValidate(SimpleValidator.java:123)
at sun.security.validator.Validator.validate(Validator.java:202)
at com.sun.net.ssl.internal.ssl.X509TrustManagerImpl.checkServerTrusted(DashoA12275)
at com.sun.net.ssl.internal.ssl.JsseX509TrustManager.checkServerTrusted(DashoA12275)
at com.sun.net.ssl.internal.ssl.SunJSSE_az.a(DashoA12275)
at com.sun.net.ssl.internal.ssl.SunJSSE_az.a(DashoA12275)
at com.sun.net.ssl.internal.ssl.SunJSSE_ax.a(DashoA12275)
at com.sun.net.ssl.internal.ssl.SSLSocketImpl.a(DashoA12275)
at com.sun.net.ssl.internal.ssl.SSLSocketImpl.j(DashoA12275)
at com.sun.net.ssl.internal.ssl.SSLSocketImpl.a(DashoA12275)
at com.sun.net.ssl.internal.ssl.AppInputStream.read(DashoA12275)
at java.io.BufferedInputStream.fill(BufferedInputStream.java:183)
at java.io.BufferedInputStream.read1(BufferedInputStream.java:222)
at java.io.BufferedInputStream.read(BufferedInputStream.java:277)
at com.sun.jndi.ldap.Connection.run(Connection.java:784)

Alternatively, you might face the following issue:
javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: No trusted certificate found

Here is the solution for this:

WORKAROUND

To work around this issue, remove the expired (archived) certificate. To do this, follow these steps:1. Open the Microsoft Management Console (MMC) snap-in where you manage the certificate store on the IAS server. If you do not already have an MMC snap-in to view the certificate store from, create one. To do so:a. Click Start, click Run, type mmc in the Open box, and then click OK.
b. On the Console menu (the File menu in Windows Server 2003), click Add/Remove Snap-in, and then click Add.
c. In the Available Standalone Snap-ins list, click Certificates, click Add, click Computer account, click Next, and then click Finish.

Note You can also add the Certificates snap-in for the user account and for the service account to this MMC snap-in.
d. Click Close, and then click OK.

2. Under Console Root, click Certificates (Local Computer).
3. On the View menu, click Options.
4. Click to select the Archived certificates check box, and then click OK.
5. Expand Personal, and then click Certificates.
6. Right-click the expired (archived) digital certificate, click Delete, and then click Yes to confirm the removal of the expired certificate.
7. Quit the MMC snap-in. You do not have to restart the computer or any services to complete this procedure.
8. FYI - In our case, we had to restart the AD server to take the changes in effect. This did not fix the issue without restarting.

This is an excerpt from Microsoft's Website. Here are the links to solve this:

http://support.microsoft.com/kb/822406/

http://support.microsoft.com/kb/839514/

The other problem could be your new / renewed certificate was not imported in Java cacerts keystore of OIM server. Use the following to connect OIM with SSL based Active Directory. This is an excerpt from OIM documentation:

Installing Certificate Services

The connector requires Certificate Services to be running on the host computer. To install Certificate Services:
1.Insert the operating system installation media into the CD-ROM or DVD drive.
2.Click Start, Settings, and Control Panel.
3.Double-click Add/Remove Programs.
4.Click Add/Remove Windows Components.
5.Select Certificate Services.
6.Follow the instructions to start Certificate Services.

Enabling LDAPS

The target Microsoft Active Directory server must have LDAP over SSL (LDAPS) enabled. To enable LDAPS, generate a certificate as follows:
1.On the Active Directory Users and Computers console, right-click the domain node, and select Properties.
2.Click the Group Policy tab.
3.Select Default Domain Policy.
4.Click Edit.
5.Click Computer Configuration, Windows Settings, Security Settings, and Public Key Policies.
6.Right-click Automatic Certificate Request Settings, and then select New and Automatic Certificate Request. A wizard is started.
7.Use the wizard to add a policy with the Domain Controller template.
At the end of this procedure, the certificate is created and LDAP is enabled using SSL on port 636.


Setting Up the Microsoft Active Directory Certificate As a Trusted Certificate

If the Microsoft Active Directory certificate is not issued or certified by a certification authority (CA), then set it up as a trusted certificate. To do this, you first export the certificate and then import it into the keystore of the Oracle Identity Manager server as a trusted CA certificate.
Exporting the Microsoft Active Directory Certificate
To export the Microsoft Active Directory certificate:
1.Click Start, Programs, Administrative Tools, and Certification Authority.
2.Right-click the Certification Authority that you create, and then select Properties.
3.On the General tab, click View Certificate.
4.On the Details tab, click Copy To File.
5.Use the wizard to create a certificate (.cer) file using base-64 encoding.
Importing the Microsoft Active Directory Certificate
To import the Microsoft Active Directory certificate into the certificate store of the Oracle Identity Manager server:

Note:
In a clustered environment, you must perform this procedure on all the nodes of the cluster.

Note:
The user password cannot be set unless 128-bit SSL is used. In addition, the computer on which Microsoft Active Directory is installed must have Microsoft Windows 2000 Service Pack 2 (or later) or Microsoft Windows 2003 running on it.

Granting Users Limited Design Console Access

Sometimes you want to share responsibility to maintain a resource or any other item of a design console with another user who does not belong to Administrator group.

Here, I show you with a technique to grant limited access of design console to users. You can have different groups having different access of design consoles.

1. Modify the user profile User-Type attribute to be "End-User Administrator" instead of "End-User".





Now, after this change, you can log in to the design console. But, you will not be able to see any items. That is because we explicitly need to give access to the menu items.



If you do not do the above said change, you will get the following error.



2. Go to your OIM web application and create a Group, say, "DESIGN CONSOLE MID ADMINS" using your web application and make this user member of the group.





3. Next, login to OIM design console as system admin (xelsysadm or whatever id you use) and authorize the menu items as well as the explicit individual items you want the "DESIGN CONSOLE MID ADMINS" members to see. For example, I will demonstrate an IT Resource called "Excel Sheet", that these members will be able to Read only. You can also assign Write and / or Delete with any combinations.



4. Go to the IT Resource and then navigate to the Administrator tab. Assign your group - "DESIGN CONSOLE MID ADMINS" to the resource and check off the rights you want this group to have. This will ensure to give members of this user the permission to explicitly look at the details of the IT Resource.




5. Now, you are ready to login as this user.

OIM Supported Version

When you are installing OIM, sometimes you might have a different version of software that OIM does not support (for eg. Java supported version issue). So, just to bypass the warning, here I present you with a trick. Go to your OIM installation folder files and navigate to installServer\Xellerate folder. You will find one file called xlSupportedVersion.properties. Easy enough - Just open it and edit the file and save it with your version number. No more warnings will appear ever again.

Here is how the file looks like:
==================================
xlSupportedVersion.properties
==================================
jbossversion=4.0.3SP1
jdkversion=1.4.2_12
weblogicversion=8.1 SP6
websphereversion=5.1.1.12
oc4jversion=10.1.3.1.0

You may modify this file to look as follows: (notice I changed the jdk version)

==================================
xlSupportedVersion.properties
==================================
jbossversion=4.0.3SP1
jdkversion=1.4.2_13
weblogicversion=8.1 SP6
websphereversion=5.1.1.12
oc4jversion=10.1.3.1.0

Converting AD long dates to Java Date format

Environment : OIM 9.0.3, OIM Connector Pack 9.0.4.1, AD 2000

When you reconcile data from AD, the AD dates fail to link up OIM dates because of different format. The dates are stored in AD in long format and OIM uses normal Java Dates. So, here is the code that you can use to make this conversion.

import java.util.Date;
import java.util.TimeZone;
import java.text.SimpleDateFormat;
public class AD
{
public void converADdateToOIMdate(long ADdate){

long ADdate = Long.parseLong(String.valueOf(ADdate));
System.out.println("long value : "+ADdate);

// Filetime Epoch is 01 January, 1601
// java date Epoch is 01 January, 1970
// so take the number and subtract java Epoch:
long javaTime = ADdate - 0x19db1ded53e8000L;

// convert UNITS from (100 nano-seconds) to (milliseconds)
javaTime /= 10000;

// Date(long date)
// Allocates a Date object and initializes it to represent
// the specified number of milliseconds since the standard base
// time known as "the epoch", namely January 1, 1970, 00:00:00 GMT.
Date theDate = new Date(javaTime);


System.out.println("java DATE value : "+theDate);

SimpleDateFormat formatter = new SimpleDateFormat("MMMMM d, yyyy");
// change to GMT time:
//formatter .setTimeZone(TimeZone.getTimeZone("GMT"));

String newDateString = formatter.format(theDate);

System.out.println("Date changed format :" + newDateString);
}


public static void main(String[] args)
{
AD d=new AD();
d.converADdateToOIMdate(128568528000000000L);
// 9223372036854775807
// 127948319499226601

}
}