5 Replies Latest reply on Oct 14, 2013 6:32 PM by 310b98b1-6ca9-435b-94d4-c7d0c09deb43

    IIS to Oracle DSN using Oracle Instant Client 12.1




      I hope someone here can help me, as this is proving to be a real chore.


      I need a .NET web application to be able to connect to an Oracle Database.

      I'm using IIS 7.5 on a Windows 2008R2 Web Server 0x64, and ASP.NET framework 4.0


      I know very little about Oracle I'm afraid (I'm a dedicated SQL Server man - sorry!), so I duly donwloaded and installed the 12.1 0x64 instant client.

      I followed all the instructions, created the PATH and TNS_ADMIN enivronment variables (set to c:\instantclient_12_1, where the Oracle DLLs are installed), I had a few problems with a missing 0x64 DLL (the visual .net distribution mfc100.dll) bundled with the instant client (since this is a pretty much virgin production Web Server, and I have no wish to install anything I don't need), but eventually I succesfully managed to make an ODBC System DSN connect successfully.


      The tnsnames.ora file was supplied to be by the DBA, and is as follows;


      xxx  =

        (DESCRIPTION =

          (ADDRESS_LIST =

            (ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = 1521))


          (CONNECT_DATA =

            (SID = xxxx)

            (SERVER = DEDICATED)




      Great I thought. Now for IIS.


      My IIS connection string is of the form;

      Dim conn As New Odbc.OdbcConnection("DSN=xxx;Uid=xxx;Pwd=xxx;")


      However, whenever I try to open said connection, I get the ubiquitous;

      "ERROR [08004] [Oracle][ODBC][Ora]ORA-12154: TNS:could not resolve the connect identifier specifiedERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed"


      After much research, I came to realise that I'm not the only person having the above error. It seem to be everywhere.

      It means that my IIS application presumably isn't getting to the tnsnames.ora lookup reference right?


      OK, I thought, let's add all IIS accounts to the c:\instantclient_12_1 (read and execute). It's IIS not having permissions to access the tnsnames.ora file, but still no joy.

      I also tried setting the IIS application pool identity to run under a domain account with extra privalidges, but still the same error.


      I tried playing around with various connection string permutations. I tried this one;


      Dim conn As New Odbc.OdbcConnection("Driver={Oracle in instantclient_12_1};Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xx.xx.xx.xx)(PORT=1521)))(CONNECT_DATA=(SID=xxxx)(SERVER=DEDICATED)));uid=xxx;pwd=xxx")


      and this one...


      Dim conn As New Odbc.OdbcConnection("Driver={Oracle in instantclient_12_1};Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xx.xx.xx.xx)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=xxxx)));uid=xxx;pwd=xxx")


      Both resulted in a completely different error, but just as ubiquitous, it would seem;

      ERROR [HY000] [Oracle][ODBC][Ora]ORA-12560: TNS:protocol adapter errorERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failedERROR [HY000] [Oracle][ODBC][Ora]ORA-12560: TNS:protocol adapter error"


      In order to try and home in on this, I tried writing a small VB SCript file to access the DSN using the same connection string I first tried;


      Set adoConnection = CreateObject("ADODB.Connection")

      adoConnection.ConnectionString = "DSN=xxx;Uid=xxx;Pwd=xxx;"


      This worked a treat, and I was able to connect perfectly. In order to try and drill down to the problem, I run this file as the domain user I set IIS to run as, and it worked a treat with the IIS credentials too.


      Thus I am at my wits' end. I cannot understand why IIS can't just use the the System DSN.

      Thanks to anyone in advance for their help. It is much appreciated.

        • 1. Re: IIS so to Oracle DSN using Oracle Instant Client 12.1

          TNS_ADMIN should be set to the location of tnsnames.ora and the other .ora files and not where the dlls are.

          So more like  c:\instantclient_12_1\network\ADMIN instead.

          Could there be two sets of Oracle ODBC drivers? Like x86 and x64?

          I guess you could check for multiple Oracle ODBC drivers by looking in the drivers tab in the ODBC tool under administrative tools.

          1 person found this helpful
          • 2. Re: IIS so to Oracle DSN using Oracle Instant Client 12.1

            Thanks for you reply.

            c:\instantclient_12_1 actually is the location where I placed the tnsnames.ora


            I placed all the files in this directory, the instant client files, and the odbc files as per the instant client instructions, and from reading this forum, put the tnsnames.ora file there too, as well as the missing dll.


            I'm certain that there's only the 0x64 drivers and nothign else. Plus, my DSN says it connects OK. It's just when I access this DSN via IIS it plays up.


            I've tried purposely spelling my DSN incorrectly, and confirmed that I get a missing DSN error instead....


            "ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"


            ...so I'm pretty sure that IIS is finding the DSN happily, but then falling over due to something else - presumably a tnsnames problem.

            • 3. Re: IIS so to Oracle DSN using Oracle Instant Client 12.1

              Sorry.  Didn't realize the instant client has a weird directory structure.

              I haven't seen much in terms of using the DSN itself to connect to Oracle in .Net.

              Maybe try:   

              Conn = New OdbcConnection ("Driver = {Microsoft ODBC for Oracle};Server=xxx ;uid=myuid;pwd=mypwd")

              Where xxx is the identifier found in tnsnames.


              On an unrelated note, you may want to move your connection string into the web.config.  It'll make any changes necessary a lot easier as well as assisting in deployment from dev environment to elsewhere.


              If all else fails, would you be open to trying ODP.Net?

              1 person found this helpful
              • 4. Re: IIS so to Oracle DSN using Oracle Instant Client 12.1

                Thanks again for helping. I'm home for the weekend, but will try that connection string Monday. I was under the (possibly incorrect) assumption that there wasn't an 0x64 Microsoft odbc for Oracle. That's why I was trying to use the Oracle ones, but I'll try nonetheless.

                I usually use the web.config file for connection strings, but this module is one that I seem to have inherited. It's written in VB.Net not our usual C#, and is a bit of a bolt-on that, without wanting to go into too much detail, communicates with that Oracle DB for filling in an individual's details from a number lookup.

                Yes, I'm open to using ODP.Net. Though I don't know what it is, I'm afraid, but at this point I'm happy to investigate anything. I thought this would all take a few minutes, but on Thursday I didn't leave work until 10:30pm, and still totally unsuccessfully too.

                I'll try and look it up and see what it is. Thanks for your invaluable help. I've spent so much time in this, and now I'm really up against it.

                • 5. Re: IIS so to Oracle DSN using Oracle Instant Client 12.1

                  OK. I spent some time on this, and managed to get to the bottom of the whole thing.


                  Here's what the problem was, in case anyone else faces a similar issue.


                  By deliberately giving an incorrect DSN name (new error), and an incorrect password (resulted in the same error), I was able to determine that the problem really was that the tnsnames.ora file wasn't getting referenced. No traffic was leaving the server at all, and hence the password wasn't even relevant.


                  Next step was to check that the envornment variable TNS_ADMIN was being read by IIS.


                  I used the command System.Environment.GetEnvironmentVariable("TNS_ADMIN") to prove that it was blank. This pretty much explained it all. The recently set up environment variable wasn't being picked up by IIS. It was nothing to do with permissions to the location of the tnsnames.ora file at all. This was a red herring.


                  It would appear that amazingly server restart is necessary for IIS worker process to pick up new environment variables. Basically this simple issue has cost me an awful lot of bother and time. DO NOT BELIEVE the posts that say restarting IIS or the application will pick up any changes to environmental variables - IT WILL NOT.


                  There is one further annoying issue, and that's to make sure you remove any trrailing whitespaces in the name of the odbc TNS Service Name box, but this was reletatively easier to drill down to, compared with the having to restart the production server.


                  Thank you ReubenC for yoru help. I didn't need to go down the ODP.NET route, but thanks for your suggestion. I only wish Oracle would write some notes about this, because I'm pretty sure that others will hit/have it this problem too. Would it also be too much to expect Oracle to include all the DLLS in the download as well(!)


                  Anyway, I'm glad I'm back on track.