Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Connection timeout iss...
Microsoft Dynamics AX (Archived)

Connection timeout issue in AX to SQL

(0) ShareShare
ReportReport
Posted on by Girija Shankar Beuria 690

Hi all,

This is the continuation of the issue and my further findings of the thread at https://community.dynamics.com/ax/f/33/p/165475/409004#409004

Scenario:

I have a 3 Box environment (one SQL Box and 2 AOS Box) One AOS has DIXF running in it.

In AOS and DIXF box I find these types of error:

Object Server 01:  The database reported (session 2 (-AOS-)): [Microsoft][ODBC Driver Manager] Connection not open. The SQL statement was: "SELECT T1.ELEMENTHANDLE,T1.LAYERID,T1.NAME,T1.PRIMARYTABLEAOTNAME,T1.QUERYOBJECTAOTNAME,T1.CONSTRAINEDTABLE,T1.CONTEXTTYPE,T1.CONTEXTSTRING,T1.ISMODELED,T1.MODELEDQUERYDEBUGINFO,T1.ISDIRTY,T1.OPERATION,T1.RECVERSION,T1.RECID,T1.MODELEDQUERYPACKDATA FROM [<AXPRODDB_model>].[dbo].MODELSECPOLRUNTIMEVIEW T1 WHERE (CONSTRAINEDTABLE=?)"

Object Server 01:  SQL diagnostics: [Microsoft][SQL Server Native Client 10.0]Login timeout expired. Connect information was: Userid = [], Database = [<AXPRODDB>], Server = [<AXDBSERVER>], DSN = [], Other = [] 

Object Server 01:  Fatal SQL condition during login. Error message: "[Microsoft][SQL Server Native Client 10.0]Login timeout expired"

Object Server 01:  The database reported (session 2 (-AOS-)): [Microsoft][ODBC Driver Manager] Connection not open. The SQL statement was: "SELECT T1.SCOPE,T1.FLUSHVERSION,T1.MODIFIEDDATETIME,T1.RECVERSION,T1.RECID,T1.CLEARDATA,T1.FLUSHDATA FROM SYSCACHEFLUSH T1 WHERE (SCOPE=?)"

Object Server 01:  SQL diagnostics: [Microsoft][SQL Server Native Client 10.0]Login timeout expired. Connect information was: Userid = [], Database = [<AXPRODDB>], Server = [<DBSERVER>], DSN = [], Other = [] 

Object Server 01:  Fatal SQL condition during login. Error message: "[Microsoft][SQL Server Native Client 10.0]Login timeout expired"

Object Server 01:  The database reported (session 1 (-AOS-)): [Microsoft][ODBC Driver Manager] Connection not open. The SQL statement was: "SELECT T1.SESSIONID,T1.SERVERID,T1.VERSION,T1.LOGINDATETIME,T1.LOGINDATETIMETZID,T1.STATUS,T1.USERID,T1.SID,T1.USERLANGUAGE,T1.HELPLANGUAGE,T1.CLIENTTYPE,T1.SESSIONTYPE,T1.CLIENTCOMPUTER,T1.DATAPARTITION,T1.RECVERSION,T1.RECID FROM SYSCLIENTSESSIONS T1 WHERE ((STATUS=?) AND (SERVERID=?))"

Sometimes when I open a AX client that connects to ay of these AOS, I get this error in AX infolog:

Connection Timeout Expired.  The timeout period elapsed during the post-login phase.  The connection could have timed out while waiting for server to complete the login process and respond; Or it could have timed out while attempting to create multiple active connections.  The duration spent while attempting to connect to this server was - [Pre-Login] initialization=1; handshake=2; [Login] initialization=0; authentication=0; [Post-Login] complete=14003;


In the SQL box I see these errors in event log:

AXUtil operation failed : Validating

Connection Timeout Expired.  The timeout period elapsed during the post-login phase.  The connection could have timed out while waiting for server to complete the login process and respond; Or it could have timed out while attempting to create multiple active connections.  The duration spent while attempting to connect to this server was - [Pre-Login] initialization=1; handshake=2; [Login] initialization=0; authentication=0; [Post-Login] complete=15002; 


Connection Timeout Expired.  The timeout period elapsed during the post-login phase.  The connection could have timed out while waiting for server to complete the login process and respond; Or it could have timed out while attempting to create multiple active connections.  The duration spent while attempting to connect to this server was - [Pre-Login] initialization=1; handshake=2; [Login] initialization=0; authentication=0; [Post-Login] complete=15002; 
The wait operation timed out
Server: <ServerName>
Database: <AXPRODDB_model>
Layer:  
Model:  
Schema: 1.9 
AXUtil version: 6.2.1000.4051 
Executed by: administrator

I have these software and version:

AX Version :  AX 2012 R2 CU7 (6.2.1000.4051)

SQL Server Version : SQL Server 2012 RTM Standard (11.0.2100.60)

I am completely not sure what is causing this error. The network seems fine as continuous ping showed no issues.

Can this be because I am missing something that sould have been installed (considering that I have AX 2012 R2 CU7 and SQL Server 2012). May be some hotfix I am missing.

Can SQL server memory be an issue ? I do not think so because I see these errors at our night time when only the batch jobs are running and no users are working on AX.

