Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Login to sql server 2008 from websphere application server fails after sometime

Posted on 2012-04-10
13
Medium Priority
?
496 Views
Last Modified: 2012-04-23
Hi Experts,

I have an application running on a unix box using Websphere 6.1 AppServer and using a SQL Server 2008 on a seperate windows machine.

My application is able to connect to the DB as i am able to login to my application immediately after the application reboot. However, after sometime, for some reason my login keeps failing and all I see in the logs is a custom exception message with a Null Pointer.

If i restart my application and then try logging in, it all works fine again. but only for sometime and then I experience the same issues.

My DB connection file has the following content.
Let me know what to check.

#-------------------------------------------------------------------------
# Constants used in Mint Application
# $Header$
#-------------------------------------------------------------------------

#--------------------------------------------------------------------------
# Database Connection attributes for SQL Server 2005
#---------------------------------------------------------------------------
SQLSERVER.IP=//XX.XXX.XX.XX
SQLSERVER.PORTNUMBER=1433
SQLSERVER.DATABASENAME=XXXXXX
SQLSERVER.SELECTMETHOD=Direct
SQLSERVER.DRIVER=com.microsoft.sqlserver.jdbc.SQLServerDriver
SQLSERVER.USERNAME=XXX
SQLSERVER.PASSWORD=XXXXX
#--------------------------------------------------------------------------
# Other  attributes
# Session time period is in Seconds..
# Password Expiry period is in Days.
#---------------------------------------------------------------------------
SESSION.TIMEOUTPERIOD=2400
PASSWORD.EXPIRYPERIOD=30



Regards,
Amit
0
Comment
Question by:amitchadda
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +1
13 Comments
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 37830476
Since the problem goes away after a restart of the application server (and not the database server), it's likely with the way your application is connecting to the database, and not with the connection properties themselves. Since you're able to connect in the beginning, your connection settings and firewall are set up properly.

Can you post a copy of the code you're using to actually connect to the database? Also, have you checked the database server (using something like SQL Server Profiler) during these failures to see if the database server itself is returning an error, or does it seem that your application server isn't even attempting to connect before it returns the error message?
0
 
LVL 80

Expert Comment

by:arnold
ID: 37830670
Are you poling connections, what is the error on the SQL server for denying the connection?
To ryanmccully's point you likely do to properly close the SQL connection.

Next time it happens run the following on either
Linux
netstat -an | grep ":1433" | wc -l
This will return the number of connections

Windows
netstat -an | find ":1433"
To see how many connection to the server there are.
0
 

Author Comment

by:amitchadda
ID: 37831293
Hey..Thx guys for these updates.

However, I would like to mention now that I deployed the same application on another environment and tried connecting to the same database server sql server 2008. And in this environment I am not facing any issues while logging in. But the issues with my other application which is connecting to the same database continue.

