Creating Database Connections

EDIS Online User Guide

To use EDIS to connect to a database for performing imports, exports, and other operations, you need to setup a service ID. A service ID is a named alias to use with EDIS tasks to access databases at run time. What’s great about service ID’s is that once the service ID is setup, the developer just needs to reference the alias and does not need the credentials. And if you need to update a password to a service ID, you simply run the service ID configuration proc (below) again.

Remarks
When a new database connection is created, it is by default accessible to all users in the EDIS_Role. To lock to a specific ID, group, or set of ID’s, see section Multi-Tenant Support.

Configuring Database Service ID’s

Stored Procedure: SSISDB.EDIS.usp_config_db_conn

Parameters

Name Description Default Value
@server_id Alias you want to use to access the database. None; Required
@server_platform The database server platform i.e. MSSQL, ORACLE, TERADATA, IBM DB2 Null
@server_provider The provider technology to use to connect to the database. Options are ODBC and OLEDB Null
@use_ado_net Flag indicating if you want to use ADO.NET to connect to the database. 0 (False)
@cn_str Connection string used to connect to the database. Null

Examples

Example 1: Adding an OLEDB Server connection

EXEC SSISDB.EDIS.usp_config_db_conn

 @server_id = 'AVT_WORKS2014'

,@server_provider = 'OLEDB'

,@server platform = 'MSSQL'

,@cn_str = 'Provider=SQLOLEDB;Data Source=DEVSRVR1\QA1;User ID = qa_usr;Password=#$v_xt3'

 

Example 2: Adding an ODBC Server Connection

EXEC SSISDB.EDIS.usp_config_db_conn

 @server_id = 'ORACLE_PRD1'

,@server_provider = 'ODBC'

,@server_platform = 'ORACLE'

,@cn_str = 'DSN=ORA_P1;UID=OA_PR_USR;PWD=#nl23$%@vR'

 

Example 3: Adding an ADO.NET, ODBC Server Connection

EXEC SSISDB.EDIS.usp_config_db_conn

 @server_id = 'ORACLE_PRD1_DNET'

,@server_provider = 'ODBC'

,@use_ado_net = 1

,@server_platform = 'ORACLE'

,@cn_str = 'DSN=ORA_P1;UID=OA_PR_USR;PWD=#nl23$%@vR'

 

Example 4: Adding an ADO.NET, OLEDB Server Connection

EXEC SSISDB.EDIS.usp_config_db_conn

 @server_id = 'AVT_WORKS2014_ADNET'

,@server_provider = 'OLEDB'

,@use_ado_net = 1

,@server_platform = 'MSSQL'

,@cn_str = 'Provider=SQLOLEDB;Data Source=DEVSRVR1\QA1;User ID = qa_usr;Password=#$v_xt3'

 

Creating SQL Server Specific database connections
EDIS makes connecting to other SQL Servers with a Windows user ID simple and easy. Below is the configuration task to create a SQL Server connection with a windows ID.

Remarks
When you create a service ID for SQL Server that connects using a Windows user ID, a credential and proxy ID are created on the host SQL Server to support this ID. Additionally, if the Windows ID is not listed as a login for the SQL Server, it is added to ensure the ID can connect at runtime.

Stored Procedure: SSISDB.EDIS.usp_config_db_conn_mssql

Parameters

Name Description Default Value
@server_id Alias to use when connecting to the SQL Server None; Required
@server_instance The SQL Server Instance to connect to None; Required
@default_db default database for the ID to connect to master
@use_integrated_security Flag indicating if you want to use integrated security (windows authentication) to connect to the SQL Server. 1 (True)
@sql_auth_user_id SQL Server user ID if using SQL authentication to connect to the server. This argument is only applicable if you are not using integrated security to connect. Empty String
@sql_auth_password SQL Server user password if using SQL authentication to connect to the server. This argument is only applicable if you are not using integrated security to connect. Empty String
@windows_auth_user_id Windows login ID to connect to the SQL Server. This argument is only applicable if you are using integrated security to connect. Empty String
@windows_auth_password Windows login ID pasword to connect to the SQL Server. This argument is only applicable if you are using integrated security to connect. Empty String

Examples

Example 1: Creating a SQL Server service ID using Windows Authentication

EXEC SSISDB.EDIS.usp_config_db_conn_mssql

 @server_d = 'SQL_DEV01'

,@server_instance = 'MDDT\DEV01'

,@use_integrated_security = 1

,@windows_auth_user_id = 'PR_USERS\_svc_user'

,@windows_auth_password = '$!dx_mg*c!'

 

Example 1: Creating a SQL Server service ID using SQL Authentication

EXEC SSISDB.EDIS.usp_config_db_conn_mssql

 @server_d = 'SQL_DEV01'

,@server_instance = 'MDDT\DEV01'

,@use_integrated_security = 0

,@sql_auth_user_id = 'power_user'

,@sql_auth_password = '$$xrTy!cn4'