March 9, 2009 at 4:20 am
Hello,
Pretty new to RS and having difficulty connecting to a remote SQL Server db.
Developed a shared datasource in BIDS using SQL Server authentication, specifying a username and password (which is also saved). Connection works fine. Written a couple of test reports using said datasource and these work fine.
The trouble occurs when the solution is deployed and i'm getting the message:
An error has occurred during report processing.
Cannot create a connection to data source 'OneBestWay'.
For more information about this error navigate to the report server on the local server machine, or enable remote errors
Looking in the log files:
aspnet_wp!library!17!03/06/2009-14:50:17:: i INFO: Call to RenderFirst( '/OneBestWay test/Report1' )
aspnet_wp!processing!17!3/6/2009-14:50:33:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'OneBestWay'., ;
Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'OneBestWay'. ---> System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.ReportingServices.DataExtensions.SqlConnectionWrapper.Open()
at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.ReportRuntimeDataSourceNode.OpenConnection(DataSource dataSourceObj, ReportProcessingContext pc)
--- End of inner exception stack trace ---
aspnet_wp!processing!17!3/6/2009-14:50:33:: e ERROR: Data source 'OneBestWay': An error has occurred. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'OneBestWay'. ---> System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
aspnet_wp!processing!17!3/6/2009-14:50:33:: e ERROR: An exception has occurred in data source 'OneBestWay'. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'OneBestWay'. ---> System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
aspnet_wp!processing!17!3/6/2009-14:50:33:: i INFO: Merge abort handler called for ID=-1. Aborting data sources ...
aspnet_wp!processing!17!3/6/2009-14:50:33:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing., ;
Info: Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'OneBestWay'. ---> System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
--- End of inner exception stack trace ---
aspnet_wp!webserver!17!03/06/2009-14:50:33:: e ERROR: Reporting Services error Microsoft.ReportingServices.Diagnostics.Utilities.RSException: An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'OneBestWay'. ---> System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
What confuses me is that I have no problem getting a connection to the datasource in BIDS and nor via previously built Crystal Reports using the same credentials (therefore I'm still connecting remotely).
Can anyone shed soume light on this?
Thanks,
Dom.
March 9, 2009 at 7:31 am
This is generally caused by SSRS dropping your connection credential so check the connection property in BIDs you will see it empty add the permissions back. You also need to deploy by copying both the RDL and solution files not just RDL.
Kind regards,
Gift Peddie
March 9, 2009 at 7:47 am
thanks for the reply.....where should I look to check the connection string?
I've set up a shared datasource, and looking at the connection string there it appears to be ok.
(i've also deployed the whole solution)
thanks
March 9, 2009 at 7:58 am
I am not sure you could check in the Reports Deployment folder but I know if you click on datasource in BIDs you will see the connection information is empty. I used to add that information many times in one day also using Share data source.
Kind regards,
Gift Peddie
March 9, 2009 at 8:11 am
...it all appears to be how I initially set it up....nothing appears to be missing and clicking 'Test Connection' works.
any other ideas?
thanks
March 9, 2009 at 8:44 am
Then you need to make sure remote connection is enabled in SAC with both local and remote enabled, Named Pipes and TCP/IP and the browser service is running. If that did not fix it then it may be related to firewall for that check below for a Microsoft solution.
The key point is make sure SSRS application pool is run with domain admin account with Window authentication in IIS.
http://blogs.msdn.com/rdoherty/archive/2006/07/20/672883.aspx
Kind regards,
Gift Peddie
March 9, 2009 at 9:46 am
Many thanks for your help so far......I think we've narrowed it down to be firewall related...so will be in touch with our security guys.
cheers,
dom
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply