Hello All. A report I created in BIDS and deployed to Report Server fails with message - "Canntot create a connection tot he data source'name' when accessed via reports website. The BIDS Preview works without issue. · SSRS2005 · SSRS and Report Server are on ProjServ (Win2003R2) · the data source file is an Access 2007 database on server NAS01 (Win Storage Server 2003) · A machine DS of System type is used · I have tried including and excluding logon account information for the ODBC connection (domain admin account) · I have tried including and excluding credentials for the report data source (domain admin account) · I have also tried, once deployed, changing the credentials to be stored on the report server and tried both with and without Win Integrated Security After reading another post, I decided to check the Report Server log. Below is the text of the log. Is this the Kerberos-double hop issue? I could share a folder on ProjServ and put the Access db there, if that would help. Other reports created in BIDS but using data from Project Server views (on the same box as SSRS) are deployed and work without issue (once the credentials are updated and stored on report server). Any help will be greatly appreciated. Thanks. Jeff w3wp!library!b!02/17/2011-08:05:15:: Call to GetPermissionsAction(/). w3wp!library!b!02/17/2011-08:05:15:: Call to GetPropertiesAction(/, PathBased). w3wp!library!b!02/17/2011-08:05:15:: Call to GetSystemPermissionsAction(). w3wp!library!b!02/17/2011-08:05:15:: Call to ListChildrenAction(/, False). w3wp!library!b!02/17/2011-08:05:15:: Call to GetSystemPropertiesAction(). w3wp!library!b!02/17/2011-08:05:17:: Call to GetPermissionsAction(/Estimated Monthly Plan). w3wp!library!b!02/17/2011-08:05:17:: Call to GetPropertiesAction(/Estimated Monthly Plan, PathBased). w3wp!library!b!02/17/2011-08:05:17:: Call to GetSystemPermissionsAction(). w3wp!library!b!02/17/2011-08:05:17:: Call to ListChildrenAction(/Estimated Monthly Plan, False). w3wp!library!b!02/17/2011-08:05:17:: Call to GetSystemPropertiesAction(). w3wp!library!b!02/17/2011-08:05:18:: Call to GetPermissionsAction(/Estimated Monthly Plan/Estimated Monthly Plan). w3wp!library!b!02/17/2011-08:05:18:: Call to GetSystemPropertiesAction(). w3wp!library!b!02/17/2011-08:05:18:: Call to GetPropertiesAction(/Estimated Monthly Plan/Estimated Monthly Plan, PathBased). w3wp!library!b!02/17/2011-08:05:18:: Call to GetSystemPermissionsAction(). w3wp!library!b!02/17/2011-08:05:18:: Call to GetPropertiesAction(/Estimated Monthly Plan/Estimated Monthly Plan, PathBased). w3wp!library!b!02/17/2011-08:05:18:: Call to ListEventsAction(). w3wp!library!d!02/17/2011-08:05:18:: Call to GetSystemPropertiesAction(). w3wp!library!d!02/17/2011-08:05:19:: i INFO: Call to RenderFirst( '/Estimated Monthly Plan/Estimated Monthly Plan' ) w3wp!processing!d!2/17/2011-08:05:19:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'EstimatedMonthlyPlanDataSource1'., ; Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'EstimatedMonthlyPlanDataSource1'. ---> System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle) at System.Data.Odbc.OdbcConnectionOpen..ctor(OdbcConnection outerConnection, OdbcConnectionString connectionOptions) at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.Odbc.OdbcConnection.Open() at Microsoft.ReportingServices.DataExtensions.OdbcConnectionWrapper.Open() at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.ReportRuntimeDataSourceNode.OpenConnection(DataSource dataSourceObj, ReportProcessingContext pc) --- End of inner exception stack trace --- w3wp!processing!d!2/17/2011-08:05:19:: e ERROR: Data source 'EstimatedMonthlyPlanDataSource1': An error has occurred. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'EstimatedMonthlyPlanDataSource1'. ---> System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified w3wp!processing!d!2/17/2011-08:05:19:: e ERROR: An exception has occurred in data source 'EstimatedMonthlyPlanDataSource1'. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'EstimatedMonthlyPlanDataSource1'. ---> System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified w3wp!processing!d!2/17/2011-08:05:19:: i INFO: Merge abort handler called for ID=-1. Aborting data sources ... w3wp!processing!d!2/17/2011-08:05:19:: 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 'EstimatedMonthlyPlanDataSource1'. ---> System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified --- End of inner exception stack trace --- w3wp!webserver!d!02/17/2011-08:05:19:: 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 'EstimatedMonthlyPlanDataSource1'. ---> System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Hi, Logon the report manager, go to the report and check if its datasource connection type is set to ODBC, then tick "Credentials stored securely in the report server" option and type domain account. thanks, Jerry

