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.
