How to make a data source connection without writing a User name & Password and the -ds.xml file

[Anita Vargas]

Hi everybody!

I´m working in a java web project using SQL Server 2000, Java 1.5 and JBoss 4.2. Now im making the data source connection on C:\jboss4.2\server\default\deploy\mssql-ds.xml like this:

<datasources>
  <local-tx-datasource>
    [b]<jndi-name>DataSource</jndi-name>
    <connection-url>jdbc:microsoft:sqlserver://localhost:1433;SelectMethod=cursor;DatabaseName=MyDataBase</connection-url>
    <driver-class>com.microsoft.jdbc.sqlserver.SQLServerDriver</driver-class>
    <user-name>sa</user-name>
    <password></password>[/b]	<!--pooling parameters-->
    <min-pool-size>150</min-pool-size>
    <max-pool-size>300</max-pool-size>

    <blocking-timeout-millis>100000000</blocking-timeout-millis>
    <idle-timeout-minutes>10</idle-timeout-minutes>
        <!-- sql to call when connection is created
        <new-connection-sql>some arbitrary sql</new-connection-sql>
        -->

        <!-- sql to call on an existing pooled connection when it is obtained from pool 
        <check-valid-connection-sql>some arbitrary sql</check-valid-connection-sql>
        -->

      <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->
      <metadata>
         <type-mapping>MS SQLSERVER2000</type-mapping>
      </metadata>
  </local-tx-datasource>

</datasources>

The point is that i´ve been asked to make a different data source connection cuz im not allowed to show the USER and PASSWORD to the database in any file. So I tried with the odbc bridge configuring it with Integration Security, so the -ds.xml file looks like this:

<datasources>
  <local-tx-datasource>
    <jndi-name>DataSource</jndi-name>
    <connection-url>jdbc:odbc:MyOdbcBridge</connection-url>
    <driver-class>sun.jdbc.odbc.JdbcOdbcDriver</driver-class>
   <!--pooling parameters-->
    <min-pool-size>150</min-pool-size>
    <max-pool-size>300</max-pool-size>
                                .
                                .
                                .

When i deploy the app the conenction is established correctly but when i want to make any action on my application, the server log sends this msg java.sql.SQLException: [Microsoft][ODBC SQL Server Driver]Optional feature not implemented, and searching on internet i found that this is a problem tha comes (among others) by using the Date object cuz the driver does not support this feature; in most of actions, classes and files i use the Data object; so i think its not good idea to make the connection though the odbc driver and also cuz i've read that i can lose the performance of my app using this driver.

Then i have no more ideas in how making such connetion!! ... i've been searching and searching but i cant find the way to do it... does anyone knows how can i acomplish this thing??... i would be really really thankful if you can help me.

Greetings from Mexico!

[Peter Johnson]

Ana, welcome to Java Ranch!

Please use the 'code' tags when posting source code or XML text - makes it much easier to read.

You can supply an encrypted password for use with datasources. To do this, you need to supply a secure identity login module in server/xxx/conf/login-config.xml. Here's an example:

postgres-ds.xml

<datasources>
  <local-tx-datasource>
    <jndi-name>jdbc/messagingDS</jndi-name>
    <connection-url>jdbc:postgresql:messagingdb</connection-url>
    <driver-class>org.postgresql.Driver</driver-class>
    <security-domain>JbiaDatabaseLogin</security-domain>
    <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
    <min-pool-size>5</min-pool-size>
    <max-pool-size>20</max-pool-size>
    <idle-timeout-minutes>15</idle-timeout-minutes>
    <metadata>
      <type-mapping>PostgreSQL 8.0</type-mapping>
    </metadata>
  </local-tx-datasource>
</datasources>

login-config.xml (excerpt)

<application-policy name="JbiaDatabaseLogin">
  <authentication>
    <login-module code="org.jboss.resource.security.SecureIdentityLoginModule" flag="required">
      <module-option name="username">jbossinaction</module-option>
      <module-option name="password">4328fae45aec5dd57f96b0e7b2b2e76fdf8592078de921bc</module-option>
      <module-option name="managedConnectionFactoryName">jboss.jca:name=jdbc/messagingDS,service=LocalTxCM</module-option>
    </login-module>
  </authentication>