Hello Jerry, In Report Manager: Connection type is ODBC. I've pasted the connection string below. I have also tried the report with the 1) security account passed when deploying, 2) changed to the domain account, stored on the report server, with Use Windows credentials unchecked and 2) domain account stored on report server with Windows credentials checked. As an added check, I performed the functions again today - domain account stored on the server. Still cannot create connection to data source. Thanks, Jeff UPDATED - ADDED REFERENCED CONNECTION STRING: Dsn=EstimatedMontlyPlanDSN;dbq=M:\Estimated Monthly Work Plan.accdb;driverid=25;fil=MS Access;maxbuffersize=2048;pagetimeout=5

I ran across this today. I can preview reports but when run from the report server, the service cannot find the datasource (per the error message above). Is that the problem - it is not possible to view a report on report manager with an ODBC-Access data source? ODBC Standard .NET Framework data provider To retrieve data from a data source that supports a standard ODBC .NET Framework data provider, use the ODBC data source type and select the default data provider, or select from the installed data providers in the Connection String dialog box. From: http://msdn.microsoft.com/en-us/library/ms159219(SQL.90).aspx Note: Although a data provider may support previewing a report on your report authoring client, not all ODBC data providers are designed to support reports published on a report server.

Hello all. Let me ask a question another way. Is it possible to use SSRS 2005 to deploy a report to Report Manager whent he data source is an Access 2007 file on a storage server? If so, please provide instructions. Thanks, Jeff Previous posting for this issue: http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/a1d9a04e-80bf-4731-bdeb-8971f2a03b41/#b4efc2dc-df3f-4d74-a074-4d74742c32b8

Hi Jeff This shouldn't be any different to deploying a report that has SQL Server as the source. The only difference is that the service user running SSRS should probably have read access to that Access file. If you are getting an error, please post the error HTHCraig Bryden - Please mark correct answers

Hello Craig, Thank you for responding. I can preview the report in BIDS without error. The following error occurs when opened from Report Server. An error has occurred during report processing. Cannot create a connection to data source 'EstimatedMonthlyPlanDataSource1'. For more information about this error navigate to the report server on the local server machine, or enable remote errors The error portion of the Report Server Log is pasted below. w3wp!processing!1!3/2/2011-09:39:43:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'EstimatedMonthlyPlanDataSource1'., ; Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'EstimatedMonthlyPlanDataSource1'. ---> System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified at Microsoft.ReportingServices.DataExtensions.OdbcConnectionWrapper.Open() at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.ReportRuntimeDataSourceNode.OpenConnection(DataSource dataSourceObj, ReportProcessingContext pc) --- End of inner exception stack trace --- w3wp!processing!1!3/2/2011-09:39:43:: e ERROR: Data source 'EstimatedMonthlyPlanDataSource1': An error has occurred. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'EstimatedMonthlyPlanDataSource1'. ---> System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified w3wp!processing!1!3/2/2011-09:39:43:: e ERROR: An exception has occurred in data source 'EstimatedMonthlyPlanDataSource1'. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'EstimatedMonthlyPlanDataSource1'. ---> System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified w3wp!processing!1!3/2/2011-09:39:43:: i INFO: Merge abort handler called for ID=-1. Aborting data sources ... w3wp!processing!1!3/2/2011-09:39:43:: 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 'EstimatedMonthlyPlanDataSource1'. ---> System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified --- End of inner exception stack trace --- w3wp!webserver!1!03/02/2011-09:39:43:: 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 'EstimatedMonthlyPlanDataSource1'. ---> System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Hi Looking at the error, it appears that the data source has not been deployed properly, or the report cannot see the data source. Edit the report properties using Report Manager and ensure that report has a valid link to it's data source. HTHCraig Bryden - Please mark correct answers

