October 16, 2019 at 10:41 am
Hi All,
Today, Users are complaining about intermittent database connectivity issues from tomcat application server which is deployed on Linux. Database server is running on Windows Server 2016 Data Center edition and Sql Server 2012 Enterprise Edition SP4.
I checked few things from sql server side.
- Able to PING db server
- Able to TELNET on 1433
- sql server services are up and running
- Tcp protocol is enabled
- Its a Default instance, running on port # 1433, able to connect using ssms ,
- Able to do .udl test from another window machine it works fine.
- Added port 1433 as an exception in firewall on Server.
As a whole I don't see any issue with sql server nor any errors in SQL Server ERRORLOG. Error seems to be not a SQL error but an error generated by informatica tool itself.
However, one of the application developer is complaining that they are seeing connectivity errors from Application srv.
They run informatica ETL tool and try to make db connection to DB Server.
Their application log has some similar errors
Error message from application log from app team
=====================================================
Error connecting to database [
[Informatica][ODBC 20101 driver]7505
Database driver error...
Function Name : Connect
Database driver error...
Function Name : Connect
Database Error: Failed to connect to database using user [kevin] and connection string [crmprod].].
Moreover, db team doesnt have any access on app server which is running on Linux.
After doing some research found this resoure. Not sure how much it will be useful.
https://kb.informatica.com/solution/23/pages/65/527185.aspx
How to approach or fix this issue? what additional questions can be asked to isolate that this is not a sql issue and it's an error thrown by Informatica tool?
Other question is, how to test connectivity issues to SQL Server on Linux box?
Appreciate your help here.
Thanks,
Sam
October 16, 2019 at 11:19 am
Maybe that question is rather something for Informatica forums? From SQL Server side only thing to do would be start a SQL Server trace, let the Dev connect and see what happens, if it's not happening on SQL Server side = ask the vendor or on forums like Stack Overflow.
Your link could work - albeit being widely offtopic for SSC - but even if we would want to help we can't because you didn't provide any details like the current odbc.ini file.
Still not sure where this attitude of throwing widely open or vague questions like "What do you think of SQL Server Security?" or expecting SSC to replace Google for any kind of application problem comes from, but I think a few forum members should revisit their attitude.
Be mad at me or not but Tomcat, Informatica and all that cr*p has not the slightest to do with SQL Server asides maybe an ODBC connection. Installing the correct ODBC Driver is part of the application setup, not SQL Server Setup.
October 16, 2019 at 11:52 am
I would also check ring_buffer & default trace for login failed events
RING_BUFFER
https://www.johnsansom.com/using-sys-dm_os_ring_buffers-troubleshoot-connectivity-issues/
Default trace:
SELECT TOP 100 te.name
,ft.SPID
,ft.LoginName
,ft.Hostname
,ft.ClientProcessID
,ft.DatabaseName
--,ft.FileName
,ft.StartTime
,dateadd(ms, ft.Duration / 1000, ft.StartTime) [FinishTime]
,ft.ApplicationName
--,ft.ObjectID
--,ft.Duration / 1000 / 1000 [Duration (s)]
,ft.TextData
,ft.RoleName
,ft.TargetUserName
,ft.TargetLoginName
FROM sys.traces st
CROSS APPLY::fn_trace_gettable(reverse(stuff(reverse(st.[path]),1,CHARINDEX('\',reverse(st.[path]),1)-1,''))+'log.trc', st.max_files) AS ft
INNER JOIN sys.trace_events AS te ON ft.EventClass = te.trace_event_id
WHERE te.name = 'Audit Login Failed'
AND st.is_default = 1
AND st.STATUS = 1
ORDER BY ft.StartTime DESC;
October 16, 2019 at 12:37 pm
Thanks Dino. I know what your saying. Still we need to get more details from the client.
I don't see any issue from the sql server side.
Even my suspicion is with the Linux network and ODBC driver issue. Probably, any firewall/ACL at network layer could be a problem and the other thing is , it is intermittent which I am able to login with Kevin.
Also, is there any command line tools available on Linux to make connectivity tests for sql server? (for example like ... sqlcmd in sql server).
October 16, 2019 at 12:42 pm
I would also check ring_buffer & default trace for login failed events
RING_BUFFER
https://www.johnsansom.com/using-sys-dm_os_ring_buffers-troubleshoot-connectivity-issues/
Default trace:
SELECT TOP 100 te.name
,ft.SPID
,ft.LoginName
,ft.Hostname
,ft.ClientProcessID
,ft.DatabaseName
--,ft.FileName
,ft.StartTime
,dateadd(ms, ft.Duration / 1000, ft.StartTime) [FinishTime]
,ft.ApplicationName
--,ft.ObjectID
--,ft.Duration / 1000 / 1000 [Duration (s)]
,ft.TextData
,ft.RoleName
,ft.TargetUserName
,ft.TargetLoginName
FROM sys.traces st
CROSS APPLY::fn_trace_gettable(reverse(stuff(reverse(st.[path]),1,CHARINDEX('\',reverse(st.[path]),1)-1,''))+'log.trc', st.max_files) AS ft
INNER JOIN sys.trace_events AS te ON ft.EventClass = te.trace_event_id
WHERE te.name = 'Audit Login Failed'
AND st.is_default = 1
AND st.STATUS = 1
ORDER BY ft.StartTime DESC;
Hi Andrey, Thank you for the query.
In our case, when I try to connect using kevin user from windows machine, where SSMS is installed , I don't see any issues. Which means there is no login failure or password mismatch.
I am suspecting those connections are not even reaching sql server and there is no way SQL can capture such information.
or else, i need to check for something like continuous ping written into a log file and see if there are any Request timeouts happening at some period.
October 16, 2019 at 1:36 pm
In our case, when I try to connect using kevin user from windows machine, where SSMS is installed , I don't see any issues. Which means there is no login failure or password mismatch.
exactly in this case Ring_buffer could help
it takes 2 minutes to check both default trace and ring_buffer and I don't see any reason not to double-check it even if you're sure the issue is somewhere in between
October 16, 2019 at 2:26 pm
vsamantha35 wrote:In our case, when I try to connect using kevin user from windows machine, where SSMS is installed , I don't see any issues. Which means there is no login failure or password mismatch.
exactly in this case Ring_buffer could help
it takes 2 minutes to check both default trace and ring_buffer and I don't see any reason not to double-check it even if you're sure the issue is somewhere in between
I can take look at ring buffer. What exactly , do u search for in the ring buffer output? any specific keywords ?
October 16, 2019 at 2:51 pm
Sys.dm_os_ring_buffers DMV can be used to troubleshoot connectivity errors, track exceptions, monitor system health, memory pressure, Non-yielding/Deadlocked schedulers and a lot more.
It means that the most content there relates to various kinds of issues.
October 16, 2019 at 4:06 pm
Error message from application log from app team
=====================================================
Error connecting to database [
[Informatica][ODBC 20101 driver]7505
Database driver error...
Function Name : Connect
Database driver error...
Function Name : Connect
Database Error: Failed to connect to database using user [kevin] and connection string [crmprod].].
Did you check the SQL Server error log around the time that this log entry is in the Informatica log? This Informatica error doesn't let you know if it's actually reaching the DB server or not. If it's reaching the DB server, this failure would be logged:
EXEC master.dbo.xp_readerrorlog 0, 1, N'Login Failed'
October 17, 2019 at 3:54 am
vsamantha35 wrote:Error message from application log from app team
=====================================================
Error connecting to database [
[Informatica][ODBC 20101 driver]7505
Database driver error...
Function Name : Connect
Database driver error...
Function Name : Connect
Database Error: Failed to connect to database using user [kevin] and connection string [crmprod].].
Did you check the SQL Server error log around the time that this log entry is in the Informatica log? This Informatica error doesn't let you know if it's actually reaching the DB server or not. If it's reaching the DB server, this failure would be logged:
EXEC master.dbo.xp_readerrorlog 0, 1, N'Login Failed'
There are no login failures in SQL SERVER ERRORLOG. those connections are not even reaching SQL Server. That's what the discussion is all about.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply