Solved

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

Posted on 2012-04-10
13
485 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
  • 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 76

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
 
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 76

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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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 76

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

760 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now