Hello Craig, Below is the connection strings and data sources. Report Manager: Dsn=EstimatedMontlyPlanDSN;dbq=M:\Estimated Monthly Work Plan.accdb;driverid=25;fil=MS Access;maxbuffersize=2048;pagetimeout=5 BIDS Shared Data Source: Dsn=EstimatedMontlyPlanDSN;dbq=M:\Estimated Monthly Work Plan.accdb;driverid=25;fil=MS Access;maxbuffersize=2048;pagetimeout=5 Connection properties for ODCB: Dsn=EstimatedMontlyPlanDSN;dbq=M:\Estimated Monthly Work Plan.accdb;driverid=25;fil=MS Access;maxbuffersize=2048;pagetimeout=5;uid=administrator;persist security info=True EstimatedMonthlyPlanDSN is a System type Machine Data Source on the server with SQL, SSRS and BIDS. The DSN was created through the Connection Properties' Build button (and subsequent windows). Thanks for your assistance. Jeff

JSmith - If you're still dealing with this issue I'm working on a similar situation. I do see one problem in your connection string... you can't use a mapped drive letter: Dsn=EstimatedMontlyPlanDSN;dbq=\\servername\share\Estimated Monthly Work Plan.accdb;driverid=25;fil=MS Access;maxbuffersize=2048;pagetimeout=5

Hi Jeff, The following error you have encoutered: An error has occurred during report processing. Cannot create a connection to data source 'EstimatedMonthlyPlanDataSource1'. For more information about this error navigate to the report server on the local server machine, or enable remote errors this is the issue related with permission to access the access datasource. Actually,my previous reply should be doable in your case. Since you still don't resolve your problem, i did a test in my reporting environment. All productions involved in my test are 32bits. 1. I call Data Sources(ODBC) tool to create a datasource to connect my Access datasource, which is called Accessdemo. 2. In my BIDS, everything is ok. 3. Deploy the report to my local report server, it also works fine. 4. My colleague within the same domain can't access the report and encountered the same error message with yours. 5. Then, i logon my report manager and go to the report datasource Properties page, click the datasource "Credentials stored securely in the report server" and type my domain account so that the report can impersonate this account to access the datasource. 6. Everything is expected. Now, my colleague can access the report without any issue. thanks, Jerry

Jerry and Chad - thank you for the replies. Jerry - I tried your suggestion last week. No one can run the report and all get the same errors when running from report manager. Chad - Your suggestion didn't work either, although I will remember to use \\server\share from now on. I do have a question - where should the ODBC DSN be created? I created in BIDS, hoping that would stay with the RDL. Perhaps not. When I open ODBC Data Source Administrator, the DSN is not there. And, the only drivers available are SQL (unlike creating a DSN in BIDS). Is this the problem? I need a DSN at the system level and not within BIDS? If this is the problem, is there a 32bit ODBC driver for Access 2007 that will run on Server 2003 R2 x64? FYI - Access is not installed on the SSRS/SQL Server box. Thanks, Jeff

OK. I have new information, or at least more compiled info. I was able to get the 32-bit DSN from the server by running odbcad32.exe. First question – can Report Manager use a 32-bit DSN? Following is all the information I can get about DSN and Data Source. From ODBC Data Source Administrator (via odbcad32.exe) · Name= EstimatedMonthlyPlanDSN · Driver= MS Access Driver (*.mdb, *.accdb) · Click Configure button · On the subsequent window (ODBC MS Access Setup) o Database: = \\server\share\dbname.accdb o click Advanced button § On the subsequent window (Set Advanced Options) · Login Name: domain\adminacct · Password: adminacct password · DefaultDir= \\server\share\folder · Driver= blank · ExtendedAnsiSQL= 0 · FIL= MS Access; · ImplicitCommitSync= blank · MaxBufferSize= 2048 · MaxScanRows= 8 · Page Timeout= 5 · ReadOnly= 1 · SafeTransactions= 0 · Threads= 3 · UserCommitSync= Yes From BIDS · Shared Data Source window o Name= EstimatedMonthlyPlanDataSource1 o Type= ODBC o Connection string: Dsn=EstimatedMonthlyPlanDSN;Driver=MS Access;dbq=\\10.19.0.150\general_laboratory_admin\proposal tracking\Estimated Monthly Work Plan.accdb;defaultdir=\\10.19.0.150\general_laboratory_admin\proposal tracking;driverid=25;fil=MS Access;maxbuffersize=2048;pagetimeout=5 o Click Edit button § On the subsequent window (Connection Properties) · Click Advanced button o On the subsequent window (Advanced Properties) § Dbq= \\server\share\folder\file.accdb § Defaultdir= \\server\share\folder § Driverid= 25 § Fil= MS Access § MaxBufferSize= 2048 § PageTimeout= 5 § Trusted_Connection = Yes § DSN= EstimatedMonthlyPlanDSN § Driver= MS Access From Report Server · Enable this data source= yes · Connection Type= ODBC · Connection String: Dsn=EstimatedMonthlyPlanDSN;Driver=MS Access;dbq=\\10.19.0.150\general_laboratory_admin\proposal tracking\Estimated Monthly Work Plan.accdb;defaultdir=\\10.19.0.150\general_laboratory_admin\proposal tracking;driverid=25;fil=MS Access;maxbuffersize=2048;pagetimeout=5 · Credentials Stored o Domain\adminacct o Domain\adminacct password o Use as Windows credentials when connecting to the data source YES Again, the preview report runs in BIDS with no problems. The following error occurs when run in report server: An error has occurred during report processing. Cannot create a connection to data source 'EstimatedMonthlyPlanDataSource1'. For more information about this error navigate to the report server on the local server machine, or enable remote errors The following is the error in the report server log: w3wp!processing!d!3/8/2011-14:43:49:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'EstimatedMonthlyPlanDataSource1'., ; Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'EstimatedMonthlyPlanDataSource1'. ---> System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified at Microsoft.ReportingServices.DataExtensions.OdbcConnectionWrapper.Open() at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.ReportRuntimeDataSourceNode.OpenConnection(DataSource dataSourceObj, ReportProcessingContext pc) --- End of inner exception stack trace --- w3wp!processing!d!3/8/2011-14:43:49:: e ERROR: Data source 'EstimatedMonthlyPlanDataSource1': An error has occurred. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'EstimatedMonthlyPlanDataSource1'. ---> System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified w3wp!processing!d!3/8/2011-14:43:49:: e ERROR: An exception has occurred in data source 'EstimatedMonthlyPlanDataSource1'. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'EstimatedMonthlyPlanDataSource1'. ---> System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified w3wp!processing!d!3/8/2011-14:43:49:: i INFO: Merge abort handler called for ID=-1. Aborting data sources ... w3wp!processing!d!3/8/2011-14:43:49:: 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 'EstimatedMonthlyPlanDataSource1'. ---> System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified --- End of inner exception stack trace --- w3wp!webserver!d!03/08/2011-14:43:49:: 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 'EstimatedMonthlyPlanDataSource1'. ---> System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Jeff - try using SysInternals Process Monitor and filter for Result: "Access Denied". (download here: http://technet.microsoft.com/en-us/sysinternals/bb896645). I found I need to grant permissions to the service account's temp folder.