</application-policy>

Note the security-domain entry in the *-ds.xml file, and that it maps to the application-policy name in login-config.xml.

For a full working example, see target 05 for project ch08 in the JBoss in Action source code (version 1.1).

[Anita Vargas]

Hello Peter, thanks for your suggestions!...

I've taken your advice about supplying an encrypted password but im having some troubles and i don't know why

I have two data source xml files, and they both make the connection to the same database (the only one difference is the jndi-name), the files are mssql-ds.xml and mssqlFMW-ds.xml, and now the structure looks like this:

  
<datasources>
  <local-tx-datasource>
    <jndi-name>DataSource</jndi-name>
    <connection-url>jdbc:microsoft:sqlserver://localhost:1433;SelectMethod=cursor;DatabaseName=MyDataBase</connection-url>
    <driver-class>com.microsoft.jdbc.sqlserver.SQLServerDriver</driver-class>
    <security-domain>HsqlDbRealm</security-domain>
    <!--pooling parameters-->
    <min-pool-size>150</min-pool-size>
    <max-pool-size>300</max-pool-size>
  

In the file C:\jboss4.2\server\default\conf\login-config.xml I have the following lines of code, and i dont really know if i must channge the one called "HsqlDbRealm", the one called "JmsXARealm" or both of them (anyways i made the possible combinations but in any case the connection is not successful):

  
<!-- Security domains for testing new jca framework -->
    <application-policy name = "HsqlDbRealm">
       <authentication>
          <login-module code = "org.jboss.resource.security.SecureIdentityLoginModule"
             flag = "required">
             <module-option name = "principal">sa</module-option>
             <module-option name = "userName">sa</module-option>
             <module-option name = "password">9fdd42c2a7390d3</module-option>
             <module-option name = "managedConnectionFactoryName">jboss.jca:service=LocalTxCM,name=DefaultDS</module-option>
          </login-module>
       </authentication>
    </application-policy>

    <application-policy name = "JmsXARealm">
       <authentication>
          <login-module code = "org.jboss.resource.security.SecureIdentityLoginModule"
             flag = "required">
             <module-option name = "principal">sa</module-option>
             <module-option name = "userName">sa</module-option>
             <module-option name = "password">9fdd42c2a7390d3</module-option>
             <module-option name = "managedConnectionFactoryName">jboss.jca:service=TxCM,name=JmsXA</module-option>
          </login-module>
       </authentication>
    </application-policy>

In the above case, the log server drops the following errors (i put the lines i consider could be the most relevant):

15:19:00,830 ERROR [URLDeploymentScanner] Incomplete Deployment listing:

--- MBeans waiting for other MBeans ---
ObjectName: jboss:service=KeyGeneratorFactory,type=HiLo
State: FAILED
Reason: org.jboss.deployment.DeploymentException: Error while checking if table aleady exists HILOSEQUENCES; - nested throwable: (org.jboss.util.NestedSQLException: Could not create connection; - nested throwable: (java.sql.SQLException: Access is denied); - nested throwable: (org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (java.sql.SQLException: Access is denied)))
I Depend On:
jboss:service=TransactionManager
jboss.jca:service=DataSourceBinding,name=DefaultDS

ObjectName: jboss.mq:service=PersistenceManager
State: FAILED
Reason: org.jboss.mq.SpyJMSException: Could not resolve uncommited transactions. Message recovery may not be accurate; - nested throwable: (org.jboss.util.NestedSQLException: Could not create connection; - nested throwable: (java.sql.SQLException: Access is denied); - nested throwable: (org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (java.sql.SQLException: Access is denied)))
I Depend On:
jboss.jca:service=DataSourceBinding,name=DefaultDS
Depends On Me:
jboss.mq:service=DestinationManager