Even when we do the Full CIL on AOS, it throws me the same error (connection timeout error)

Any suggestions/approaches would really help. Any pointers or suggestion to investigate would also help.

Regards,

Girija

*This post is locked for comments

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Connection timeout issue in AX to SQL

    Hi Girija, 

    I have also faced lot of mentioned errors. recently I am able to solve one of the below mentioned SQL errors by reinstalling complete ax components. (AOS, CLIENT and any other AX component installed).

    "AXUtil operation failed : Validating

Connection Timeout Expired.  The timeout period elapsed during the post-login phase.  The connection could have timed out while waiting for server to complete the login process and respond; Or it could have timed out while attempting to create multiple active connections.  The duration spent while attempting to connect to this server was - [Pre-Login] initialization=1; handshake=2; [Login] initialization=0; authentication=0; [Post-Login] complete=15002; 


Connection Timeout Expired.  The timeout period elapsed during the post-login phase.  The connection could have timed out while waiting for server to complete the login process and respond; Or it could have timed out while attempting to create multiple active connections.  The duration spent while attempting to connect to this server was - [Pre-Login] initialization=1; handshake=2; [Login] initialization=0; authentication=0; [Post-Login] complete=15002; 
The wait operation timed out
Server: <ServerName>
Database: <AXPRODDB_model>
Layer:  
Model:  
Schema: 1.9 
AXUtil version: 6.2.1000.4051 
Executed by: administrator"


  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Connection timeout issue in AX to SQL

    Hi Girija

    May i ask how do you solve this issue ? i was face on the same issue , thanks!

  • Suggested answer
    Koen Roks Profile Picture
    Koen Roks on at
    RE: Connection timeout issue in AX to SQL

    Hi Grijia

    I have the same issue at the moment. After some research we found out that there where a huge number of open sessions on the sql server hence the delayed logins. 

    We resolved it by looking at the statements attached to the open sessions and found out where the issues was caused.

    Kind regards,

    Koen

  • Ismael Utitiaj Profile Picture
    Ismael Utitiaj 426 on at
    RE: Connection timeout issue in AX to SQL

    Hi Girija, do you know what issues was the problem?

  • Girija Shankar Beuria Profile Picture
    Girija Shankar Beuria 690 on at
    RE: Connection timeout issue in AX to SQL

    Hi Dave ,

    No. I have been struggling with the same for quite some time now. 

    Does anyone ever faced this and able to trace what could be the issue ? Atleast can someone point me as to how to trace ODBC/SQL .net client issues on AOS Sever?

    - Girija

  • Dave Morris Profile Picture
    Dave Morris on at
    RE: Connection timeout issue in AX to SQL

    Hi Girija,

    Did you get anyway to solving this?

  • Girija Shankar Beuria Profile Picture
    Girija Shankar Beuria 690 on at
    RE: Connection timeout issue in AX to SQL

    Hi Jack,

    Thanks for looking at my post.

    No I do not have anything specific on my SQL side.

    One thing I forgot, All my machines are VM on cloud (AWS). I do not think my problem is the errors that i see in AOS boxes. But the real issue I am worried is that of "AXUtil" in my SQL box.

    The reason is that when this error happens there are two normal AX batch jobs (that write XML files in certain interval with data from AX) that get error out (in log i get error :  Error accessing database....") and it stays in "Executing" status but nothing happens till someone manually cancels and restarts it again.

    Also the error is not constsitent, sometimes I get 2-3 times, sometimes 7-8 times a day.

    This is really affecting these jobs running if the time coincides. This causes someone to monitor constantly this jobs and act if there is error or no updates.

    The problem I am having is that I have no clue what can cause this or where to start my investigation from because from Event Viewer i get no clue what causes this.

    Any pointers or ideas as to how I should investigate would really help.

    - Girija

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Connection timeout issue in AX to SQL

    Just adding, I see a lot of similar messages in my environment. Particularly the first group of messages you're showing from your AOS/DIXF.

    I have 3 AOS and a separate DB server. I receive a lot of these messages on my AOS where my active users connect, and have only seen a couple from my batch server recently, so it's definitely related to activity (requests being made).

    The events usually show up in groups, event IDs 140, 163, and 117.

    I am not however seeing any errors from the SQL side, as you have shown, nor am I seeing that message being thrown from the client.

    AX 2012 R2 CU6 (6.2.1000.6278)

    SQL 2012 SP1 Enterprise (11.0.3128.0)

    I also have DEV and TEST environments (located in a different site) which do not show these errors, but also don't have the user activity as seen in Prod.

    Originally I was concerned it was somehow related to how I setup SQL in regards to listeners and the instance names, but I'm still not sure if that is the case.

    I have my Production SQL instance assigned a unique IP address listener so that I could map a unique DNS FQDN name to the instance name (and not have to specify an instance name. For example, the server name is SERVER01, but to connect to the instance, instead of using "SERVER01\MSSQLSERVER" I use "SQLPROD" which since it has it's own listener, defaults to "SERVER01\MSSQLSERVER".

    I learned later on that AX identifies and uses the SQL server name for something however, so I had to add host file entries to the AOS servers that directed "SERVER01" to the IP address for "SQLPROD".

    I don't suppose you have anything special setup on your SQL side like that do you?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,253 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,188 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans