error - Cannot create a connection to data source

Hello.
I have installed reporting Services on a serevr and have a MS SQL DB on a
different server.I am using a domain id to coneect to db server which have
required access on database.Both the server are in same domain.
also I have configured a seperate application pool in IIS for
/reportmanager which is runnig under same domain ID which i used for
reporting service .
I have created a connection which is shared by all reports.
Now problem is while browsing report i am getting error -
"Cannot create a connection to data source 'dbSPSReportingSample'.
(rsErrorOpeningConnection)
SQL Server does not exist or access denied."
I am able to test connectivity to Db server using ODBC connection manager
succesfully.
Error in logfile says --
w3wp!processing!16a4!29/11/2007-14:43:59:: e ERROR: Data source
'dbSPSReportingSample': An error has occurred. Details:
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
Cannot create a connection to data source 'dbSPSReportingSample'. -->
System.Data.SqlClient.SqlException: SQL Server does not exist or access
denied.
Please advice
Thanks
.When you deploy the data source the credentials do not get deployed and you
need to go to Report Manager and view the data source and set the
credentials. Have it be securely stored.
Also, I would strongly consider using a non-domain account if you can. If
you are able to have the Database server run in mixed mode then create an
SQL Server account just used for reporting. Then you can set stored
procedures to allow execute for this account. I put the account in a read
only role on the server. This keeps things clean. Plus, at my company, all
domain user ids are enforced to have the password changed every 90 days
which would be a real pain for this.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Amit" <Amit@.discussions.microsoft.com> wrote in message
news:3E53F4DC-667D-40EA-B3F9-600D69917D1F@.microsoft.com...
> Hello.
> I have installed reporting Services on a serevr and have a MS SQL DB on a
> different server.I am using a domain id to coneect to db server which have
> required access on database.Both the server are in same domain.
> also I have configured a seperate application pool in IIS for
> /reportmanager which is runnig under same domain ID which i used for
> reporting service .
> I have created a connection which is shared by all reports.
> Now problem is while browsing report i am getting error -
> "Cannot create a connection to data source 'dbSPSReportingSample'.
> (rsErrorOpeningConnection)
> SQL Server does not exist or access denied."
> I am able to test connectivity to Db server using ODBC connection manager
> succesfully.
> Error in logfile says --
> w3wp!processing!16a4!29/11/2007-14:43:59:: e ERROR: Data source
> 'dbSPSReportingSample': An error has occurred. Details:
> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
> Cannot create a connection to data source 'dbSPSReportingSample'. -->
> System.Data.SqlClient.SqlException: SQL Server does not exist or access
> denied.
> Please advice
> Thanks
> .|||Hello Bruce,
Thanks for your response.
1.The domain ID which i am using are service account and are set for "
Password never expire option."
2. In the shared connection i am using "credentials stored secuely in report
server"
Still getting the same error. is the error message in sql reporting log file
gives any hint where I am missing ?
" Error in logfile says --
w3wp!processing!16a4!29/11/2007-14:43:59:: e ERROR: Data source
'dbSPSReportingSample': An error has occurred. Details:
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
Cannot create a connection to data source 'dbSPSReportingSample'. -->
System.Data.SqlClient.SqlException: SQL Server does not exist or access
denied.
"
Thanks Again
Amit
"Bruce L-C [MVP]" wrote:
> When you deploy the data source the credentials do not get deployed and you
> need to go to Report Manager and view the data source and set the
> credentials. Have it be securely stored.
> Also, I would strongly consider using a non-domain account if you can. If
> you are able to have the Database server run in mixed mode then create an
> SQL Server account just used for reporting. Then you can set stored
> procedures to allow execute for this account. I put the account in a read
> only role on the server. This keeps things clean. Plus, at my company, all
> domain user ids are enforced to have the password changed every 90 days
> which would be a real pain for this.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Amit" <Amit@.discussions.microsoft.com> wrote in message
> news:3E53F4DC-667D-40EA-B3F9-600D69917D1F@.microsoft.com...
> > Hello.
> > I have installed reporting Services on a serevr and have a MS SQL DB on a
> > different server.I am using a domain id to coneect to db server which have
> > required access on database.Both the server are in same domain.
> >
> > also I have configured a seperate application pool in IIS for
> > /reportmanager which is runnig under same domain ID which i used for
> > reporting service .
> >
> > I have created a connection which is shared by all reports.
> > Now problem is while browsing report i am getting error -
> >
> > "Cannot create a connection to data source 'dbSPSReportingSample'.
> > (rsErrorOpeningConnection)
> > SQL Server does not exist or access denied."
> >
> > I am able to test connectivity to Db server using ODBC connection manager
> > succesfully.
> >
> > Error in logfile says --
> > w3wp!processing!16a4!29/11/2007-14:43:59:: e ERROR: Data source
> > 'dbSPSReportingSample': An error has occurred. Details:
> > Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
> > Cannot create a connection to data source 'dbSPSReportingSample'. -->
> > System.Data.SqlClient.SqlException: SQL Server does not exist or access
> > denied.
> >
> > Please advice
> > Thanks
> > .
>
>|||Hi Amit
Go to the report manager interface and make sure that your data source
has the right credential information, after you deploy. When you say
that RS is on one server and SQL is on another server , do you mean
that there is one web server and one data base server ? In which case
I am guessing that the RS db would be on the second server as well.
This may create a double hop issue in the network where the first hop
will be from the web server to the RS database and the second hop
would be to your stored proc. You will need kerberos enabled on the
network. If you do then check the event viewer on the sql server box
to see whether the right kerberos ticket is getting through from the
web server to the database server .
Cheers
Shai
On Nov 30, 3:13 pm, Amit <A...@.discussions.microsoft.com> wrote:
> Hello Bruce,
> Thanks for your response.
> 1.The domain ID which i am using are service account and are set for "
> Password never expire option."
> 2. In the shared connection i am using "credentials stored secuely in report
> server"
> Still getting the same error. is the error message in sql reporting log file
> gives any hint where I am missing ?
> " Error in logfile says --
> w3wp!processing!16a4!29/11/2007-14:43:59:: e ERROR: Data source
> 'dbSPSReportingSample': An error has occurred. Details:
> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
> Cannot create a connection to data source 'dbSPSReportingSample'. -->
> System.Data.SqlClient.SqlException: SQL Server does not exist or access
> denied.
> "
> Thanks Again
> Amit
> "Bruce L-C [MVP]" wrote:
>
> > When you deploy the data source the credentials do not get deployed and you
> > need to go to Report Manager and view the data source and set the
> > credentials. Have it be securely stored.
> > Also, I would strongly consider using a non-domain account if you can. If
> > you are able to have the Database server run in mixed mode then create an
> > SQL Server account just used for reporting. Then you can set stored
> > procedures to allow execute for this account. I put the account in a read
> > only role on the server. This keeps things clean. Plus, at my company, all
> > domain user ids are enforced to have the password changed every 90 days
> > which would be a real pain for this.
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> > "Amit" <A...@.discussions.microsoft.com> wrote in message
> >news:3E53F4DC-667D-40EA-B3F9-600D69917D1F@.microsoft.com...
> > > Hello.
> > > I have installed reporting Services on a serevr and have a MS SQL DB on a
> > > different server.I am using a domain id to coneect to db server which have
> > > required access on database.Both the server are in same domain.
> > > also I have configured a seperate application pool in IIS for
> > > /reportmanager which is runnig under same domain ID which i used for
> > > reporting service .
> > > I have created a connection which is shared by all reports.
> > > Now problem is while browsing report i am getting error -
> > > "Cannot create a connection to data source 'dbSPSReportingSample'.
> > > (rsErrorOpeningConnection)
> > > SQL Server does not exist or access denied."
> > > I am able to test connectivity to Db server using ODBC connection manager
> > > succesfully.
> > > Error in logfile says --
> > > w3wp!processing!16a4!29/11/2007-14:43:59:: e ERROR: Data source
> > > 'dbSPSReportingSample': An error has occurred. Details:
> > > Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
> > > Cannot create a connection to data source 'dbSPSReportingSample'. -->
> > > System.Data.SqlClient.SqlException: SQL Server does not exist or access
> > > denied.
> > > Please advice
> > > Thanks
> > > .- Hide quoted text -
> - Show quoted text -|||Hello Shai,
Yes I have 2 server
Server A (windows 2003) have IIS6 and SPS 2003 and reporting services
installd.
ServerB have SQL serevr and have DB for reporting services.
Both the server are in same domain.
I am giving right credentials in data source connection.
The reports i am using are SPS reports which i got after installing SPS pack
for SQL reporting server.
Thanks
Amit
"shaikat.das@.gmail.com" wrote:
> Hi Amit
> Go to the report manager interface and make sure that your data source
> has the right credential information, after you deploy. When you say
> that RS is on one server and SQL is on another server , do you mean
> that there is one web server and one data base server ? In which case
> I am guessing that the RS db would be on the second server as well.
> This may create a double hop issue in the network where the first hop
> will be from the web server to the RS database and the second hop
> would be to your stored proc. You will need kerberos enabled on the
> network. If you do then check the event viewer on the sql server box
> to see whether the right kerberos ticket is getting through from the
> web server to the database server .
> Cheers
> Shai
>
> On Nov 30, 3:13 pm, Amit <A...@.discussions.microsoft.com> wrote:
> > Hello Bruce,
> >
> > Thanks for your response.
> > 1.The domain ID which i am using are service account and are set for "
> > Password never expire option."
> >
> > 2. In the shared connection i am using "credentials stored secuely in report
> > server"
> >
> > Still getting the same error. is the error message in sql reporting log file
> > gives any hint where I am missing ?
> > " Error in logfile says --
> > w3wp!processing!16a4!29/11/2007-14:43:59:: e ERROR: Data source
> > 'dbSPSReportingSample': An error has occurred. Details:
> > Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
> > Cannot create a connection to data source 'dbSPSReportingSample'. -->
> > System.Data.SqlClient.SqlException: SQL Server does not exist or access
> > denied.
> > "
> >
> > Thanks Again
> > Amit
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >
> >
> > > When you deploy the data source the credentials do not get deployed and you
> > > need to go to Report Manager and view the data source and set the
> > > credentials. Have it be securely stored.
> >
> > > Also, I would strongly consider using a non-domain account if you can. If
> > > you are able to have the Database server run in mixed mode then create an
> > > SQL Server account just used for reporting. Then you can set stored
> > > procedures to allow execute for this account. I put the account in a read
> > > only role on the server. This keeps things clean. Plus, at my company, all
> > > domain user ids are enforced to have the password changed every 90 days
> > > which would be a real pain for this.
> >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> >
> > > "Amit" <A...@.discussions.microsoft.com> wrote in message
> > >news:3E53F4DC-667D-40EA-B3F9-600D69917D1F@.microsoft.com...
> > > > Hello.
> > > > I have installed reporting Services on a serevr and have a MS SQL DB on a
> > > > different server.I am using a domain id to coneect to db server which have
> > > > required access on database.Both the server are in same domain.
> >
> > > > also I have configured a seperate application pool in IIS for
> > > > /reportmanager which is runnig under same domain ID which i used for
> > > > reporting service .
> >
> > > > I have created a connection which is shared by all reports.
> > > > Now problem is while browsing report i am getting error -
> >
> > > > "Cannot create a connection to data source 'dbSPSReportingSample'.
> > > > (rsErrorOpeningConnection)
> > > > SQL Server does not exist or access denied."
> >
> > > > I am able to test connectivity to Db server using ODBC connection manager
> > > > succesfully.
> >
> > > > Error in logfile says --
> > > > w3wp!processing!16a4!29/11/2007-14:43:59:: e ERROR: Data source
> > > > 'dbSPSReportingSample': An error has occurred. Details:
> > > > Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
> > > > Cannot create a connection to data source 'dbSPSReportingSample'. -->
> > > > System.Data.SqlClient.SqlException: SQL Server does not exist or access
> > > > denied.
> >
> > > > Please advice
> > > > Thanks
> > > > .- Hide quoted text -
> >
> > - Show quoted text -
>|||As I wrote earlier, you will need to enable kerberos on the network .
Cheers
Shai