Technology

Shibboleth is an Internet2 project used to implement identity authentication and authorization across multiple domains (sometimes known as a single sign-on). Shibboleth also allows federated authentication, which allows an organization or institution to let a user on one domain to authenticate to another domain. This is common in academic settings where one university may want to allow users from another university to use their services using that first university’s authentication system. A plug-in for Shibboleth known as uApprove provides an approval screen so users can see what information is being shared before being logged into a remote system. uApprove is designed to work with MySQL, however this tutorial shows how it can be easily modified to use Microsoft SQL Server.

Update (2011.12.05): uApprove 2.3 has changed to use ANSI SQL. The default SQL configuration in uApprove 2.3 will work with Microsoft SQL, so long as the following changes are made to the schema:

CREATE TABLE AttributeReleaseConsent (
userId         VARCHAR(104)                           NOT NULL,
relyingPartyId VARCHAR(104)                           NOT NULL,
attributeId    VARCHAR(104)                           NOT NULL,
valuesHash     VARCHAR(256)                           NOT NULL,
consentDate    DATETIME NOT NULL,

PRIMARY KEY (userId, relyingPartyId, attributeId)
);

CREATE TABLE ToUAcceptance (
userId         VARCHAR(104)                           NOT NULL,
version        VARCHAR(104)                           NOT NULL,
fingerprint    VARCHAR(256)                           NOT NULL,
acceptanceDate TIMESTAMP      NOT NULL,  
 PRIMARY KEY (userId, version)
);

For Microsoft SQL, the ToUAcceptance needs to have the DEFAULT and CURRENT_TIMESTAMP attributes removed and the AttributeReleaseConsent.consentDate needs to be changed to a DATETIME. The only other thing that needs to be changed is that in the uApprove.properties file, the database.drive needs to be changed to either com.microsoft.sqlserver.jdbc.SQLServerDriver if using the Microsoft native JDBC driver or net.sourceforge.jtds.jdbc.Driver if using the open source jTDS driver. Be sure to copy either the sqljdbc4.jar or the jtds-1.2.5.jar file into all the appropriate lib directories.

That’s it! The 2.3 version of uApprove requires significantly less modification over version 2.2. For everything else, follow the standard uApprove install instructions.

The following is the original post for older version of uApprove (2.2.1):

Follow the installation instructions found on the uApprove website. There are only three major steps which need to be altered. When creating the database schema, use the following for Microsoft SQL:

create table ArpUser (
  idxArpUser bigint identity(1,1) primary key,
  auUserName varchar(255) not null,
  auLastTermsVersion varchar(255),
  auFirstAccess datetime,
  auLastAccess datetime
);
create index idxUserName on ArpUser (auUserName );

create table ShibProvider (
  idxShibProvider bigint identity(1,1) primary key,
  spProviderName varchar(255)
);

SET IDENTITY_INSERT ShibProvider On;
insert into ShibProvider (idxShibProvider) values (1);
SET IDENTITY_INSERT ShibProvider Off;
create index idxProvidername on ShibProvider (spProviderName);

create table AttrReleaseApproval (
  idxAttrReleaseApproval bigint identity(1,1) primary key,
  araIdxArpUser bigint references ArpUser ( idxArpUser ),
  araIdxShibProvider bigint references ShibProvider( idxShibProvider ),
  araTimeStamp datetime not null,
  araTermsVersion varchar(255),
  araAttributes text
);

create table ProviderAccess (
  idxProviderAccess bigint identity(1,1) primary key,
  paIdxArpUser bigint references ArpUser( idxArpUser ),
  paIdxShibProvider bigint references ShibProvider( idxShibProvider ),
  paAttributesSent text,
  paTermsVersion varchar(255),
  paIdxAttrReleaseApproval bigint references AttrReleaseApproval ( idxAttrReleaseApproval ),
  paShibHandle varchar(255),
  paTimeStamp datetime not null
);

The major differences include using MSSQL’s bigint instead of MySQL’s unsigned int, using identity(1,1) instead auto_increment, replacing the timestamp fields with datetime fields and turning off the identity for inserting the first service provider record. You will need to create a standard SQL user and give it rights to this table in SQL Management Studio. If you use an Active Directory user with windows authentication, Shibboleth must be running on a Windows server and you’ll have to use the native authentication dll. Since I preformed this installation on Linux, that setup is outside the scope of this tutorial.

Next, you’ll notice in the uApprove documentation that all the SQL commands are stored in a mysql.commands file. Create a microsoftSQL.commands file and place the following in it:

selGlobalShibProvider = select idxShibProvider as idx from ShibProvider where spProviderName is null

selIdxUser = select idxArpUser as idxUser from ArpUser where auUserName = '?'

selShibProvider = select idxShibProvider as idxProvider from ShibProvider where spProviderName = '?'

insShibProvider = insert into ShibProvider (spProviderName) values ( '?' )

