Problem connecting via ODBC - Forum - OpenEdge RDBMS - Progress Community

Problem connecting via ODBC

 Forum

Problem connecting via ODBC

This question is not answered

I'm trying to set up ODBC connections on my databases. 

OE 10.2B08

Windows 2012

64bit server

I've configured everything up as expected, but when you try and connect, even locally you get the following in the logs: 

[2017/12/05@15:17:11.569+0000] P-7328       T-7904  I FMAGENT13: (14262) Successfully connected to AdminServer on port 8842 using TCP/IP IPv4 address 192.168.101.28. 
[2017/12/05@15:17:11.570+0000] P-7328       T-7904  I FMAGENT13: (8846)  Registered with Admin Server. 
[2017/12/05@15:18:28.174+0000] P-7352       T-528   I SQLSRV2 2: (-----) SQL Server 10.2B.08 started, configuration: "l_idx_cmo_pws.defaultconfiguration" 
[2017/12/05@15:18:28.179+0000] P-7352       T-528   I SQLSRV2 2: (-----) Failure during dsmUserSecureConnect (-1)
[2017/12/05@15:18:28.185+0000] P-7352       T-528   I SQLSRV2 2: (-----) "odbcservergroup" started on IPv4 port 3000 for address 0.0.0.0, pid 7352 (0x00001cb8).
[2017/12/05@15:18:28.185+0000] P-7352       T-528   I SQLSRV2 2: (-----) Thread stack size: 1024000 (bytes).
[2017/12/05@15:18:28.185+0000] P-7352       T-528   I SQLSRV2 2: (-----) DLC from ENVIRONMENT VARIABLE is: C:\Progress\OpenEdge 
[2017/12/05@15:18:28.185+0000] P-7352       T-528   I SQLSRV2 2: (-----) WRKDIR from REGISTRY is: C:\OpenEdge\WRK\ 
[2017/12/05@15:18:28.185+0000] P-7352       T-528   I SQLSRV2 2: (-----) JDKHOME from REGISTRY is: C:\Progress\OpenEdge\jdk 
[2017/12/05@15:18:28.185+0000] P-7352       T-528   I SQLSRV2 2: (-----) JREHOME from REGISTRY is: C:\Progress\OpenEdge\jre 
[2017/12/05@15:18:28.185+0000] P-7352       T-528   I SQLSRV2 2: (-----) CLASSPATH from DEFAULT is:  
[2017/12/05@15:18:28.185+0000] P-7352       T-528   I SQLSRV2 2: (-----) PROSQL_LOCKWAIT_TIMEOUT value is: 5 seconds

I read some knowledgebases to see it could be to do with permissions, so I've made sure the Admin Service is starting as the user that created the databases and also ensured that user has full rights to the database directory. Still the same. 

Does anyone have any ideas?

All Replies
  • in which tool you try to connect with ODBC ?

    odbcad32.exe ?

  • Yes, initially, to test it works.

  • and you are using the correct windows version ? the one under the system32 or the one under the syswow64 ?

    depending on your progress version : is it 64 bit or 32 bit ?

  • it's 10.2 B so I suppose your progress version is the 32 bit install

    that you should use the one under the syswow64 dir

  • It's 64bit on the DB server, so I'm using the 64 bit version to connect locally to test it's working.

  • I don't have a 32bit driver on the 64bit server.

  • there is an article in the KB about connecting to a database using the squirrel program - it is a free download.  You might walk through those steps and see if you can connect that way.

  • Thanks cj. I can't follow those instructions as there's no JDK installed/configured and I don't have permission to install one.

  • A few things:

    1.. Is the _sqlsrv2.exe spawned (SQLSRV2 pid 7352) ?

    2. Is OE 32 or 64 bit?

    3. If you have OE 11+ installed on your PC, then you have a JDK/JRE for Squirrel. Most Windows PCs have Java installed somewhere. Open a command prompt and type java -fullversion

    5. In your example,  a SQL server was spawned and an error issued. What happens if you try to connect again using ODBC? It should try and hit PID 7352 again.

    There are a number of relevant KB entries on "dsmUserConnect" errors. Did you go through them all?

    Paul

    Paul Koufalis
    White Star Software

    pk@wss.com
    @oeDBA (https://twitter.com/oeDBA)

    ProTop: The #1 Free OpenEdge DB Monitoring Tool
    http://protop.wss.com
  • I've retested this morning.

    1) No, _sqlsrv2.exe is not spawned

    2) OE 64 bit

    3) No OE 11 available and java is an unrecognised command

    5) If I reconnect there is nothing on in the logs

    I think I've been through all the KB articles on it. I've set the admin service to use the account of the user that created the DB, I've also made sure that the user account has full access to the database folder.

    When I cancel the connect attempt I finally get something in the logs.

    [2017/12/06@12:14:46.974+0000] P-2132       T-2100  I BROKER  1: (8842)  Could not spawn a SQL server.

    [2017/12/06@12:14:46.974+0000] P-2132       T-2100  I BROKER  1: (796)   Error writing msg, socket=712 errno=10054 usernum=1 disconnected.

    [2017/12/06@12:14:48.216+0000] P-6844       T-8804  I SQLSRV2 8: (-----) SQL Server 10.2B.08 started, configuration: "l_idx_cmo_pts.defaultconfiguration"

    [2017/12/06@12:14:48.221+0000] P-6844       T-8804  I SQLSRV2 8: (-----) Failure during dsmUserSecureConnect (-1)

    [2017/12/06@12:14:48.229+0000] P-6844       T-8804  I SQLSRV2 8: (-----) "odbcservergroup" started on IPv4 port 3020 for address 0.0.0.0, pid 6844 (0x00001abc).

    [2017/12/06@12:14:48.229+0000] P-6844       T-8804  I SQLSRV2 8: (-----) Thread stack size: 1024000 (bytes).

    [2017/12/06@12:14:48.229+0000] P-6844       T-8804  I SQLSRV2 8: (-----) DLC from ENVIRONMENT VARIABLE is: C:\Progress\OpenEdge

    [2017/12/06@12:14:48.229+0000] P-6844       T-8804  I SQLSRV2 8: (-----) WRKDIR from REGISTRY is: C:\OpenEdge\WRK\

    [2017/12/06@12:14:48.229+0000] P-6844       T-8804  I SQLSRV2 8: (-----) JDKHOME from REGISTRY is: C:\Progress\OpenEdge\jdk

    [2017/12/06@12:14:48.229+0000] P-6844       T-8804  I SQLSRV2 8: (-----) JREHOME from REGISTRY is: C:\Progress\OpenEdge\jre

    [2017/12/06@12:14:48.229+0000] P-6844       T-8804  I SQLSRV2 8: (-----) CLASSPATH from DEFAULT is:  

    [2017/12/06@12:14:48.229+0000] P-6844       T-8804  I SQLSRV2 8: (-----) PROSQL_LOCKWAIT_TIMEOUT value is: 5 seconds

    [2017/12/06@12:14:48.229+0000] P-6844       T-8804  I SQLSRV2 8: (-----) Failure during dsmShutdownUser (-1). Proceeding.

  • Try to manually start a secondary login broker from the cmd line and then try to connect to that port from ODBC using the same account as used by the service to start.

    Check the perms on %WRKDIR%. Make sure that account can write to that dir.

    Paul Koufalis
    White Star Software

    pk@wss.com
    @oeDBA (https://twitter.com/oeDBA)

    ProTop: The #1 Free OpenEdge DB Monitoring Tool
    http://protop.wss.com
  • There's something very strange going on here. When I start up another broker with -ServerType SQL, I get a message saying no SQL Servers are available when I try to connect.

  • [2017/12/06@16:27:55.996+0000] P-7820       T-4972  I BROKER  8: (5644)  Started for 1234 using TCP IPV4 address 0.0.0.0, pid 7820.

    [2017/12/06@16:27:55.997+0000] P-7820       T-4972  I BROKER  8: (5645)  This is an additional broker for this protocol.

    [2017/12/06@16:27:55.998+0000] P-7820       T-4972  I BROKER  8: (8864)  This broker supports SQL server groups only.

    [2017/12/06@16:28:39.273+0000] P-7820       T-4972  I BROKER  8: (8839)  No SQL servers are available.  Try again later.

    I've basically given everyone full control over

    c:\tmp (-T)

    c:\OpenEdge

    c:\Program Files\Principal Systems

    Still no joy.

  • Is your -Mn large enough to accommodate this new broker and its servers?  If the servers table is full, the broker won't be able to spawn servers.  Also: is -n large enough to allow for the new connections?

  • might try creating a sports db and starting it with an SQL broker and then connect to that.  Would make sure it isn't a config setting on the other db.