--- MBEANS THAT ARE THE ROOT CAUSE OF THE PROBLEM ---
ObjectName: jboss.mq:service=PersistenceManager
State: FAILED
Reason: org.jboss.mq.SpyJMSException: Could not resolve uncommited transactions. Message recovery may not be accurate; - nested throwable: (org.jboss.util.NestedSQLException: Could not create connection; - nested throwable: (java.sql.SQLException: Access is denied); - nested throwable: (org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (java.sql.SQLException: Access is denied)))
I Depend On:
jboss.jca:service=DataSourceBinding,name=DefaultDS
Depends On Me:
jboss.mq:service=DestinationManager

ObjectName: jboss:service=KeyGeneratorFactory,type=HiLo
State: FAILED
Reason: org.jboss.deployment.DeploymentException: Error while checking if table aleady exists HILOSEQUENCES; - nested throwable: (org.jboss.util.NestedSQLException: Could not create connection; - nested throwable: (java.sql.SQLException: Access is denied); - nested throwable: (org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (java.sql.SQLException: Access is denied)))
I Depend On:
jboss:service=TransactionManager
jboss.jca:service=DataSourceBinding,name=DefaultDS


And tehre are some other lines that say:
15:11:24,830 WARN [JBossManagedConnectionPool] Throwable while attempting to get a new connection: null
org.jboss.resource.JBossResourceException: No matching credentials in Subject!

I dont know if this error is because i have two data sorce files (though i dont think so) or if there is something else that im missing or maybe there is something wrong in the login-config.xml file... any idea???

[Peter Johnson]

You don't change any of the existing entries in login-config.xml, instead you add a new application-policy that is like the one I showed. You give it a unique name and reference that name in your *-ds.xml file.

[Anita Vargas]

Hello Peter, I've added a new policy into de login-config but now i got this error:

ERROR [AbstractPasswordCredentialLoginModule] The ConnectionManager mbean: jboss.jca:name=jdbc/messagingDS,service=LocalTxCM specified in a ConfiguredIdentityLoginModule could not be found. ConnectionFactory will be unusable!
ERROR [STDERR] java.lang.SecurityException: Invalid authentication attempt, principal=null

Then I thought it could be cuz it might be missing this line

   <module-option name = "principal">sa</module-option>    

and i added to the policy but i still get the same error, my application-policy looks like this:

 
<application-policy name="JBBLogin">   
  <authentication>   
    <login-module code="org.jboss.resource.security.SecureIdentityLoginModule" flag="required"> 
      <module-option name = "principal">sa</module-option>  
      <module-option name = "username">sa</module-option>   
      <module-option name = "password">9fdd42c2a7390d3</module-option>   
      <module-option name = "managedConnectionFactoryName">jboss.jca:name=jdbc/messagingDS,service=LocalTxCM</module-option>   
    </login-module>   
  </authentication>   
</application-policy>  

What could be the reason of the error??

[Peter Johnson]

The MBean name specified for managedConnectionFactoryName must match the name MBean created for your datasource:

<module-option name="managedConnectionFactoryName">jboss.jca:name=DataSource,service=LocalTxCM</module-option>

Remove the "principle" line, it is not used.

See if those two changes fix the problem.

[Anita Vargas]

OH!... I've FOUND THE SOLUTION!!

The ERROR was on this line

 <module-option name = "managedConnectionFactoryName">jboss.jca:name=jdbc/messagingDS,service=LocalTxCM</module-option>

The Managed Connection Factory Name must be the same name than the JNDI name defined on the file mssql-ds.xml; and the jndi name is this one

  <jndi-name>DataSource</jndi-name>  

And the wrong line in my application policy name now look like this:

<module-option name = "managedConnectionFactoryName">jboss.jca:name=DataSource,service=LocalTxCM</module-option>

Peter, THANKS thanks a lot for your help! It was really useful for me and I really apreciate it!

Saludos desde Mexico! (Greetings from Mexico!)

[Anita Vargas]

Oh thanks again Peter... it was a little bit late your reply...anyways im really thankful... and i would invite you to drink a cup of coffee!... There is one called Java Chips...a nd for some weird reason I really love it (ehmmm i dont really know why )...

Anyways... Tanks for your help!

[Anita Vargas]

Well after too much trying on making the data source connection thrugh an Encryted password I was again told not to write the pass(encryted/not encrypted) in any configuration file . And as i posted before, I was trying through the ODBC bridge but i think this is not the optimal solution... Then I'm wonderig if is there any other possible way to make the conection through Integration security (with the windows autentication and not sql auth).

