Trusted Connection to SQL Server

[clairecostello]

Hi,

Machine setup:

Windows Server 2003
JBoss 4.0.2
Java 5.0
SQL Server 2000 Enterprise Edition version 8.0 (set to mixed mode authentication.)

mssql-ds.xml:

<datasources>
 <local-tx-datasource>
 <jndi-name>MSSQLDS</jndi-name>
 <connection-url>jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=iWISEDataStore</connection-url>
 <driver-class>com.microsoft.jdbc.sqlserver.SQLServerDriver</driver-class>
 <connection-property name="Trusted_Connection">yes</connection-property>
 <user-name></user-name>
 <password></password>
 <metadata>
 <type-mapping>MS SQLSERVER2000</type-mapping>
 </metadata>
 </local-tx-datasource>

</datasources>

login-conf.xml (just the required policy element):

<application-policy name = "MSSQLDbRealm">
 <authentication>
 <login-module code = "org.jboss.resource.security.ConfiguredIdentityLoginModule" flag = "required">
 <module-option name = "principal">sa</module-option>
 <module-option name = "userName">sa</module-option>
 <module-option name = "password"></module-option>
 <module-option name ="managedConnectionFactoryName"> jboss.jca:service=LocalTxCM,name=MSSQLDS</module-option>
 </login-module>
 </authentication>
</application-policy>

When I deploy my J2EE application (which works perfectly with HSQL), I get the following error when JBoss tries to connect to the SQL Server:

org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.)

Does anyone know how to fix this?
All help much appreciated.
-Claire.

[clairecostello]

Hi,

I'm posting my solution to this problem. I'd spent a few hours searching for an answer to my original predicament. In the end, I decided to mess around with the authenication methods and passwords of SQL Server users.

Using the SQL Enterprise Manager, I created a new user for my database on the SQL server (which is installed on the same machine as JBoss AS.) I set the password of this new user to be an "SQL Server Authentication" password. It seems single sign-on cannot be used when using JDBC connection to MS SQL Server, ie, I cannot connect using Windows account. Perhaps I'm wrong here? Note: Mixed-mode authentication is set.

Thanks,
Claire.