Data Source Names (DSN)

CREATE DSN

Description

Parameters

CREATE DSN

Description

Use the CREATE DSN statement to create a standard, single-source DSN, or a federated DSN. A federated DSN is a grouping of one or more standard DSNs. The Federation Server driver connection string options are an extension of the ODBC syntax. The connection string is a series of keyword/value pairs that are separated by semicolons. The equal sign (=) connects each keyword with its value.

Parameters

CREATE DSN dsn-name

UNDER data-service

[DESCRIPTION ‘description text’]

Note: Avoid using the backslash character when specifying DESCRIPTION, except to represent a literal backslash that should be escaped to parse correctly.

[CONNECT ‘driver-connection-string-options’]

[create-dsn-options]

[AS ADMINISTRATOR]

Here is the syntax to create a federated DSN:

CREATE DSN dsn-name

[DESCRIPTION ‘description text’] [create-dsn-options]
ADD "(" dsn-name ["," ...] ")"
[AS ADMINISTRATOR]

Note: Avoid using the backslash character when specifying DESCRIPTION, except to represent a literal backslash that should be escaped to parse correctly.

[create-dsn-options]

ADD "(" dsn-name
["," ...] ")"

[AS ADMINISTRATOR]

dsn-name

Specifies the DSN name (required). Quotation marks surrounding the DSN name are optional.

CREATE DSN dsn-name

data-service

Specifies the data service name. This option only applies to a standard DSN and is required.

UNDER data-service-name

The following naming rules apply:

The specified name must not match the reserved names of the internal BASE data service.
The specified name must not match the name of any existing data service.
The specified name must not match the name of an existing catalog for defined data services that do not support catalog names, unless the CATALOG option is used to specify a different name.

DESC[RIPTION] ‘description-text’

Description of the DSN surrounded in single quotation marks. Use for a standard or federated DSN (optional).

[DESC 'description text']

Note: Avoid using the backslash character when specifying DESCRIPTION, except to represent a literal backslash where the string should be escaped to parse correctly.

CONNECT | NOPROMPT driver-connection-string-options

Specifies connection string options for standard DSNs only. Use NOPROMPT to suppress dialog box messages.

[CONNECT | NOPROMPT driver-connection-string-options']

The connection string options for the Federation Server drivers are an extension of the ODBC syntax that specifies options as semicolon-delimited key=value pairs. For more information about connection options and advanced options specific to your data source, see the Driver Reference chapter.

create-dsn-options

Specifies what options are included with the DSN.

dsn-config-options

Specifies the options to configure with the DSN.

 dsn-config-options ::=
  "{" OPTIONS ["("] dsn-config-option
       [{"," dsn-config-option} ... ] [")"]"}"

dsn_config_option

Specifies the DSN configuration option as one of the following:

LANG FEDSQL | DS2 | NO

Specifies whether to use FedSQL, DS2 or native dialect. The dialect defaults to FedSQL for BASE DSNs and all secured DSNs. The LANG (FEDSQL) option applies to both standard and federated DSNs. Note that you can execute only the language specified in the LANG setting. FedSQL and DS2 cannot be used together.

LANG=FEDSQL, LANG=DS2, LANG=NO

SECURITY Y[ES] | T[RUE] | 1 | N[O] | F[ALSE] | 0

YES is the default value. Specifies whether to secure SQL statements before processing them. For example, if a DSN is defined to use SECURITY NO, Federation Server security is bypassed. Therefore, when you connect with the DSN, you are connecting with the privileges granted at the data source level. If a DSN is defined to use SECURITY YES, privileges granted through the Federation Server will be enforced in addition to those of the underlying data source. Used in conjunction with CSO SHARED, this feature facilitates management of a more granular security policy in SAS Federation Server over a less granular one in the back-end database.

If SECURITY is set to YES, FEDSQL is automatically set to YES.

The SECURITY option applies to both standard and federated DSNs. It corresponds to the Federation Server SQL Authorization Enforcement setting that is displayed for the DSN through the Federation Server Manager.

On a standard DSN, SECURITY NO will ignore any SQL privileges configured in the Federation Server. SECURITY YES enforces SQL privileges. See Security Permissions in the Federation Server Authorization section for a list of privileges that are affected by the SECURITY setting.

On a federated DSN, SECURITY NO indicates that the security setting of the child DSN is used. A setting of NO allows each child DSN to operate under the security settings that are configured for it. Setting SECURITY to YES activates SQL privilege enforcement for all of the child DSNs affiliated with the federated DSN. Effectively, the SECURITY setting on a federated DSN can demand privilege enforcement on child DSNs, but cannot be used to remove it.

CREDENTIALS_SEARCH_ORDER | CSO "(" cso-value [ {"," cso-value} ... ] ")"

cso-value ::= PERSONAL |SHARED

Specifies whether to use back-end credentials owned by the current user (PERSONAL) or shared among many users (SHARED). The DSN can be configured to search for either in the order specified. If a search is not specified, the default is CSO (PERSONAL,SHARED). Credentials Search Order applies only to standard DSNs.

For example, if a user owns a database login and a DSN is configured with a CSO value of PERSONAL, the DSN will use that user's database login to connect to the database. However, when the DSN is configured using CSO SHARED and the user is configured as an authorized consumer of a shared login, the DSN connects using the shared login credentials.

AS ADMINISTRATOR

[ AS ADMINISTRATOR ]

Creates the DSN using the ADMINISTRATOR role as the owner. With the ADMINISTRATOR role, the DSN is owned by the individual user. If the user is SYSTEM, the DSN is owned by SYSTEM. 'AS ADMINISTRATOR ' is optional and can be used in a standard or federated DSN.

Examples

Here are examples of the CREATE DSN statement:

CREATE DSN "DSN1" UNDER BASE DESCRIPTION 'creating DSN1' NOPROMPT
'DRIVER=BASE;CATALOG="catalog1_BASE";SCHEMA="schema1"' {OPTIONS (FEDSQL
NO,SECURITY NO)}

CREATE DSN BASEDSN under BASE CONNECT 'DATA_SERVICE=BASE;
LOCKTABLE=EXCLUSIVE'

CREATE DSN BASEDSN under BASE CONNECT 'CATALOG="catalog1_BASE";
LOCKTABLE=EXCLUSIVE'

CREATE DSN BASEDSN under BASE CONNECT '(CATALOG="catalog1_BASE";
LOCKTABLE=SHARE);(CATALOG="catalog2_BASE";LOCKTABLE=EXCLUSIVE)'