selArpInfoByUsername1 = select idxArpUser as idxUser, convert(varchar,araTimeStamp,20) as ArpDate, araTermsVersion as TermsOfUseManager, araAttributes as Attributes, spProviderName as ShibProvider from ArpUser, AttrReleaseApproval, ShibProvider where auUserName='?' and idxArpUser=araIdxArpUser and araIdxShibProvider = idxShibProvider order by araTimeStamp desc

selArpInfoByUsername2 = select idxArpUser as idxUser, auLastTermsVersion as TermsOfUseManager, auLastAccess as ArpDate from ArpUser where auUserName='?'

insUser = insert into ArpUser (auUserName, auLastTermsVersion, auFirstAccess, auLastAccess ) values ( '?', '?', getdate(), getdate() )

updUser = update ArpUser set auLastTermsVersion = '?', auFirstAccess=auFirstAccess, auLastAccess=getdate() where auUsername = '?'
updUser1 = update ArpUser set auFirstAccess = auFirstAccess, auLastAccess = getdate() where auUserName = '?'

selGlobalArp = select count(*) as cnt from AttrReleaseApproval, ArpUser, ShibProvider where idxArpUser=araIdxArpUser and idxShibProvider = araIdxShibProvider and spProviderName is null and auUserName = '?'

insAttrApproval = insert into AttrReleaseApproval ( araIdxArpUser, araIdxShibProvider, araTimeStamp , araTermsVersion,araAttributes ) values ( ?, ?, getdate() , '?', null )

insAttrApproval1 = insert into AttrReleaseApproval (araIdxArpUser, araIdxShibProvider, araTimeStamp, araTermsVersion, araAttributes ) values ( ?, ?, getdate() , '?', '?' )

delAttrApproval = delete from AttrReleaseApproval where araIdxArpUser = ? and araIdxShibProvider = ?

updAttrApproval = update AttrReleaseApproval set araTermsVersion = '?', araAttributes = '?' where araIdxArpUser = ? and araIdxShibProvider = ?

selIdxAttrApproval = select idxAttrReleaseApproval as idxApproval, araIdxArpUser as IdxUser, araIdxShibProvider as idxProvider, araTermsVersion as TermsVersion, araAttributes as Attributes from ArpUser, AttrReleaseApproval, ShibProvider where auUserName='?' and idxArpUser=araIdxArpUser and spProviderName = '?' and araIdxShibProvider = idxShibProvider order by araTimeStamp desc

selIdxAttrApprovalGlobal = select idxAttrReleaseApproval as idxApproval, araIdxArpUser as IdxUser, araIdxShibProvider as idxProvider, araTermsVersion as TermsVersion, araAttributes as Attributes from ArpUser, AttrReleaseApproval, ShibProvider where auUserName='?' and idxArpUser=araIdxArpUser and spProviderName is null and araIdxShibProvider = idxShibProvider order by araTimeStamp desc

insProviderAccess = insert into ProviderAccess ( paIdxArpUser, paIdxShibProvider, paTermsVersion, paAttributesSent, paIdxAttrReleaseApproval, paTimeStamp ) values ( ?, ?, '?', '?', ?, getdate() )

selIdxProviderAccess = select idxProviderAccess as idxPA from ProviderAccess, AttrReleaseApproval where paIdxAttrReleaseApproval=idxAttrReleaseApproval and araIdxArpUser=?

clearReleaseForAccess = update ProviderAccess set paIdxAttrReleaseApproval = NULL, paTimeStamp = paTimeStamp where paIdxArpUser = ?

delAttrReleaseApprovals = delete from AttrReleaseApproval where araIdxArpUser = ?

The primary differences include replacing MySQL’s now function with get_date and replacing the date_format with convert. In MySQL, fields that assumed the current time when inserted with nulls must explicitly define the field and use the get_date function in MSSQL.

The database.properties file must be changed to use the new MSSQL command file like so:

sqlCommands=/opt/uApprove/conf/microsoftSQL.commands

driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
url=jdbc:sqlserver://<hostname>:<port>;databaseName=uApprove;intergratedSecurity=true
user=<insert user>
password=<insert password>

The version 3 JDBC drivers for MSSQL can be found on the Microsoft website at the following address:

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=%20a737000d-68d0-4531-b65d-da0f2a735707&displaylang=en

There are two jars in this package and you will only need one of them. For this installation which was for Tomcat 5 running on Java 1.6, I used sqljdbc4.jar. This jar needs to be added everywhere there is a mysql-connector.jar. This includes uApprove-2.2.1/idp-plugin-2.2.1/lib, uApprove-2.2.1/viewer-2.2.1/webapp/WEB-INF/lib and even the shibboleth-idp/war/idp.war. The idp.war is redeployed in the uApprove install instructions. You can add it before this point or add it manually to the jar’s WEB-INF/lib folder afterwords.

That should be all that is necessary to run the uApprove plug-in against Microsoft SQL Server. I’ve tested this in MSSQL 2005 but it should run fine on 2008 as well. If you run into issues, be sure to check the log files for uApprove, Shibboleth and Tomcat to help diagnose issues.