You can perform following on the Databases page:
- View database connections
- Add a new database connection
- Edit a database connection
- Delete a database connection
View database connections
You can view the following database connection information on the Connections menu, on the Databases tab:
- Connection Name
- Scope Level
- Connection Type
- Server Name
- Authentication Type
- Database Name
- Applies to
On the Databases tab, you can filter on any column.
On the Administrator App, you can view global database connections only. On the User App however, you can view database connections created at the User App (local) level and at the Administrator App (global) level.
Add a database connection
- Go to Connections > Databases.
- Click Add. The Add New Connection panel will appear.
-
On the Add New Connection panel, do the following:
- Enter a Connection name.
- Select from the following Connection types:
- SQL Server – Enter the SQL Server name, including the complete path and name of the database server (for example: DBSERVER1\SQLEXPRESS). Enter the Database name and select an option in the Authentication drop-down list (see below for details). Enter details as prompted.
- ODBC (Microsoft SQL) – Enter the connection string.
- ODBC (Oracle) – Enter the connection string.
- ODBC (IBM Db2) – Enter the connection string.
- OLEDB – Enter the connection string.
- Click Save.
Edit a database connection
- Go to Connections > Databases.
- On the databases page, select the database connection and click Edit.
- On the Update connection panel, make your changes.
- Click Save. You will be notified that the connection details have been updated successfully.
The Connection Name field cannot be edited.
Delete a database connection
- Go to Connections > Databases.
- On the databases page, select the database connection and click Delete.
- When you are prompted to confirm the deletion, click Yes. You will be notified that the database connection has been deleted successfully.
Notes: If a deleted database connection is added again with the same configuration and connection name, the solutions will work as they did before. Users will have to add all the details for this database connection again; there is no need to redeploy the solution in which this connection was used.
Authentication modes
SQL User: The SQL user is used to connect to the SQL Server. This user should be created in SQL and have read permission on the database used in this connection.
Windows Specified Account: The specified Windows user is used to connect to the SQL Server. This user should have login access to SQL Server and should have read permission on the database used in this connection.
Windows Default Account: The application pool account is used to connect to the SQL Server. This user should have login access to SQL Server and should have read permission on the database used in this connection.
Adding connection strings
ODBC Microsoft SQL server connections
If you are connecting to Microsoft SQL Server, your connection string will look similar to this:
DRIVER={SQL Server};SERVER=localhost;DATABASE=dbws;UID=svdb;PWD=ws;OPTION=N
- {SQL Server} is the driver name; this varies by version
- localhost is the database server
- dbws is the database name
- svdb and ws are the username and password which must be used to connect to the SQL Server database
- For more information on OPTION parameter, please refer to Microsoft SQL Server documentation
ODBC Oracle connections
If you are connecting to an Oracle database, your connection string will look similar to this:
Driver={Oracle in instantclient_12_1};dbq=<hostname_or_ip_address>:<port_number>/<instance_name>;Uid=<userid>;Pwd=<password>;
For example, if you have a database server called oracle-db.company.local running a TNS listener on port 1521, with a database instance called DB1 that is accessible by user dbuser and password dbpass, the connection string would be the following:
Driver={Oracle in instantclient_12_1};dbq=oracle-db.company.local:1521/DB1;Uid=dbuser;Pwd=dbpass;
Oracle connection string example:
Driver={Oracle in instantclient_12_1};dbq=oracle-db.company.local:1521/DB1;Uid=dbuser;Pwd=dbpass;
- {Oracle in instantclient_12_1} is the driver name; this varies by version
- db.company.local is the Oracle Server followed by the port :1521 and then /databasename
- Uid is the username and Pwd is the password
The driver string refers to the specific driver that is installed on the Workflow server.
Contact your database administrator if you need help configuring your database string.
If the database user needs to access tables that are not in their default schema, do one of the following:
- Create a trigger for the database user which changes the current schema as the user logs in to the schema they need to access from your Solution.
- Create a synonym for the database user to the table they wish to use in the query.
ODBC DB2 connections
If you are connecting to DB2, your connection string will look similar to this:
Driver={IBM DB2 ODBC DRIVER - C_apps_clidriver};Database=sample;Hostname=db2server;Port=50000;Protocol=TCPIP;Uid=DB2;Pwd=DB2PASS
- {IBM DB2 ODBC Driver - C_apps_clidriver} is the driver name; this varies by version
- Hostname is the database server
- Port is the port number to communicate to the database
- Protocol is the communication protocol
- Uid is the userid and Pwd is the password
If the database user needs to access tables that are not in their default schema, do the following:
- Append a CurrentSchema parameter to the connection string. For example:
Driver={IBM DB2 ODBC DRIVER - C_apps_clidriver};Database=sample;Hostname=db2server;Port=50000;Protocol=TCPIP;Uid=DB2;Pwd=DB2PASS;CurrentSchema=SYSIBM;
OLEDB connections
If you are connecting using OLEDB, your connection string may look similar to this:
Provider=sqloledb;Data Source=myServer;Initial Catalog=myDatabase;User Id=user1;Password=pwd;
- sqloledb is the provider name
- myserver is the database server
- myBatabase is the database name
- user1 and pwd are the username and password you need to connect to the database
Applies to field:
Studio – If selected, the connection will be available for Query output to SQL Server for Query solutions.