This article explains how to configure your Asset Bank so that it connects to a SQL Server database using a Windows account (using Integrated Windows Authentication). You might want to do this to avoid having the database user credentials in Asset Bank's components.xconf file.

1. Edit Asset Bank's database settings to configure it to connect to SQL Server without passing a username or password. The database settings are in database-local.properties in newer versions of Asset Bank or components.xconf in older versions.

1a) If [asset-bank]\WEB-INF\classes\database-local.properties exists:

Open it in a text editor and make sure that the SQL Server settings are not commented out (i.e. they have no # at the beginning of the line) and the MySQL settings are commented out or deleted.

Keep the username and password settings in the file but leave them blank, i.e.:

database.username =
database.password =

Add integratedSecurity=true; to the end of the database.url string, i.e.:

database.url = jdbc:sqlserver://mydb.server.com:1433;databaseName=assetbank;integratedSecurity=true;

1b) If [asset-bank]\WEB-INF\classes\database-local.properties does not exist:

Open components.xconf in [asset-bank]\WEB-INF and find the data source component for SQL Server, which has the following comment above it:

<!-- SQL server Data Source -->

Uncomment this XML element, and ensure that the other MySQL data source component is commented out.

Remove or comment out the user and password elements, for example:

<component role="JdbcDataSource" class="com.bright.framework.database.service.ResourceLimitingJdbcDataSource"><pool-controller min="10" max="20" max-strict="true" blocking="true" timeout="10000" connection-class="org.apache.avalon.excalibur.datasource.JdbcConnection" />
<auto-commit>false</auto-commit>
<driver>net.sourceforge.jtds.jdbc.Driver</driver>
<dburl>jdbc:jtds:sqlserver://localhost:1433/assetbank;instance=SQLEXPRESS2008</dburl>
<!--
<user>sa</user>
<password>password</password>
-->
</component>

2. Download the Microsoft JDBC Driver 6.2 for SQL Server  (or jTDS driver package if your Asset Bank is using the jTDS driver) and, depending on your server's OS, extract either the 32bit or 64bit sqljdbc_auth.dll (or ntlmauth.dll if you are using jTDS) file from the respective directory within the zip file and place the file anywhere in the system path (defined by the PATH system variable).

3. Check (or change) the Tomcat service so it is runs as the Windows user account you want to use to connect to the database. Make sure this user has sufficient permissions on the Tomcat directory and on all the executables that Asset Bank needs to run.

4) Grant this Windows user permission to access the SQL Server database as follows:

• In SQL Server Management Studio, go to Security->Logins and create a SQL Server login for the user.
• In SQL Server Management Studio, right click on the [DOMAIN]\[user] login you've just created, go to User Mapping and grant db_owner permission on the relevant database. Alternatively, grant the user the following roles: db_datareader, db_datawriter, db_ddladmin and public.