I was also trying (desperate option i think) by creatig a new session user on the Enterprise manager, this user is a Windows user, then in the *-ds.xml file i just wrote this line <user-name>MyComputerName\User</user-name> without password but i got this Error:

[JBossManagedConnectionPool] Throwable while attempting to get a new connection: null
org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Login failed for user 'MyComputerName\User'.)

So, is it possible to make the connection through the Windows Authetication??... (Thought the important thing here is making the connection without showing any kind of password in any file) Does anyone know?. Any more ideas?... If its not possible i need a reasoable reason hehe to base judgement on the option of the encrypted password.

Greetings!

[Jaikiran Pai]

Windows integrated authentication really depends on whether the driver supports it (usually its a additional parameter in the connection URL). Since you are using MSSQL JDBC driver, you will have to check their documentation for more details.

[Jaikiran Pai]

This might help (especially the "Connecting with Integrated Authentication" section)

[Anita Vargas]

Hello Jaikiran, thanks for you reply! I´ve found out that the MSSQL JDBC driver im using does not allow to make the database connection thrugh Windows Integrated Aunthentication, so i decided to download the Microsoft SQL Server JDBC Driver 2.0 cuz it seems this one supports to make such connection. I did the next steps:

1) Deleted MSSQL jar's from \jboss4.2\lib and MyApp\lib:
msbase.jar,
mssqlserver.jar,
msutil.jar
2) I established the CLASSPATH on: CLASSPATH =.;C:\Program Files\Microsoft SQL Server JDBC Driver\sqljdbc_2.0\enu\sqljdbc.jar
3) Added .jar in Java\jdk1.5.0_15\bin
4) Added .jar in \jboss4.2\lib
5) Added .jar in MyApp\lib

Firstly i wanted to try the connection through SQL Aunthentication but it seems i didnt install correctly the driver cuz i´m having some trubles since de begining , i got this ERROR:

org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (org.jboss.resource.JBossResourceException: Failed to register driver for: com.microsoft.jdbc.sqlserver.SQLServerDriver; - nested throwable: (java.lang.ClassNotFoundException: com.microsoft.jdbc.sqlserver.SQLServerDriver))

What did i do wrong? ... I can´t even make the connection through SQL Authentication... then i cannot be able to try the connection throug Windows Integrated Aunthentication...

[Anita Vargas]

Is it becuse of this line in my *-ds.xml file??? If is this the problem...what could i set as type-mapping??

 <metadata>
        <type-mapping>MS SQLSERVER2000</type-mapping>
    </metadata>

[Peter Johnson]

2) Do not set CLASSPATH - that is usually a good way to get weird results when running other apps. (I gave up setting it years ago and my Java experience has been much better since then.) Instead, copy the sqljdbc.jar file to server/xxx/lib.

3, 4, 5) Do not place the sqljdbc.jar in any of those locations - you will end up with classloader issues.

java.lang.ClassNotFoundException: com.microsoft.jdbc.sqlserver.SQLServerDriver

This means that sqljdbc.jar does not use this class as the driver class. You need to find out from the sqljdbc.jar docs which class to use in the driver-class entry inm the *-ds.xml file. (The type-mapping entry you posted is used by the EJB2.x container for entity beans, it must match one of the type-mapping/name entries in the server/xxx/conf/standardjbosscmp-jdbc.xml file. The "MS SQLSERVER2000" name is used for SQL Server 2000 and later.)

[Anita Vargas]

Hello, I had some troubles trying to get into the forum. And I want to tell you guys that I solved the issue and now my application is perfectly getting the connection through integrated security (I had to change the driver cuz MSSQL driver DOES NOT ALLOW conecction through Windows Authentication, it only allows connections trhough SQL Server Authentication), now I want to show step by step what i finally did to achieve it (like a cookery recipe).

