Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 What log shows users and connection attempts to db

Author  Topic 

mgreen84
Yak Posting Veteran

94 Posts

Posted - 2014-07-22 : 13:25:44
How and what log can I view to see when applications attempt to connect to the db?

We're getting a error when trying to connect to a particular db with a user via a java app, and I want to view the log on the database side that would show me this action taking place. How would I access such a log?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-22 : 13:50:33
If the connection is making it to the database server but erroring, then it would show up in a trace.

If the connection isn't make it to the database server, then there is nothing that can be seen there.

Could you show us the error? I can tell you if it didn't make it to the database server.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Shanky
Yak Posting Veteran

84 Posts

Posted - 2014-07-23 : 04:27:01
Can you check SQL Server errorlog of course default trace or SQL Server audit will help you more

Hope this helps

Regards
Shanky
http://social.technet.microsoft.com/wiki/contents/articles/24253.list-of-articles-by-shanky.aspx
Go to Top of Page

mgreen84
Yak Posting Veteran

94 Posts

Posted - 2014-07-23 : 12:47:08
The following message is being received off just one db on our server, with a particular user. but when we test the user credentials in sql server management studio it connects just fine.

WARNING: A potential connection leak detected for connection pool ngcmsSqlServerPool. The stack trace of the thread is provided below :
com.sun.enterprise.resource.pool.ConnectionPool.setResourceStateToBusy(ConnectionPool.java:324)
com.sun.enterprise.resource.pool.ConnectionPool.getResourceFromTransaction(ConnectionPool.java:603)
com.sun.enterprise.resource.pool.ConnectionPool.internalGetResource(ConnectionPool.java:515)
com.sun.enterprise.resource.pool.ConnectionPool.getResource(ConnectionPool.java:381)
com.sun.enterprise.resource.pool.PoolManagerImpl.getResourceFromPool(PoolManagerImpl.java:245)
com.sun.enterprise.resource.pool.PoolManagerImpl.getResource(PoolManagerImpl.java:170)
com.sun.enterprise.connectors.ConnectionManagerImpl.getResource(ConnectionManagerImpl.java:332)
com.sun.enterprise.connectors.ConnectionManagerImpl.internalGetConnection(ConnectionManagerImpl.java:301)
com.sun.enterprise.connectors.ConnectionManagerImpl.allocateConnection(ConnectionManagerImpl.java:190)
com.sun.enterprise.connectors.ConnectionManagerImpl.allocateConnection(ConnectionManagerImpl.java:165)
com.sun.enterprise.connectors.ConnectionManagerImpl.allocateConnection(ConnectionManagerImpl.java:160)
com.sun.gjc.spi.base.DataSource.getConnection(DataSource.java:113)
org.hibernate.ejb.connection.InjectedDataSourceConnectionProvider.getConnection(InjectedDataSourceConnectionProvider.java:70)
org.hibernate.internal.AbstractSessionImpl$NonContextualJdbcConnectionAccess.obtainConnection(AbstractSessionImpl.java:292)
org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.obtainConnection(LogicalConnectionImpl.java:297)
org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.getConnection(LogicalConnectionImpl.java:169)
org.hibernate.engine.jdbc.internal.proxy.ConnectionProxyHandler.extractPhysicalConnection(ConnectionProxyHandler.java:82)
org.hibernate.engine.jdbc.internal.proxy.ConnectionProxyHandler.continueInvocation(ConnectionProxyHandler.java:138)
org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
com.sun.proxy.$Proxy288.prepareStatement(Unknown Source)
org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:147)
org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:166)
org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:145)
org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1749)
org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1726)
org.hibernate.loader.Loader.doQuery(Loader.java:852)
org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:293)
org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:263)
org.hibernate.loader.Loader.loadEntity(Loader.java:2006)
org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:82)
org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:72)
org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:3887)
org.hibernate.event.internal.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:458)
org.hibernate.event.internal.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:427)
org.hibernate.event.internal.DefaultLoadEventListener.load(DefaultLoadEventListener.java:204)
org.hibernate.event.internal.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEventListener.java:260)
org.hibernate.event.internal.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:148)
org.hibernate.internal.SessionImpl.fireLoad(SessionImpl.java:1078)
org.hibernate.internal.SessionImpl.internalLoad(SessionImpl.java:1005)
org.hibernate.type.EntityType.resolveIdentifier(EntityType.java:616)
org.hibernate.type.EntityType.resolve(EntityType.java:441)
org.hibernate.engine.internal.TwoPhaseLoad.doInitializeEntity(TwoPhaseLoad.java:168)
org.hibernate.engine.internal.TwoPhaseLoad.initializeEntity(TwoPhaseLoad.java:134)
org.hibernate.loader.Loader.initializeEntitiesAndCollections(Loader.java:1019)
org.hibernate.loader.Loader.doQuery(Loader.java:898)
org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:293)
org.hibernate.loader.Loader.doList(Loader.java:2411)
org.hibernate.loader.Loader.doList(Loader.java:2397)
org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2227)
org.hibernate.loader.Loader.list(Loader.java:2222)
org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:470)
org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:355)
org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:195)
org.hibernate.internal.SessionImpl.list(SessionImpl.java:1247)
org.hibernate.internal.QueryImpl.list(QueryImpl.java:101)
org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:285)
com.sgintl.ngcms.domain.migration.dbimport.ImportPolicy.importPoliciesImpl(ImportPolicy.java:117)
com.sgintl.ngcms.domain.migration.dbimport.ImportPolicy.importPolicy(ImportPolicy.java:58)
com.sgintl.ngcms.domain.migration.dbimport.ImportPolicy$$FastClassByCGLIB$$622d4f66.invoke(<generated>)
org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:698)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:96)
org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:260)
org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:631)
com.sgintl.ngcms.domain.migration.dbimport.ImportPolicy$$EnhancerByCGLIB$$a06ee31b.importPolicy(<generated>)
com.sgintl.claims.web.controller.WorkflowControllerStatusPath.viewContract(WorkflowControllerStatusPath.java:242)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
java.lang.reflect.Method.invoke(Method.java:606)
org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:219)
org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:745)
org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:686)
org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:925)
org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:856)
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:936)
org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:827)
javax.servlet.http.HttpServlet.service(HttpServlet.java:668)
org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:812)
javax.servlet.http.HttpServlet.service(HttpServlet.java:770)
org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1550)
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:343)
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:217)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330)
org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:118)
org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:84)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:103)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:113)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:54)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:45)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilter(BasicAuthenticationFilter.java:150)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:183)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:105)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:87)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:192)
org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:160)
org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)
org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:259)
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:256)
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:217)
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:279)
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:655)
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:595)
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:161)
org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:331)
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:231)
com.sun.enterprise.v3.services.impl.ContainerMapper$AdapterCallable.call(ContainerMapper.java:317)
com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:195)
com.sun.grizzly.http.ProcessorTask.invokeAdapter(ProcessorTask.java:860)
com.sun.grizzly.http.ProcessorTask.doProcess(ProcessorTask.java:757)
com.sun.grizzly.http.ProcessorTask.process(ProcessorTask.java:1056)
com.sun.grizzly.http.DefaultProtocolFilter.execute(DefaultProtocolFilter.java:229)
com.sun.grizzly.DefaultProtocolChain.executeProtocolFilter(DefaultProtocolChain.java:137)
com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:104)
com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:90)
com.sun.grizzly.http.HttpProtocolChain.execute(HttpProtocolChain.java:79)
com.sun.grizzly.ProtocolChainContextTask.doCall(ProtocolChainContextTask.java:54)
com.sun.grizzly.SelectionKeyContextTask.call(SelectionKeyContextTask.java:59)
com.sun.grizzly.ContextTask.run(ContextTask.java:71)
com.sun.grizzly.util.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:532)
com.sun.grizzly.util.AbstractThreadPool$Worker.run(AbstractThreadPool.java:513)
java.lang.Thread.run(Thread.java:744)
Monitoring Statistics :
Monitoring Statistics for
ngcmsSqlServerPool
INFO: Reclaiming the leaked connection of pool [ ngcmsSqlServerPool ] and destroying it so as to avoid both the application that leaked the connection and any other request that can potentially acquire the same connection from the pool end up using the connection at the same time
WARN: SQL Error: 0, SQLState: 08S01
ERROR: socket closed


quote:
Originally posted by tkizer

If the connection is making it to the database server but erroring, then it would show up in a trace.

If the connection isn't make it to the database server, then there is nothing that can be seen there.

Could you show us the error? I can tell you if it didn't make it to the database server.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-23 : 13:09:15
That doesn't look like a connection issue, but rather a connection pool issue, specifically a connection pool leak. This is something that needs to be fixed in the application.

You can run a trace like I mentioned, but I don't think you're going to find any useful information based on that error. You'd be interested in the events in the Security class and possibly the Exception event in Errors and Warnings.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -