I am trying to make a new ODBC connection to our company's SQL Server Reporting Services 2012 server to reach an Oracle db, however I've been unsuccessful trying to create the connection string. I've been told the tnsnames entry is available on the server, however when I try to connect to it the connection fails.
Locally on my PC the connection string is Dsn=ROD_UAT which functions fine, but not on the SQL Server Reporting Services 2012 server. I was hoping someone might have an idea of what the string should look like and contain. The ODBC connection is to an Oracle database, I have the host name, SID, port, ID and PW.
I've tried most ODBC strings on this site, but keep getting error, "The permissions granted to user 'TUS\AYO1XTT' are insufficient for performing this operation. (rsAccessDenied) Get Online Help".
Thank you in advance!
You will need to talk to your DBA that set up the oracle client and connection on the SQL Server. The connection string will be in the tnsnames.ora file on the server. Could be anything, depends how standardised your DBA is in their approach to defining the file. Essentially the string is an alias to a complex (ish) set of parameters that point at Oracle machine/instance/listening port.
Are you saying the tnsnames.ora entry is what I should use as the connection string? I thought the presence of the tnsnames.ora file means I only have to reference the entry in the connection string.
For instance, the tnsnames entry was called ROD_UAT, so my connection string would be Dsn=ROD_UAT. This approach doesn't work of course but I don't understand why the tnsnames.ora file is even needed if I create the entry as the connection string.
I actually supplied the DBA with the tnsnames.ora string, but it does not work when I use it as the connection string.