1. Download the Microsoft SQL Server JDBC Driver 2.0 from http://www.microsoft.com/.
2. Unzip the driver (it doesn't really matter the path.. just remember it).
3. Copy the file C:\Program files\Microsoft SQL Server JDBC Driver 2.0\sqljdbc_2.0\enu\sqljdbc.jar and place it in JBOSS_HOME\server\default\lib.
4. To configure the driver so that it allows to make connections through Windows Authentication, you must copy the file C:\Program files\Microsoft SQL Server JDBC Driver 2.0\sqljdbc_2.0\enu\auth\x86\sqljdbc_auth.dll and add it in C:\WINDOWS\system32.
5. The *-ds.xml file now looks like this:

 <datasources>
  <local-tx-datasource>
    <jndi-name>DataSource</jndi-name>

    <connection-url>jdbc:sqlserver://localhost:1433;SelectMethod=cursor;DatabaseName=MyDataBase;integratedSecurity=true </connection-url>
    <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>

    <min-pool-size>150</min-pool-size>
    <max-pool-size>300</max-pool-size>
    <blocking-timeout-millis>100000000</blocking-timeout-millis>
    <idle-timeout-minutes>10</idle-timeout-minutes>
    <metadata>
        <type-mapping>MS SQLSERVER2000</type-mapping>
    </metadata>
  </local-tx-datasource>
</datasources>

NOTE: Since you set integratedSecurity=true the sqljdbc_auth.dll determines the current Windows user's credentials to log into de database.
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
POSSIBLE ERRORS:

* ERROR [STDERR] 13/08/2009 11:35:42 AM com.microsoft.sqlserver.jdbc.AuthenticationJNI <clinit>
WARNING: Failed to load the sqljdbc_auth.dll
This error its because you dont added the sqljdbc_auth.dll in in C:\WINDOWS\system32.

* Its very important to know that the <driver-class> its not the same one for MSSQL driver than Microsoft SQL Server JDBC Driver 2.0. The name its veri similar and I was having troubles because of this, and it took me lonng time to find out what was it
MSSQL <driver-class> - com.microsoft.jdbc.sqlserver.SQLServerDriver
Microsoft SQL Server JDBC Driver 2.0 <driver-class> - com.microsoft.sqlserver.jdbc.SQLServerDriver
If you keep using the MSSQL <driver-class>, since this driver allows only SQL Server Authentication (it need a user name and pass) you get this error:
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.)


* org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (org.jboss.resource.JBossResourceException: Failed to register driver for: com.microsoft.sqlserver.jdbc.SQLServerDriver; - nested throwable: (java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver))
This error its because you didnt placed the sqljdbc.jar in JBOSS_HOME\server\default\lib.

Hope its helpful!

Thanks a lot Peter and Jaikiran four your help guys.

Saludos desde Mexico!

[Aknod Algabar]

Hello,
I am really hope that you will be able to help me in my problem. I have JBoss 4.2.2, PostgreSQL and application with basic security, here fragment from login-config.xml

<application-policy name ="decerns"
	<authentication>
		<login-module code="org.jboss.security.auth.spi.UsersRolesLoginModule" flag="required">
			<module-option name="usersProperties">decerns-users.properties</module-option>
			<module-option name="rolesProperties">decerns-roles.properties</module-option>
		</login-module>
	</authentication>
</application-policy>

and from ...-ds.xml

<datasources>
   <local-tx-datasource>
      <jndi-name>decern2Datasource</jndi-name>
      <connection-url>jdbc:postgresql://localhost:5432/postgres</connection-url>
      <driver-class>org.postgresql.Driver</driver-class>
      <user-name>postgres</user-name>
      <password>postgres</password>
   </local-tx-datasource>
</datasources>

and each time when I am trying to deploy it I have "SecurityException... PersistenceManager... invalid authentication attempt principal=null", exactly as it was described above. What is more interesting I didn't have this problem before with same settings, but after some changes in application, but not in configurations files, I received this error message... I already tried to compare old and new versions file by file, but I was unable to find anything that can cause something like that. About other security settings I followed by this example and as I mentioned everything was perfect. I am really hope that you have some ideas...
Thank you in advance!

[Peter Johnson]

Aknod, welcome to Java Ranch!

You really should have started a new discussion, and not asked a question on an older post. Please start a new post, (you can reference this post if you like rather than retyping it) and include the exception stack trace.