Wildfly 8.1 and Microsoft SQL JDBC 4.0 Driver

[Perry Terrance]

I seem to have the darndest time getting my Wildfly Datasource setup with my locally running SQL 2012 Express.

I am using Microsoft's official JDBC 4.0 Driver and I have been tinkering with this for the past week to no avail. Can you guys spot something I'm not seeing?

Request { "address" => [ ("subsystem" => "datasources"), ("data-source" => “MYDATABASE-DS") ], "operation" => "test-connection-in-pool" } Response Internal Server Error { "outcome" => "failed", "failure-description" => "JBAS010440: failed to invoke operation: JBAS010447: Connection is not valid", "rolled-back" => true
Here my datasource configuration so far:

Name: MYDATABASE-DS JNDI: java:jboss/datasources/MYDATABASE-DS Is enabled?: true Datasource Class: Driver: sqljdbc4-4.0.jar Driver Class: com.microsoft.sqlserver.jdbc.SQLServerDriver Share Prepared Statements: false Statement Cache Size: 0 Connection URL: jdbc:sqlserver://localhost;

[Jaikiran Pai]

How are you issuing that management operation? From some code? What does it look like? Or are you using the CLI to issue it? If so, how are you connecting to the CLI? Either way, the quickest way to test a connection in pool is to use the admin console to issue that request and let it manage what management operation needs to be invoked behind the scenes.

[Perry Terrance]

Jaikiran Pai wrote:How are you issuing that management operation? From some code? What does it look like? Or are you using the CLI to issue it? If so, how are you connecting to the CLI? Either way, the quickest way to test a connection in pool is to use the admin console to issue that request and let it manage what management operation needs to be invoked behind the scenes.

I am setting this up completely only using the WildFly DataSource Admin Console. The error I posted is what shows in the Admin Console messages when I click on the "Test Connection" button.

I had the JDBC driver, sqljdbc4-4.0.jar, put into the Deployments folder under my Wildfly Standalone folder - and rebooted Wildfly so the Admin Console could detect the driver.

I just goto the Datasource Page of the Admin Console and select "Add Datasource" and follow the step-by-step instructions displayed.

[Jaikiran Pai]

Is there anything that you see in the server.log of the server, if you have access to it?

[Perry Terrance]

Jaikiran Pai wrote:Is there anything that you see in the server.log of the server, if you have access to it?

What a brilliant idea! I found from that log that my authentication was messed up - and I had to put the authentication right in the Connection URL itself instead of the separate username/password section of the Console - and it worked!

[Jaikiran Pai]

Glad to know that you got it working.

It's interesting that you had to specify the username and password as part of the connection URL (and it indeed is what the documentation states here http://technet.microsoft.com/en-us/library/ms378526%28v=sql.110%29.aspx). To me, that defeats the purpose of having a (masked) password textbox/field on the UI, for this specific driver of course.

[Perry Terrance]

Jaikiran Pai wrote:Glad to know that you got it working.

It's interesting that you had to specify the username and password as part of the connection URL (and it indeed is what the documentation states here http://technet.microsoft.com/en-us/library/ms378526%28v=sql.110%29.aspx). To me, that defeats the purpose of having a (masked) password textbox/field on the UI, for this specific driver of course.

Yeah - I know right?!

I can't believe Microsoft's official JDBC Driver is kind of backwards in this regard - it should have clearly let me put in the credentials in the proper credential fields instead of this direct-method.