Solved - Call Flow Designer SQL error

Discussion in 'Call Flow Designer' started by BrechtD, Apr 8, 2018.

Thread Status:
Not open for further replies.
  1. BrechtD

    Joined:
    Apr 8, 2018
    Messages:
    14
    Likes Received:
    0
    Hello everyone

    I am creating a call flow application that should be able to connect a customer to his responsible agent. This information can be found in my database which is a Microsoft SQL express 2016 database running on a windows machine with the ip of 10.10.5.1
    My 3CX Server has the ip of 10.10.5.232. I can ping from my 3CX server to the Windows machine. I created a special incoming firewall rule that allows external access to port 1433.

    The problem is that when my Call Flow application tries to access the database, it gets this error:

    18/04/08 12:12:50.857|100030| Err|10|0016|: PlugIn[CallFlowTest - Callflow - MainFlow - CallID ZMPXQAXTBMEE] ERROR: Error executing last component: System.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 44 - Could not compose Service Principal Name (SPN) for Windows Integrated Authentication. Possible causes are server(s) incorrectly specified to connection API calls, Domain Name System (DNS) lookup failure or memory shortage) ---> System.Net.Internals.SocketExceptionFactory+ExtendedSocketException (0x00000005): No such device or address
    at System.Net.Dns.InternalGetHostByAddress(IPAddress address, Boolean includeIPv6)
    at System.Net.Dns.GetHostEntry(String hostNameOrAddress)
    at System.Data.SqlClient.SNI.SNIProxy.GetSqlServerSPN(String hostNameOrAddress, String portOrInstanceName)
    at System.Data.SqlClient.SNI.SNIProxy.GetSqlServerSPN(DataSource dataSource)
    at System.Data.SqlClient.SNI.SNIProxy.CreateConnectionHandle(Object callbackObject, String fullServerName, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Byte[]& instanceName, Byte[]& spnBuffer, Boolean flushCache, Boolean async, Boolean parallel, Boolean isIntegratedSecurity)
    at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling)
    at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
    at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
    at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
    at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
    at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
    at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
    at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
    at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
    at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
    at System.Data.SqlClient.SqlConnection.Open()
    at CallFlowTest.DatabaseAccessComponent.prepareCommand(IDbConnection conn, QMExtendAPI iface, String id)
    at CallFlowTest.DatabaseAccessComponent.executeReader(IDbConnection conn, QMExtendAPI iface, String id)
    at CallFlowTest.DatabaseAccessComponent.executeStatement(IDbConnection conn, QMExtendAPI iface, String id)
    at CallFlowTest.DatabaseAccessComponent.executeStart(QMExtendAPI iface, String id)
    at CallFlowTest.DatabaseAccessComponent.Start(QMExtendAPI iface, CallQueue callQueue, QueueCall queueCall, ActiveConnection activeConnection, TimerManager timerManager, Dictionary`2 variableMap, TempWavFileManager tempWavFileManager, PromptQueue promptQueue)
    at CallFlowTest.SequenceContainerComponent.Start(QMExtendAPI iface, CallQueue callQueue, QueueCall queueCall, ActiveConnection activeConnection, TimerManager timerManager, Dictionary`2 variableMap, TempWavFileManager tempWavFileManager, PromptQueue promptQueue)
    at CallFlowTest.ConditionalComponent.Start(QMExtendAPI iface, CallQueue callQueue, QueueCall queueCall, ActiveConnection activeConnection, TimerManager timerManager, Dictionary`2 variableMap, TempWavFileManager tempWavFileManager, PromptQueue promptQueue)
    at CallFlowTest.Callflow.ProcessStart()
    ClientConnectionId:00000000-0000-0000-0000-000000000000

    Is there anyone who had the same problem or that can help me with this? It's quite important for me to fix this as soon as possible.

    upload_2018-4-8_12-35-32.png
     
  2. edossantos

    edossantos Support Team
    Staff Member 3CX Support

    Joined:
    Jun 27, 2007
    Messages:
    1,345
    Likes Received:
    108
    Hello @BrechtD,

    There is a problem in the connection to the database. You need to google for the following error:
    I don't see the complete connection string you're using, but from the error message it seems you're trying to connect using Windows Authentication. That means that the CFD app will try to login using the QueueManager service user from the 3CX machine, and that user probably doesn't have access to the server. Maybe you should use user name and password instead...

    Kind regards.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    craigreilly likes this.
  3. BrechtD

    Joined:
    Apr 8, 2018
    Messages:
    14
    Likes Received:
    0
    Well guess that is correct what you are saying, but I have already tried other connection strings or even using the separate properties, but it always gives me the same error.

    I'm quite desperate right now :/
     
  4. Michael Gaillez

    Joined:
    Apr 10, 2018
    Messages:
    5
    Likes Received:
    0
    What edition of SQL Server are you using?
     
  5. Brecht Dedecker

    Joined:
    Feb 22, 2018
    Messages:
    8
    Likes Received:
    0
    SQL Server 2016 express
     
  6. Michael Gaillez

    Joined:
    Apr 10, 2018
    Messages:
    5
    Likes Received:
    0
    Did you enable TCP/IP on the server? Using the SQL Server Configuration manager.

    Also try enabling sql server browser
     
  7. edossantos

    edossantos Support Team
    Staff Member 3CX Support

    Joined:
    Jun 27, 2007
    Messages:
    1,345
    Likes Received:
    108
    Can you share the connection string you're using, removing the user and password information?

    If you're using a named instance, using a connection string is the way to go, as you can't specify the instance name using separate parameters.

    Check for more information on connection strings from here:
    https://www.connectionstrings.com/sql-server/

    Please give us all the information you can:
    - Is 3CX running on Windows or Linux?
    - What version of 3CX are you using? Send the build number from the Dashboard (below the license).
    - Are you using a named instance or default instance?
    - Ensure that you have enabled TCP connections in SQL Server.

    Kind regards.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  8. BrechtD

    Joined:
    Apr 8, 2018
    Messages:
    14
    Likes Received:
    0
    TCP/IP is enabled and the sql server browser as well
     
  9. BrechtD

    Joined:
    Apr 8, 2018
    Messages:
    14
    Likes Received:
    0
    I am currently using the 3CX Pro 16SC lincense and the build version of my system is 15.5.0

    Also the 3CX server is running in a Debian environment and I can connect to my server using the IP and the port so I guess this is not a named instance.

    And the TCP connections are enabled.
    Remote connections from SQL Server management server are enabled as well and I even created a special firewall rule that allows incoming tcp connections on port 1433.
    This is the port on which my sql server is running.

    My connectionstring is: "Data Source=tcp:10.10.5.1,1433;Initial Catalog=3CXtest;User Id=test;Password=test;"
    (I used this format cause I read that for a 3CX linux server you have to slightly change the connectionstring)
    (my 3CX server and my Windows system with my SQL server are running in the same LAN and can reach each other)

    I also tried the following format, which doesn't work either:

    Server=myServerAddress;Database=myDataBase;User Id=myUsername;
    Password=myPassword;

    Haven't already tried the following:

    Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;
    Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;

    Maybe this format could work?

    Kind regards

    Brecht
     
  10. edossantos

    edossantos Support Team
    Staff Member 3CX Support

    Joined:
    Jun 27, 2007
    Messages:
    1,345
    Likes Received:
    108
    Data Source=.\SQLExpress;For the 3CX version, can you tell us the exact build number you're using? For example, a machine running Update 4 could show 15.5.10244.4:
    upload_2018-4-10_14-14-5.png

    You can find that information in the Dashboard.

    This is important as you're running it from a Linux server, and there is a known issue in the SQL Server client libraries for Linux which cause that a CFD app can't connect to a named instance.
    According to Saurabh Singh from Microsoft:
    3CX is running .NET Core 2.0 since Update 2, and previously it was using .NET Core 1.0, so it's important that you have the latest 3CX version to avoid this issue...

    Regarding your connection string, the "Network Library" option is only for Windows, so it will not work in your case. If you're using SQL Server 2016 Express, then maybe the instance name is automatically set to "SQLExpress", so I think it's worth to try the following connection string:
    "Data Source=10.10.5.1\SQLExpress;Initial Catalog=3CXtest;Database=3CXtest;User Id=test;Password=test;"

    I'm duplicating the database name there with 2 different keys, just in case, because I'm not sure which one is considering the server...


    More questions in case this doesn't work:
    - Did you enable SQL login? Or just integrated authentication?
    - Are you able to login from the same Windows machine in which SQL Server is running? What tool do you use to login?
    - Can you use Visual Studio to create a connection to the database and then get the connection string?

    Regards.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  11. BrechtD

    Joined:
    Apr 8, 2018
    Messages:
    14
    Likes Received:
    0
    It is still not working, but I can connect to my database using the SQL Server Management Studio running on the local system on which the SQL server is running. I also enabled the SQL login.
    But I haven't tried to connect Visual Studio to the database.

    Kind Regards
     
  12. edossantos

    edossantos Support Team
    Staff Member 3CX Support

    Joined:
    Jun 27, 2007
    Messages:
    1,345
    Likes Received:
    108
    If you just enabled SQL login, then you probably were logging in with Windows Authentication, and that's why you were getting that error:
    error: 44 - Could not compose Service Principal Name (SPN) for Windows Integrated Authentication.

    Ensure that you can login with user and password. Use Visual Studio to connect, and with it you will be able to get the connection string to use.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  13. Brecht Dedecker

    Joined:
    Feb 22, 2018
    Messages:
    8
    Likes Received:
    0
    The 3CX version is 15.5.10244.4 to be specific
    Ok I'll try
     
  14. BrechtD

    Joined:
    Apr 8, 2018
    Messages:
    14
    Likes Received:
    0
     
  15. edossantos

    edossantos Support Team
    Staff Member 3CX Support

    Joined:
    Jun 27, 2007
    Messages:
    1,345
    Likes Received:
    108
    You're welcome, post your findings when you have done some tests, so we can continue helping you if needed.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  16. BrechtD

    Joined:
    Apr 8, 2018
    Messages:
    14
    Likes Received:
    0
    Alright, I was able to make a connection from visual studio.
    The connectionstring that I found was the following:

    Data Source=10.10.5.1\SQLEXPRESS;Integrated Security=False;User ID=test;Password=********;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False

    I tried this one in my Call Flow Designer app, but I still get the same error.
    It's quite strange, cause I really don't see what the problem can be.
    Maybe you see the stars at the place of the password. It tried this version and the one with a clear password instead of the stars, without result...

    Kind regards
     
  17. edossantos

    edossantos Support Team
    Staff Member 3CX Support

    Joined:
    Jun 27, 2007
    Messages:
    1,345
    Likes Received:
    108
    Please enter the clear password and not the stars, and also use double back slash to escape the data source.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  18. Brecht Dedecker

    Joined:
    Feb 22, 2018
    Messages:
    8
    Likes Received:
    0
    Well I did that :/
     
  19. edossantos

    edossantos Support Team
    Staff Member 3CX Support

    Joined:
    Jun 27, 2007
    Messages:
    1,345
    Likes Received:
    108
    I would try to connect from a remote Visual Studio using that same connection string, to discard a firewall issue.

    If the issue persists, maybe it's better to ask for help in an SQL server forum, as there you will have more people that might know some detail about SQL server that we don't know...
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  20. Brecht Dedecker

    Joined:
    Feb 22, 2018
    Messages:
    8
    Likes Received:
    0
    Ok thx for the help anyway !
     
Thread Status:
Not open for further replies.