Skip to main content

Database Connections

We cannot cover all database systems here. However, you might find that one or two of these tips point you toward a solution for “your” system.

Connection Fails – Local and Remote​

The “Check connection” button returns an error. The error message often provides the correct clue.

  • Is the server named correctly? For example, for MySQL or MS SQL, specifying the IP address is sufficient; for MS SQL Express, however, the instance must be named, e.g.: localhost\SQLEXPRESS
  • Is the database name spelled correctly? Does the user have access rights to the database? – For example, in MS SQL, databases are assigned to individual “Logins”. Ask your database administrator!
  • MS SQL: Is the correct authentication method set for the user (“SQL Server and Windows Authentication mode” for user/password access)?
  • Are the username and password correct? Be sure to use the correct case!
  • Integrated security is enabled: The connection test is not meaningful. Integrated security uses the local Windows user or domain user under which the respective program was started to log in. In the configuration environment, this is usually the username you used to log in to the computer; however, the router service runs by default as “System” (sufficient for local access to MS SQL, but not for remote access).
  • Are the required services running (MS SQL: SQL Server and SQL Server Browser, via Server Configuration Manager or “Control Panel – Administrative Tools – Services”)?

Connection cannot be established – remote only​

The “Check connection” button returns an error. The error message often provides the correct clue. In addition to the points mentioned above, please check the following:

  • Is the network connection active? Can you ping the database server (“Start – Run – ping <IP>”)? Ask your network administrator!
  • Is the database server’s port open in the firewalls on both computers? Ask your network administrator to enable communication between the router and the database server on this port. To troubleshoot the issue, you may temporarily disable the firewall; if it was the cause, re-enable it immediately and configure the port forwarding instead. The ports for the OPC router itself are listed under Network Ports.
  • Is the server named correctly? For example, for MySQL or MS SQL, specifying the IP address is sufficient; for MS SQL Express, however, the instance must be named, e.g.: 192.168.5.184\SQLEXPRESS or computer name\SQLEXPRESS
  • Does the database allow remote connections (e.g., in MS SQL, this must be enabled for the corresponding user)?
  • Are the appropriate protocols enabled (MS SQL: In the Server Configuration Manager, depending on the connection, enable “Named Pipes” or “TCP/IP” in the Network and Native Client configurations)?

Connection is established, but no tables are visible (MS SQL)​

The “Check connection” button returns “OK”. However, no tables can be selected in the transfer object's engineering.

  • Does the user have the appropriate permissions for the database? – Depending on the user’s role, the user may not be permitted to “List Tables.”
    • At “Login Properties”, assign “db_datareader” and “db_datawriter” to the corresponding login in the user mapping for the required database. Note: The “db_owner” role is required to execute stored procedures. – Contact your database administrator.
    • At most, the user can also be granted administrator privileges on the database or the system (e.g., the “sysadmin” role). However, this should only be done temporarily for troubleshooting purposes.

Stored procedures cannot be executed. (MS SQL)​

  • Does the user have the appropriate permissions for the database?
    • Under “Login Properties”, the corresponding login must be assigned the "db_owner" role in the user mapping for the required database.

Stored procedures are aborted.​

  • Are the connection’s command timeout and the corresponding connection’s timeout set correctly?

The connection is established, but the database connection does not appear in the DB transfer object.​

The connection test was successful, but the DB connection does not appear in the DB transfer object in the flow configuration, or an error is returned.

  • You must save the DB connection by clicking the “OK” button before it becomes available.

No data transfer occurs at runtime​

The connection was established without any problems at design time. At runtime, the status indicator reports a connection error. Check the message in the event log or in the log file. If it mentions SqlException with a failed login attempt for the user NT-AUTORITÄT\ANONYMOUS-ANMELDUNG (NT AUTHORITY\ANONYMOUS LOGON on English-language systems), then the following applies:

  • If you are accessing the database server remotely and have “integrated security” enabled, you must not run the service as the System user. Configure the service to run under the domain user.