December 18, 2014 at 9:27 am
Hi all,
I maintain a Java Servlet application that connects to a SQL server database using JNDI and Tomcat. Recently we migrated the SQL server database from Windows server 2003 to Windows server 2008 R2 and from SQL Server 2005 to SQL Server 2008 R2, but since we migrated the database the connection has stopped working. I upgraded the sqljdbc.jar to sqljdbc4.jar but this has made no difference.
Is there a SQL Server configuration I am missing here? I also tried checking the connection using ODBC in Windows but the connection failed. What am I doing wrong?
Thanks in advance
ajfarroll
December 18, 2014 at 9:42 am
Have you asked the DBA to check the SQL server logs on the DB for the login failure, or run a trace when trying to log on, looking to see how its trying to connect as that might give an indication as to where the failure is.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
December 18, 2014 at 10:02 am
Thanks for prompt response.
I checked the sql server text logs and they are showing no information of the attempted connection. I doubt if there will be any relevant entry in the SQL Server transaction log in the database. As for running a trace I have never performed that before. I will investigate and have a go.
Thanks
December 19, 2014 at 2:13 am
ajfarroll (12/18/2014)
Thanks for prompt response.I checked the sql server text logs and they are showing no information of the attempted connection. I doubt if there will be any relevant entry in the SQL Server transaction log in the database. As for running a trace I have never performed that before. I will investigate and have a go.
Thanks
The fact the login attempt isn't showing up in the log could indicate that the credentials arnt making it to the SQL server as I would have expected to see something.
I doubt you'll see anything on a trace either.
Can you connect using a SSMS using the credentials in the ODBC connection? assuming its using an SQL Server user rather than windows Authentication.
If not Check that the SQL server instance is set up in Mixed mode (SQL SERVER and Windows authentication mode).
_________________________________________________________________________
SSC Guide to Posting and Best Practices
December 19, 2014 at 2:19 am
Dear Ajfarroll,
1. Are you able to ping the database server from the application server?
2. Are you able to connect the SQL Server using SSMS
3. Check the firewall on the database server and application server and add an exception of port 1433 if not already present.
4. SQL Server Database and Browser service should be turned on. (Needs to be check from SQL Server Configuration Manager on the database server).
5. Check for the SQL Server Network protocols. Named Pipes, TCP/IP etc
I am pretty sure you will find your issue in one of these points.
Hope it helps..!!
Regards
Shafat Husain
Regards,
Shafat Husain
🙂
And Your Lord Never Forgets...!! (64:19 -- Quran)
December 19, 2014 at 5:05 am
Thanks Jason-299789
When I attempt a connection through the servlet application and then look at the log in the SQL Server Management Studio, the only entry is as follows;
Backup,Unknown,Log was backed up. Database: Images<c/> creation date(time): 2013/11/23(10:27:57)<c/> first LSN: 322050:59755:1<c/> last LSN: 322050:85856:1<c/> number of dump devices: 1<c/> device information: (FILE=17<c/> TYPE=DISK: {'ImagesLogBac'}). This is an informational message only. No user action is required.
However, I have a possible solution and going to check what you think. I have attempted to connect using Oracles SQL Developer app from the application server to the database server (Ifound a way of doing this by referencing the sqljdbc in SQL Developer) but it gave me a TCP/IP error, although this worked fine from my PC and another server within the network. I also tested the connection using a Windows ODBC connection and this also failed although again it was successful from my PC and the other server within our network. I also tried changing the the server name for the IP address in the ODBC connection attempt but it also failed. Also, I am not allowed to install SSMS on the application server. HOWEVER, I changed the server name with the IP address in the Tomcat JNDI entry and this time SUCCESSFUL CONNECTION!!!! Does this mean that the database server is listening with TCP\IP instead of "named pipe" and perhaps the old server used "named pipe"?
Thanks and I look forward to your feedback.
AJFarroll
December 19, 2014 at 5:09 am
Thanks Shafat Husain
I am not allowed to install SSMS on the application server but I used ODBC explained in section below
I cannot ping using the server name but I can ping using the IP address
The SQL Database and Browser services are indeed running
When I attempt a connection through the servlet application and then look at the log in the SQL Server Management Studio, the only entry is as follows;
Backup,Unknown,Log was backed up. Database: Images<c/> creation date(time): 2013/11/23(10:27:57)<c/> first LSN: 322050:59755:1<c/> last LSN: 322050:85856:1<c/> number of dump devices: 1<c/> device information: (FILE=17<c/> TYPE=DISK: {'ImagesLogBac'}). This is an informational message only. No user action is required.
However, I have a possible solution and going to check what you think. I have attempted to connect using Oracles SQL Developer app from the application server to the database server (Ifound a way of doing this by referencing the sqljdbc in SQL Developer) but it gave me a TCP/IP error, although this worked fine from my PC and another server within the network. I also tested the connection using a Windows ODBC connection and this also failed although again it was successful from my PC and the other server within our network. I also tried changing the the server name for the IP address in the ODBC connection attempt but it also failed. Also, I am not allowed to install SSMS on the application server. HOWEVER, I changed the server name with the IP address in the Tomcat JNDI entry and this time SUCCESSFUL CONNECTION!!!! Does this mean that the database server is listening with TCP\IP instead of "named pipe" and perhaps the old server used "named pipe"?
Thanks and I look forward to your feedback.
AJFarroll
December 19, 2014 at 7:52 am
I have resolved this problem. It was an entry required in the known hosts!!!! But thank you all for your input and assistance I have learned from this question I raised.
Regards
AJFarroll
December 19, 2014 at 9:31 am
Glad you sorted your issue, Id have never thought about the hosts file.
I quite understand about not putting SSMS on the server, bit of a possible security issue, but being able to connect from a PC shows that SQL was accepting remote connections, so the TCP/IP etc are taken out of the equation.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
December 19, 2014 at 9:38 am
Hi AJFarroll ,
The error message is related to a problem of backup ( if I have been able to understand it , but as my understanding of the English is far to be perfect ). You should check whether the connection/login errors are logged in the error log of the SQL Server instance ( the successful and failed logins are not audited as default : right-click on the server name , select Properties then Security and look at the part login auditing ).
An error message for connection error is clear usually , but I have to temper this idea as I am only using .Net Framework and the System.Data.SqlClient class for which the messages are easy to understand as it can detect any type of error about ports , or server name or database name ( sorry , I have no time to learn ODBC , or Java and my most recent uses of ORACLE were related to ORACLE 7 , a good but very old version ).
Be careful about the named pipes which need a long and complicated expression for which to create an error is easy.
Have a nice day
December 19, 2014 at 11:30 am
ajfarroll (12/19/2014)
I have resolved this problem. It was an entry required in the known hosts!!!! But thank you all for your input and assistance I have learned from this question I raised.Regards
AJFarroll
This seems to be dns problem, because you were able to connect with IP and adding an entry to hosts file fixed the issue. Adding entry to hosts file is rather error-prone.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply