1 Reply Latest reply on Oct 10, 2005 11:07 AM by Claire Costello

    Trusted Connection to SQL Server

    Claire Costello Newbie

      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.

        • 1. Re: Trusted Connection to SQL Server
          Claire Costello Newbie

          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.