CODEWITHSUNDEEP
sql-serveroracle-databasessis
Klaus C
Klaus C

Reputation: 21

SSIS connection to Oracle using Windows authentication

Using SSIS for SQL Server (2012 or later) Standard Edition, I want to connect to a remote Oracle database using Windows authentication.

In my tests, this works fine when using sqlplus:
sqlplus /@MyRemoteConnection

In SSIS (using either ADO.NET or OLE DB Connection Managers), the connection succeeds when specifying the user id and password. The connection fails when specifying the "/" user (without a password) in the SSIS connection.

Is authentication through Windows supported at all for SSIS connections to Oracle?
If yes, how do I do this?

If Windows authentication is not supported, are there other tools which allow me to connect from SQL Server Standard Edition (that's why e.g. Attunity cannot be used)?

All suggestions are highly appreciated!

Upvotes: 1

Views: 2100

Answers (1)

Klaus C
Klaus C

Reputation: 21

Yes, Windows authentication from SQL Server Standard Edition to Oracle is supported.

A valid ADO.NET connection string (e.g. using Visual Studio (SSIS), or the Import Wizard in SQL Server Management Studio) may look like this:

Data Source=<host name>:<port number>/<database name>;Integrated Security=SSPI;

An example: 

Data Source=my_host_name.com:1521/ORCL;Integrated Security=SSPI;

For Windows Authentication, "Integrated Security" needs to be set to "yes", "True" or "SSPI".

A valid OLE DB connection string looks like this:

Data Source=<host name>:<port number>/<database name>;User ID=/Provider=OraOLEDB.Oracle.1;

Example: 

Data Source=my_host_name.com:1521/ORCL;User ID=/Provider=OraOLEDB.Oracle.1;

Please note: The port number is optional and can be omitted if the standard port number (normally 1521) is used. When using SSIS, the Connection Manager may complain with the message "The given path's format is not supported." This happens if the port number is specified in the connection string. The connection string (including the port number) will work nevertheless, if the connection string is filled by a variable in an expression.

Further requirements:

On the file system (both on the client and the server), there has to exist a file sqlnet.ora.
This file has to contain the string

SQLNET.AUTHENTICATION_SERVICES= (NTS)

On the database server, and when using a full Oracle client, the file sqlnet.ora should be located in the ORACLE_HOME\Network\Admin directory.
ORACLE_HOME normally is defined in the registry, under
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE

If an Instant Client has been installed, the file sqlnet.ora can be put in the "network\admin" subdirectory under the directory where the Instant Client is located. Please note that the Instant Client will not look into the registry to use (or evaluate) ORACLE_HOME. Therefore sqlnet.ora files located there won't be read when trying to connect to the Oracle server!
(That's the reason why my connections failed all the time.)

Another option is to specify a directory for sqlnet.ora by setting the environment variable TNS_ADMIN, e.g. in the Windows Control Panel. Example: TNS_ADMIN=C:\TNS_ADMIN
Then, put the file sqlnet.ora into this directory.

On the Oracle server, the Windows user needs to exist. Example: 

create user "OPS$MYDOMAIN\MY_USER_NAME" identified externally;
grant create session, alter session to "OPS$MYDOMAIN\MY_USER_NAME";

(additional privileges may be required.)

Then it should be possible to connect to Oracle as Windows user MY_USER_NAME, using Windows Authentication.

Upvotes: 1

Related Questions