I am now confused as to why is one application connecting just fine thorughout...and the same application deployed on a different server stops logging in :(
database seems all fine thorughout as it keeps accepting connections from the other application...
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 41

Expert Comment

by:HonorGod
ID: 37835307
Might you be running into a limitation related to the number of connections allowed to the database?

Do the connections remain active, or are they closed gracefully, and completely?
0
 

Author Comment

by:amitchadda
ID: 37836207
But even when my application running on one server stops making the connection to the database, my same application running on a different server continues to make connections without any issues.
0
 
LVL 80

Expert Comment

by:arnold
ID: 37836233
I have no idea what you have setup, so tell me that x does one thing while y does another things sheds no light on the issue. Do you use dsn? And connection pooling?
This is why the other experts referred to seeing what your code looks like for the connection.
I.e do you store the credentials in the app session such that on one server this is being overwritten in error?
0
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 37836251
Plain and simple, if the servers are behaving differently, then something about them is different. If they're pointed to the same database on the same server, but one connects and one does, then it's something to do with the way the server is connecting (or disposing of connections once it's done).

Have you compared the configuration files? Is there any custom code involved, and have you confirmed that it matches? Is all relevant software the same release/patch version on both servers? As Arnold mentioned, we don't have much to go off here, and "it works here but not there" doesn't tell us anything about here or there that might shed light on the situation. Please provide some more details about the configuration and any relevant configuration you've done, as well as any way the working/non-working servers are different, and we can try and help figure out the reasoning from there.
0
 

Author Comment

by:amitchadda
ID: 37836330
Hi guys, apologies for being unclear, this was very unintentional.

Let me explain things again more clearly.

Attached is the factory class where the db connections are made. The parameters for making these connections is read from a file Constant_en.properties whose contents are as below-

#-------------------------------------------------------------------------
# Constants used in Mint Application
# $Header$
#-------------------------------------------------------------------------

#--------------------------------------------------------------------------
# Database Connection attributes for SQL Server 2005
#---------------------------------------------------------------------------
SQLSERVER.IP=//XX.XXX.XX.XX
SQLSERVER.PORTNUMBER=1433
SQLSERVER.DATABASENAME=XXXXXX
SQLSERVER.SELECTMETHOD=Direct
SQLSERVER.DRIVER=com.microsoft.sqlserver.jdbc.SQLServerDriver
SQLSERVER.USERNAME=XXX
SQLSERVER.PASSWORD=XXXXX
#--------------------------------------------------------------------------
# Other  attributes
# Session time period is in Seconds..
# Password Expiry period is in Days.
#---------------------------------------------------------------------------
SESSION.TIMEOUTPERIOD=2400
PASSWORD.EXPIRYPERIOD=30
#----------------------------------------------------------------------------

We had the application running in our environment connecting to sql server 2005. There was a business requirement of moving this application to host it on a third party server. And at the same time, also to upgarde the db server to sql server 2008.

The new servers were provided to us by the third party with some very limit control access. We performed the installation of webspphere etc to create the same environment as that existed for the original environment. We then deployed the same application on this new environment. Meanwhile, the database import from sql server 2005 to sql server 2008 was done in the background and we got a ready windows machine running sql server 2008 to be used.

We then changed the properties of the configuration file mentioned above so that it pointed to this new sql server 2008 and then brought our application up. The login worked just fine, which proved that the database connection was being established fine. This was also confirmed by our application server logs which logged statements of a successful db connection and login. Then we noticed that after a few hours, the login started failing and no connections were being established to the new db. We also found that on application reboot, this problem went away for sometime but then the login's start failing again after a while.

We then decided to test our own environment which had the same application code to point to the new sql server 2008. We changed the db connection properties file with the same details as we used in the new environment and were successfully able to connect to the db. Moreover, in our own environment we did not face the same issue of login failing after sometime.

The code deployed on both the environments is the same, and they are connecting to the same sql server 2008 database.

I hope this makes sense now.
0
 
LVL 80

Expert Comment

by:arnold
ID: 37837765
Are the websphere and the SQL server that the application accesses housed on the same third party server?
can you run netstat -no  on the websphere server when this issue occurs to see whether connections are being initiated syn_sent events but not responses are coming back?
0
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 37838777
From your detailed description, it sounds like they're not connecting to the same SQL database, though - one is connecting to your hosted database (and it's not working after a while), and the other is connecting to a local database (and it works). While they may be the same copy of the database and have all the same data in them, there's something about the configuration that's different, and if they're on different servers, then it likely has to do with the remote hosted server configuration.

Since you've shown that your application works properly when you connect it to a local database, is this something you can open up a support ticket regarding with your hosted database vendor? It seems appropriate since the connections fail when you point at their server, but not when you access a local copy of the data.
0
 
LVL 41

Expert Comment

by:HonorGod
ID: 37839255
> But even when my application running on one server stops making the connection to the database, my same application running on a different server continues to make connections without any issues.

  The connection pool settings for one application server only limit, or restrict the connections from that application server.  Other application servers may have different, and possibly larger, connection pool settings.

  This link may help with these settings, but I'm having trouble, at the moment, trying to get this page to display:

http://publib.boulder.ibm.com/infocenter/wasinfo/v7r0/index.jsp?topic=/com.ibm.websphere.base.doc/info/aes/ae/udat_conpoolset.html
0
 

Accepted Solution

by:
amitchadda earned 0 total points
ID: 37859955
Hi Guys,

I found the issue to be with the time difference between my new application server and new database server.

they had a time difference of two hours, and in the application code, there was a snippet written that allowed max no. of retries to login to db based on the current time of db and the app server.

I fixed this by setting the app server and db server time in sync with each other.

Thanks for you help throughout!
0
 

Author Closing Comment

by:amitchadda
ID: 37879938
Fixed on my own.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question