Solved

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

Posted on 2012-04-10
13
488 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 77

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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 77

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 77

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Find data in a column which is not in  a date format 29 30
sql help 8 55
Whats wrong in this query - Select * from tableA,tableA 11 31
MS SQL BCP Extra Lines Between Records 2 19
This article is about some of the basic and important steps to be used to improve the performance in web-sphere commerce application development. 1) Always leverage the Dyna-caching facility provided by the product 2) Remove the unwanted code …
There are numerous questions about how to setup an IBM HTTP Server to be administered from WebSphere Application Server administrative console. I do hope this article will wrap things up and become a reference for this task. You need three things…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

810 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