Create Database Connections - Automate_Studio_Manager - Latest

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.

Add a database connection

1. Go to Connections > Databases.
2. Click Add. The Add New Connection panel will appear.

3. 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.
4. Click Save.

Edit a database connection

1. Go to Connections > Databases.
2. On the databases page, select the database connection and click Edit.
3. On the Update connection panel, make your changes.
4. Click Save. You will be notified that the connection details have been updated successfully.

Delete a database connection

1. Go to Connections > Databases.
2. On the databases page, select the database connection and click Delete.
3. When you are prompted to confirm the deletion, click Yes. You will be notified that the database connection has been deleted successfully.

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.