none
Cannot create a connection to data source !!!! SSRS 2005

    Question

  • I cannot get this error resolve either for myself or any users, and I'm actually part of the Admin group!  Furthermore, a user with whom I'm tring allow to view this report keeps getting prompted for their windows username and password.  I thought that since this datasource was set to Windows Authentification that it would just pass it through?

    I'm pulling my hair out at this point:

    Screen Shots:

    http://www.webfound.net/datasource_connection.jpg

    http://www.webfound.net/datasource_connection2.jpg

    http://www.webfound.net/datasource_connection3.jpg


    Also As far as I know, I've given sufficient permissions to the right logins and right users on my SQL Databases and related stored procs that the datasets (not datasource) run

    • An error has occurred during report processing.
      • Cannot create a connection to data source 'datasourcename'.
        • For more information about this error navigate to the report server on the local server machine, or enable remote errors
    Wednesday, March 8, 2006 9:10 PM

Answers

  • never mind.. ive figured it out :)

    stored the credentials securely within the datasource on the report server itself. works now... though its slower to initialise than when the security is integrated.
    Thursday, March 9, 2006 4:52 AM

All replies

  • Are the client, RS, and SQL server all on different machines?  If so, then you are probably hitting the double-hop problem.  This is a limitation of integrated security in environments which don't support kerberos.

    Tudor talks about it a bit in this blog entry:

    http://blogs.msdn.com/tudortr/archive/2005/11/03/488731.aspx

    You might want to check that out and see if the problem described there is what you are experiencing.

    Wednesday, March 8, 2006 9:47 PM
    Moderator
  • No, I have gotten this to work fine for my test server which is the server running both RS and SQL Server 2005. RS and SQL Server 2005 are on the same box.  The server I'm trying to connect to now is a different server's DB which is where the datasource is pointing to. So there is no double-hop issue here at least!

    So in other words if I change the datasets to use the datasources that piont to ServerA below, my report runs for me ok.  If I change the datasets in my report to talk to ServerB, I get the error immediately.  The datasets are running stored procs and referencing the data sources for the DB connection in the dataset properties.  I am using 3 datasets in my report and around 4 datasources are in my project that are uploaded automatically when I deploy my report to the report serve (TestServerA)

    TestServerA - Has RS and SQL Server 2005.  When using Datasources that point to this server's stored procs, it works for me at least the other users still have issues though where it's prompting them for a windows username and password

    ServerB - a production server running SQL Server 2000 (doesn't have Reporting Services on it, we're just runnign stored procs against it for the datasets) in which the dataset (on TestServerA)  is trying to run a stored proc off of ServerB.  It stil uses ServerA for Reporting services part...whcih is where I'm getting the error from my own client when trying to run the report from the client in Report Manager

     

    Wednesday, March 8, 2006 10:04 PM
  • Ok, now I tried logging on directly to my TestServerA and ran the report from IE on that server (report Manager) and it works fine.  Ok, so how am I getting a double hop issue if I have everything installed on the same server?  I don't think hopping is the issue here still!
    Wednesday, March 8, 2006 10:24 PM
  • scratch my last response...it was working only because my datasets were pointing to the datasources that were pointing to my TestServerA.  Once I changed it back to the point where I got the error (for this post) whcih was to chagne teh datasets to point to the datasources that point to ServerB, I still get the error, even when running the report from TestServerA's Report Manager....
    Wednesday, March 8, 2006 10:42 PM
  • Here's a diagram to show you what's going on.  Keep in mind, if the datasets aren't using datasources that point to ServerB and are using datasources that point to TestServerA, all works fine...it's just when my report is using datasources trying to talk to ServerB.

    http://www.webfound.net/diagram.jpg

    Wednesday, March 8, 2006 11:07 PM
  • Can you check the errors in the report server log files - they are under /Reporting Services/LogFiles/ReportServer*.log

    If you see an error message coming from your data sources server that looks like - login failed for user (null), then it's likely that you are experiencing the Integrated security double-hop issue.

    If that's the case, you can store credentials for the data sources in the report server, or enable Kerberos delegation as shown below:

    http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerbdel.mspx

     

    Thanks,

    Tudor

    Wednesday, March 8, 2006 11:39 PM
  • In your second setup (ServerB) you are describing what I would expect to be the symptoms of the double hop problem.  You have 3 machines, A->B->C.  A is the client machine, B is the machine with reporting services, and C is the machine that has the data you are reporting against.  You are trying to pass credentials from machine A all the way to machine C.  Unless you have set up your environment to allow kerberos delegation (see the links that I pasted above), then this will fail.  If you don't want to (or cannot) enable delegation, then a workaround for you is to store credentials for accessing the datasources in the catalog.

    In your 1st scenario (TestServerA), I am not certain why they are getting prompted for credentials.  I suspect it is because your test users don't have access to your machine, are they able to connect when providing their credentials?

    Thursday, March 9, 2006 1:58 AM
    Moderator
  • my users do have access to the SQL Server databases so I don't know why they would get prompted still.  No, when they type in their username and password, it doesn't take.
    Thursday, March 9, 2006 2:45 AM
  • ,I have missed the same problem once ,the problem is that ur datasource was set ot windows Authentification,(if u pay more attention to ur report'Connection string,u could find the bugs),but ur database has no right for this kind of user,

    u could follow this steps to add a user or a group which the user belong to : 

    1.login in database

    2.extend the security node

    3.extend the logins node

    3.right-click logins node,click new login, and then add the user or the group   which the user belong to to Database'logins.

    And there is a skill,if ur user is a  member of administrator,and u assign permissions to the other user,by default,this kind of user only is a menber of user,dont have the role of administrator,so if u use the windows Authentification to build connection string for ur report,the user who just have a role of user don't have logins to database,u need to follow the steps above to add the user or the group to Database'logins.

    Thursday, March 9, 2006 2:58 AM
  • Samuel, they definitely already have logins to the database, right down to the permissions of the stored procs being called by the datasets so this is covered!  They have read rights as well as datawriter (which isn't really needed).  This is why I posted, I have done almost everything.
    Thursday, March 9, 2006 3:01 AM
  • I believe you are running into a couple of problems which are not necessarily related.

    When you are prompted for credentials in IE with the standard credentials dialog, it is because IIS did not accept the user's token when IE presented it in response to a 401.  This usually means that the user is not a valid user on the target machine.  Check your IIS logs for 401s.

    The datasource problems are because the user we are trying to access the database with is not allowed access.  This can be for any number of reasons, the double-hop issue being a pretty common one when people are trying to pass through Windows credentials. 

    Thursday, March 9, 2006 3:46 AM
    Moderator
  • I have a similar problem to FlavorFlave. Our  setup:

    Machine A : client
    Machine B: SSRS 2005
    Machine C: SSAS 2000

    Running reports from Machine B works, running from from A does not - getting the exact error he specified. Logged on to machine A with a limited user account, onto B/C with full admin rights.

    Enabling delegation is not going to be possible on our network. John, i see you say a workaround is to "store credentials for accessing the datasources in the catalog". Not sure what you meant by this.. or where to do this?
    Thursday, March 9, 2006 3:58 AM
  • never mind.. ive figured it out :)

    stored the credentials securely within the datasource on the report server itself. works now... though its slower to initialise than when the security is integrated.
    Thursday, March 9, 2006 4:52 AM
  • talwar, can you explain the steps you took in detail so hopefully I can do the same...thanks
    Thursday, March 9, 2006 5:28 AM
  • talwar, I think you figured it out for both of us...it's working here as well.  Thanks a bunch!  this was a tough one.  Just stored the credentials using the windows authentification option and the domain account that you are using to run all your sql server services and it seems to work fine then
    Thursday, March 9, 2006 6:35 AM
  • Yeah talwar I have a similar problem so any pointers you could push my way would be useful

    Regards

     

    Tuesday, May 9, 2006 10:09 AM
  • Can you be more specific?  Screen shots?  Just a little more help for the rest of us.
    Friday, May 19, 2006 1:49 PM
  • I resolved my problem.

     

    1) On the SQL Server that is acting as the datasource, needed to add domain level login. 

    2) On that same server, in the database I then added a user, and allowed the user read access to my tables and views.

     

    To debug,  start at the database and work your way back regarding connections.

    Friday, May 19, 2006 5:15 PM
  • I have the same problem,  and  the client, RS, and SQL server all on differente machines?
    and I need to use Windows authentication. How can I do?
    Thank

    Tuesday, December 19, 2006 1:44 PM
  • This looks like an old discussion, but BobChauvin your solution worked for me.  I set up a domain level login for my report server database but forgot to do it on the prod server (I typically run reports from a copy of prod that is sitting on the report server, however for this particular report i needed live data).  Once I created a user in prod with rights the report started working. Thanks.

    Friday, September 10, 2010 6:41 PM
  • Hi - I know this post is ancient, but... can you please tell me what you mean by "stored the credentials securely withing the datasource"?  I have this problem after migrating to new servers but only in production - test is fine.  No idea what the problem is.  I figured I could give your solution a try.  I have very little experience with SSRS - someone else set it up and they no longer work here.  Thanks
    Monday, June 3, 2013 2:01 PM
  • Hi,

    This one has been a real nightmare, but I have resolved this.

    Error messages typically will say:

    1. Cannot create a connection to data source 'XXXXXXXXX'.

    Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=20999; handshake=0; The wait operation timed out.

    HOW TO FIX

    1. Select the Data Source
    2. Select Data Source Properties option.
    3. Select General on the left, and then click the Edit button.
    4. Next click on the Advanced button to open the following.
    5. Expand the Advanced Properties dialog window.
    6. Select MultipleActiveResultSets  and ensure that it is set to True.

    This option enables SSRS to use multiple active results sets when queries are executed, preventing timeouts.

    Steve Davis

    steve@mylife-cycle.com

    Wednesday, January 8, 2014 11:16 AM