Question about accessing SQL Server db using jdbc

[DSauve]

We have a SQL Server database that we would like to access from JDE E1. I've created a system DSN to point to the database, created a system user for myself and set up OCM mappings, and can access a table in this database from UTB. If I try to access this same table from DATABROWSER on my local web dev client, however, it fails. If I try to run a simple application to access this same table from our WLS Dev server, it also fails. I have an open SR with MOS, but am not having much luck, so I thought I'd throw this out to the JDEList community to see if you have any ideas. Thanks for any suggestions!

 

[ice_cube210]

Do you have the SQL Server JDBC drivers installed on the Web Container and also enabled in jdbj.ini

 

[DSauve]

Yes to both.

 

[craig_welton]

What is reported in the jas* and jderoot* log files?

Craig

 

[DSauve]

the jas* log file shows the following for trying to access this from my local web dev client:

24 Oct 2016 07:57:56,973 [SEVERE] <username>- [JAS] JDBCProxy.jdbcProxy(): JDBj database operation failed | UserName : <username>, Environment : DV910, User Role : *ALL com.jdedwards.database.base.JDBException: [DATABASE_CONNECT_FAILED] Database Connection failed for DataSource <datasource-name>.

 

[craig_welton]

no jderoot log? That usually has more details of an error because it's set to ALL components by default. See if it's enabled in your jdelog.properties file, E1LOG section. That or change your LOG1 section to report ALL components in the jas log (that will spew a lot of stuff though)

 

[DSauve]

jderoot log file:

27 Oct 2016 10:49:53,403 [MANDATORY] - [JAS] Initiating EnterpriseOne startup using configuration location (default_path) as '~JAS_LOCATION~'.
27 Oct 2016 10:49:53,590 [MANDATORY] - [NET] Created new JdeServerListenerThread, port=6015,hostAddr=Not Set
27 Oct 2016 10:49:53,590 [MANDATORY] - [NET] Created new JdeServerListener, port=6015, maxConn=200, maxQueue=100
27 Oct 2016 10:49:54,090 [MANDATORY] - [JAS] Successfully initialized the EnterpriseOne web engine.
27 Oct 2016 10:49:59,129 [WARN ] - [JAS] SecurityServer already exists, no override allowed
27 Oct 2016 10:50:00,704 [MANDATORY] - [JDBJ] This server is running as part of a H4A, Mobile, or StandAlone Win32 E1 client.
27 Oct 2016 10:50:00,704 [MANDATORY] - [JDBJ] The attached Win32 E1 Development Client will notify this server when to regenerate Serialized Objects automatically.
27 Oct 2016 10:50:00,704 [MANDATORY] - [JDBJ] This server will use the the <E1 Client Install Location>/DV910/spec/spec.ini file to access the package specs for on-demand generation.
27 Oct 2016 10:50:05,603 [MANDATORY] - [RUNTIME] Asynch BSFN thread poolSizeLimit = 50
27 Oct 2016 10:50:05,603 [MANDATORY] - [RUNTIME] Asyhcn BSFN thread poolSizeIncrement = 5
27 Oct 2016 10:50:05,603 [MANDATORY] - [RUNTIME] AsyncProcessingTimeout set to 720000 milliseconds.
27 Oct 2016 10:50:06,742 [MANDATORY] - [GENERATOR] GenFromJNIFlag: Setting value [false] in cache.
27 Oct 2016 10:50:53,155 [WARN ] - [RUNTIME] VTForm.lookupProductCodeByFormId(): Can not load product code: F55xxxx
27 Oct 2016 10:51:00,510 [MANDATORY] - [RUNTIME] OWVirtual thread poolSizeLimit = 400
27 Oct 2016 10:51:00,510 [MANDATORY] - [RUNTIME] OWVirtual thread poolSizeIncrement = 10
27 Oct 2016 10:51:41,627 [SEVERE] <username> - [BASE] com.jdedwards.database.base.JDBException: [DATABASE_CONNECT_FAILED] Database Connection failed for DataSource <datasource>. com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open database "<datasource-name>" requested by the login. The login failed.
27 Oct 2016 10:51:41,627 [SEVERE] <username> - [BASE] com.jdedwards.database.base.JDBException: [DATABASE_CONNECT_FAILED] Database Connection failed for DataSource <datasource>. com.jdedwards.database.base.JDBException: [DATABASE_CONNECT_FAILED] Database Connection failed for DataSource <datasource>.
27 Oct 2016 10:51:41,627 [SEVERE] <username> - [JAS] JDBCProxy.jdbcProxy(): JDBj database operation failed | UserName : <username>, Environment : DV910, User Role : *ALL com.jdedwards.database.base.JDBException: [DATABASE_CONNECT_FAILED] Database Connection failed for DataSource <datasource>.

 

[brother_of_karamazov]

We have a SQL Server database that we would like to access from JDE E1. I've created a system DSN to point to the database, created a system user for myself and set up OCM mappings, and can access a table in this database from UTB. If I try to access this same table from DATABROWSER on my local web dev client, however, it fails. If I try to run a simple application to access this same table from our WLS Dev server, it also fails. I have an open SR with MOS, but am not having much luck, so I thought I'd throw this out to the JDEList community to see if you have any ideas. Thanks for any suggestions!

Assuming you have the JDBC SQL driver set up correctly in Server Manager, did you check that the Security Server information is correct for the web server?

Also, did you set up OCM's properly to access the SQL Server in the J* environment?

 

[DSauve]

Turns out my problem was my port # in the Data Source definition was incorrect. I had assumed the default port of 1433, but it was actually another port # being used by this instance. Once I changed this, all is working fine.

Thanks!

 

[brother_of_karamazov]

Turns out my problem was my port # in the Data Source definition was incorrect. I had assumed the default port of 1433, but it was actually another port # being used by this instance. Once I changed this, all is working fine.

Thanks!

Ugh, who changes default SQL Server ports?

 

[RussellCodlin]

Named SQL Server instances by default select a dynamic port and use the listener to allocate connections. The OP probably needs to get the port set (1433 or something else) on that server because the next time the database is restarted it is likely to get a different port.

 

[brother_of_karamazov]

Named SQL Server instances by default select a dynamic port and use the listener to allocate connections. The OP probably needs to get the port set (1433 or something else) on that server because the next time the database is restarted it is likely to get a different port.

True, he did mention that it was an instance.