We have a requirement that our products run on Oracle, as well as on SQL Server (around which they were originally created). Unfortunately, we have no experience developing Oracle in Oracle, but as a senior developer, I had to lead the project. So far, I have managed to connect our application to the Oracle database (I am using Oracle XE 11.2) using the following connection string:
Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=VS010-ORA11GR1)(PORT=1523))(CONNECT_DATA=(SERVICE_NAME=DEVORA)));User Id=dbo;Password=dbo;
The reason we decided to use this type of connection string is because we do not want to rely on changing tnsnames.ora on each client computer. However, as you can see, this is indicated by the Oracle user and the password associated with it. We also need to provide the ability to use the SQL Server integrated security equivalent.
According to the literature I read, for this I just need to specify / as the user ID, and then omit part of the password (since this is still ignored for Windows authentication). I also created a user in Oracle, making sure it matches the Windows user, with the following snippets:
CREATE USER "OPS$<DOMAIN>\<user>" IDENTIFIED EXTERNALLY; GRANT CONNECT,RESOURCE TO "OPS$<DOMAIN>\<user>";
I also verified that the sqlnet.ora file was on my local machine hosting the XE instance, and my dev environment contained the line:
SQLNET.AUTHENTICATION_SERVICES= (NTS)
I realized that this would allow my application to connect to the Oracle uing Windows Authentication database. However, what actually happens is that I get the following Oracle error message:
ORA-01005: empty password set; logon denied
it doesn’t make much sense, because, of course, its zero - it should be, according to the textbooks that I read.
The application is aimed at .Net Framework 3.5, we use System.Data.OracleProvider, and the actual connection, etc. processed by the corporate library 5. By the way, I know about the obsolescence of the OracleClient component, but I just want it to work before I move on to the additional complexity of changing providers.
Can someone tell me what I am missing? Did I choose the wrong connection string type? I apologize for any major mistakes, but I have always managed to avoid Oracle so far, so my knowledge about this is close to zero.
Thank you very much