Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2012-04-10
13
Medium Priority
?
498 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 81

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 81

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 81

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

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

578 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