Connection to Oracle

Hi,

I have created a simple report that reads data from Oracle within Visual Studio. I have no problem with previewing the report within Visual Studio. When I deploy the report to the Report Server (same machine) and try to open the report through Report Manager, I get the following error message:

An error has occurred during report processing.

Cannot create a connection to data source 'xxxx'.

System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.

I am aware of the following issue that requires extra security to be assigned to the Oracle directories: http://support.microsoft.com/Default.aspx?kbid=870668

Even after applying the Read/Execute access to the Network Service account that is running the Reporting Service, I still get the error message.

Can anyone help me with the debugging of this issue?

Thanks,

Ben

To me, that error could mean a couple of things, not necessarily just read/execute problems.

Is the version of Oracle client equal to or greater than 8i? Do you have your Oracle homes set correctly (especially as you may have multiple clients installed on the same machine)? Did you propagate the read/execute permissions down the directory structure, including to the <oracle install dir>/network/admin folder and its contents? What account is IIS running under? Does your tns file have the correct entry in it? And lastly, are you running Win2K3 or a prior version - IIRC the NETWORKSERVICE account only started being used from 2K3 onwards, prior to that there may have been other identities that IIS and SSRS run under.

HTH!

|||Try updating the oracle client to 10.2.01|||

Hi sluggy,

I am currently running Windows XP Prof.

On this machine, I have 3 different Oracle clients (8.1, 9.2, and 10). Read/Execute has been propagated to all 3 directories starting from each of the Oracle_Home directory.

IIS (World Wide Web Publishing Service) is running under Local System.

TNS is correct. Both Visual Studio and the Report Server are running on the same machine.

How can you tell which Oracle Client the Reporting Service will use? I tried to direct it to Oracle 9.2 by modifying the both System Variables (ORACLE_HOME) and the registry (My Computer\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ORACLE_HOME=C:\ORACLE\ORA92). Neither of these seems to work.

Thanks,

Ben

|||

Hi ghw123,

I currently have Oracle Client 10.2.1 on the machine. Unfortunately, I am not sure how to force the Report Service to use this client over the 2 clients that I have on the machine.

Any suggestions?

Thanks,

Ben

|||Make sure that the 10.2.01 client is listed first in the local enviroment variable 'Path'|||

Hi ghw123,

Thanks for your help. I think my problem is related to Oracle client installation.

I have reinstalled everything on a separate machine and everything works fine when I apply the proper Read/Exec rights for the account running the Reporting Service.

Thanks,

Ben

Connection problems SQL Express

I have a serious SQL Express connection problem on one of our customers
machine. The errormessage reads: "User does not have permission to perform
this action".
Our application is a .Net 2.0 Windows Application. Using the
System.Data.SQLClient.SQLConnection class to connect. The user is logged on
with an account that grants Administrator priviledges on the machine.
The connectionstring looks like this:
Data Source=.\SQLExpress;AttachDbFilename=C:\Temp\Test. mdf;Integrated
Security=True;User Instance=True
Our company have distributed lots of applications where this works
perfectly, but suddenly we get this error on one machine.
When I use SQLCMD I can connect to the .\SQLExpress instance and attach the
file C:\Temp\Test.mdf and query the tables. When the database is attached as
"Test", I can even connect from a .Net Windows application to the database
using the following connectionstring:
Data Source=.\SQLExpress;Initial Catalog=Test;Integrated Security=True
So it seems like there is a problem with connecting using
"AttachDbFilename=C:\Temp\Test.mdf" or "User Instance=True"
Can anyone help me on this?
I would go with the obvious and assume the user running your application
doesn't have write permissions on the C:\Temp\Test.mdf or C:\Temp\Test.ldf
file. This would explain all of the symptoms you have described.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Allan G. P." <Allan G. P.@.discussions.microsoft.com> wrote in message
news:076BB143-01B4-4F42-853C-3C864D51DFC0@.microsoft.com...
>I have a serious SQL Express connection problem on one of our customers
> machine. The errormessage reads: "User does not have permission to perform
> this action".
> Our application is a .Net 2.0 Windows Application. Using the
> System.Data.SQLClient.SQLConnection class to connect. The user is logged
> on
> with an account that grants Administrator priviledges on the machine.
> The connectionstring looks like this:
> Data Source=.\SQLExpress;AttachDbFilename=C:\Temp\Test. mdf;Integrated
> Security=True;User Instance=True
> Our company have distributed lots of applications where this works
> perfectly, but suddenly we get this error on one machine.
> When I use SQLCMD I can connect to the .\SQLExpress instance and attach
> the
> file C:\Temp\Test.mdf and query the tables. When the database is attached
> as
> "Test", I can even connect from a .Net Windows application to the database
> using the following connectionstring:
> Data Source=.\SQLExpress;Initial Catalog=Test;Integrated Security=True
> So it seems like there is a problem with connecting using
> "AttachDbFilename=C:\Temp\Test.mdf" or "User Instance=True"
> Can anyone help me on this?
|||I'm not sure it would. The .ldf-file IS created when I use SQLCMD to attach
the mdf-file (still logged on as the same user). I don't believe that this
would be possible if the user didn't have write permission to the directory.
And as I described below - the user has local admin rights.
"Roger Wolter[MSFT]" wrote:

> I would go with the obvious and assume the user running your application
> doesn't have write permissions on the C:\Temp\Test.mdf or C:\Temp\Test.ldf
> file. This would explain all of the symptoms you have described.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Allan G. P." <Allan G. P.@.discussions.microsoft.com> wrote in message
> news:076BB143-01B4-4F42-853C-3C864D51DFC0@.microsoft.com...
>
|||When you connect with SQLCMD, sql is running as NetworkService or whatever
user you specified for the service to run as when you installed sql. When
you connect with the userinstance option set, sql is running as the user
running your program. If you already know what the problem is and isn't,
I'll stop wasting your time.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Allan G. P." <AllanGP@.discussions.microsoft.com> wrote in message
news:8AB9575C-3961-4EB7-B3E1-64FCEA7EEE52@.microsoft.com...[vbcol=seagreen]
> I'm not sure it would. The .ldf-file IS created when I use SQLCMD to
> attach
> the mdf-file (still logged on as the same user). I don't believe that this
> would be possible if the user didn't have write permission to the
> directory.
> And as I described below - the user has local admin rights.
> "Roger Wolter[MSFT]" wrote:

Connection problems SQL Express

I have a serious SQL Express connection problem on one of our customers
machine. The errormessage reads: "User does not have permission to perform
this action".
Our application is a .Net 2.0 Windows Application. Using the
System.Data.SQLClient.SQLConnection class to connect. The user is logged on
with an account that grants Administrator priviledges on the machine.
The connectionstring looks like this:
Data Source=. \SQLExpress;AttachDbFilename=C:\Temp\Tes
t.mdf;Integrated
Security=True;User Instance=True
Our company have distributed lots of applications where this works
perfectly, but suddenly we get this error on one machine.
When I use SQLCMD I can connect to the .\SQLExpress instance and attach the
file C:\Temp\Test.mdf and query the tables. When the database is attached as
"Test", I can even connect from a .Net Windows application to the database
using the following connectionstring:
Data Source=.\SQLExpress;Initial Catalog=Test;Integrated Security=True
So it seems like there is a problem with connecting using
"AttachDbFilename=C:\Temp\Test.mdf" or "User Instance=True"
Can anyone help me on this?I would go with the obvious and assume the user running your application
doesn't have write permissions on the C:\Temp\Test.mdf or C:\Temp\Test.ldf
file. This would explain all of the symptoms you have described.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Allan G. P." <Allan G. P.@.discussions.microsoft.com> wrote in message
news:076BB143-01B4-4F42-853C-3C864D51DFC0@.microsoft.com...
>I have a serious SQL Express connection problem on one of our customers
> machine. The errormessage reads: "User does not have permission to perform
> this action".
> Our application is a .Net 2.0 Windows Application. Using the
> System.Data.SQLClient.SQLConnection class to connect. The user is logged
> on
> with an account that grants Administrator priviledges on the machine.
> The connectionstring looks like this:
> Data Source=. \SQLExpress;AttachDbFilename=C:\Temp\Tes
t.mdf;Integrated
> Security=True;User Instance=True
> Our company have distributed lots of applications where this works
> perfectly, but suddenly we get this error on one machine.
> When I use SQLCMD I can connect to the .\SQLExpress instance and attach
> the
> file C:\Temp\Test.mdf and query the tables. When the database is attached
> as
> "Test", I can even connect from a .Net Windows application to the database
> using the following connectionstring:
> Data Source=.\SQLExpress;Initial Catalog=Test;Integrated Security=True
> So it seems like there is a problem with connecting using
> "AttachDbFilename=C:\Temp\Test.mdf" or "User Instance=True"
> Can anyone help me on this?|||I'm not sure it would. The .ldf-file IS created when I use SQLCMD to attach
the mdf-file (still logged on as the same user). I don't believe that this
would be possible if the user didn't have write permission to the directory.
And as I described below - the user has local admin rights.
"Roger Wolter[MSFT]" wrote:

> I would go with the obvious and assume the user running your application
> doesn't have write permissions on the C:\Temp\Test.mdf or C:\Temp\Test.ldf
> file. This would explain all of the symptoms you have described.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Allan G. P." <Allan G. P.@.discussions.microsoft.com> wrote in message
> news:076BB143-01B4-4F42-853C-3C864D51DFC0@.microsoft.com...
>|||When you connect with SQLCMD, sql is running as NetworkService or whatever
user you specified for the service to run as when you installed sql. When
you connect with the userinstance option set, sql is running as the user
running your program. If you already know what the problem is and isn't,
I'll stop wasting your time.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Allan G. P." <AllanGP@.discussions.microsoft.com> wrote in message
news:8AB9575C-3961-4EB7-B3E1-64FCEA7EEE52@.microsoft.com...[vbcol=seagreen]
> I'm not sure it would. The .ldf-file IS created when I use SQLCMD to
> attach
> the mdf-file (still logged on as the same user). I don't believe that this
> would be possible if the user didn't have write permission to the
> directory.
> And as I described below - the user has local admin rights.
> "Roger Wolter[MSFT]" wrote:
>