CREATE DSN BASEDSN under BASE CONNECT 'CATALOG="catalog1_BASE";
LOCKTABLE=SHARE;SCHEMA=(NAME="schema1_BASE";LOCKTABLE=EXCLUSIVE)'

CREATE DSN BASEDSN under BASE CONNECT 'CATALOG="catalog1_BASE";
LOCKTABLE=SHARE;SCHEMA=(NAME="schema1_BASE";LOCKTABLE=EXCLUSIVE);
SCHEMA=(NAME="schema2_BASE";ACCESS=TEMP)'

CREATE DSN MYDSN under MYSERV {OPTIONS CREDENTIALS_SEARCH_ORDER
(PERSONAL)}

CREATE DSN MYDSN under MYSERV {OPTIONS CREDENTIALS_SEARCH_ORDER
(PERSONAL, SHARED)}

CREATE DSN ORADSN UNDER ORASERVICE CONNECT 'ORA ENCODING=UNICODE;
ORANUMERIC=YES'

CREATE DSN "DB2Users" UNDER "Oracle Service" CONNECT 'DRIVER=Oracle;GROUP=DB2Users'
 {OPTIONS CSO PERSONAL} AS ADMINISTRATOR

CREATE DSN MYFEDERATED_DSN ADD (mydsn1, mydsn2, mydsn3) 
AS ADMINISTRATOR

ALTER DSN

Description

Use the ALTER DSN statement to change the name of a standard or federated DSN, or alter advanced options. Advanced options are specific to the data source that the DSN applies to. For information about the advanced options, see the Driver Reference topic for your data source.

Parameters

Here is the syntax to alter a standard DSN:

ALTER DSN dsn-name alter-dsn-options

ALTER DSN dsn-name RENAME TO new-dsn-name

Here is the syntax to alter a federated DSN:

ALTER DSN dsn-name ADD "(" dsn-name ["," ...] ")"

ALTER DSN dsn-name DROP "(" dsn-name ["," ...] ")"

Note: The ADD and DROP options are valid with a federated DSN only.

dsn-name

Specifies the DSN name.

alter-dsn-options

Specifies the options to alter.

alter-dsn-options

    ::= create-dsn-options

new-dsn-name

Specifies the new DSN name.

DESC[RIPTION] ‘description-text’

Description of the DSN surrounded in single quotation marks. Use for a standard or federated DSN (optional).

[DESC 'description text']

Note: Avoid using the backslash character when specifying DESCRIPTION, except to represent a literal backslash where the string should be escaped to parse correctly.

CONNECT | NOPROMPT driver-connection-string-options

Specifies connection string options for standard DSNs only. Use NOPROMPT to suppress dialog box messages.

[CONNECT | NOPROMPT driver-connection-string-options']

Examples

Here are examples of the ALTER DSN statement:

ALTER DSN "DSN1" DESC 'altering DSN1 description' NOPROMPT
'DRIVER=BASE;CATALOG="catalog1_BASE";SCHEMA=(name="schema1_BASE")'

ALTER DSN "DSN5" RENAME to DSN7

ALTER DSN "DSN7" {OPTIONS set (FEDSQL
YES,SECURITY YES)}

ALTER DSN "DSN7" {OPTIONS xset CREDENTIALS_SEARCH_ORDER(SHARED),
 xset FEDSQL NO, xset SECURITY NO}

ALTER DSN "DSN7" {OPTIONS DROP FEDSQL, DROP SECURITY}

DROP DSN

Description

Use the DROP DSN statement to drop a server-based DSN.

Parameters

DROP DSN dsn-name [FORCE]

dsn-name

Specifies the name of the DSN.

FORCE

Use FORCE to suppress error messages when the DSN does not exist.

Examples

Here are examples of the DROP DSN statement:

DROP DSN "DSN1"

DROP DSN "DSN1" FORCE

Last updated: March 6, 2018