Post reply

Cannot create a connection to data source

  • April 4, 2007 at 1:19 pm

    #153507

    Hi everyone.

    I am getting the message "Cannot create a connection to data source.." when users try to run reports. Information gathered so far:

    - the users access reports through Report Manager

    - it is a Sql Server 2005 database

    - the database and the report server are in the same Windows Server 2003 box; the client (IE) is in a XP desktop

    - the shared data source for all these reports is configured to make use of Windows Integrated Security

    - users can browse OK the folders and files in Report Manager; they get the error message when they try to run any of the reports

    - myself, I am able to run any report; the difference is that I belong to the Domain Admins group

    - we have verified that if we add the users to Doman Admins group the problem goes away

    - I have also verified that if I create users in the database the problem also goes away

    I am looking for a solution that doesn't require assignment of users to the Domain Admins group and that doesn't require the creation of users at database level.

    Any clue anyone?

    I appreciate any insight about it.

    Thanks in advance.

    Roque Daudt

    Database Analyst

  • April 4, 2007 at 5:21 pm

    #698681

    Roque,

    The solution I have applied in the past is to use stored credentials in your shared data source utilizing a profile that has read only permissions to the database. Typically something like RSExec with a password on the backend that does not expire.

    This does not get around your request to not create a user at the database level. But this has proven in the past to be an easy solution.

    This solution is, of course, done based on your other security being set up in such a way that the user in authenticated when entering the Report Manager.

    Hope this is helpful...

  • April 5, 2007 at 5:27 pm

    #698950

    Thanks for the information.

    By advice of some DBAs I have created a login in the database for an existing group in the corresponding domain. This group contains the users that are going to access the reports.

    It seems to work fine but I need to spend more time to understand all the implications. From the top of my mind your approach seems to be superior because - once users are not aware of the stored credentials - they wouldn't be able to login to the database directly.

    Do you see other pros and cons here?

    Roque Daudt

  • April 5, 2007 at 5:57 pm

    #698952

    Hi Roque,

    Looks like you have worked out a sound approach. If the users have read only permissions to the data this is certainly a pro and not being challenged for user/password information each time you run a report is a pro.

    I've tested configurations using windows credentials when connecting to the data source and gotten mixed results at best. More often than not I found users able to log into the report manager and/or sharepoint webpart, but unable to run the report after typing in the same credentials.

    Thus far the stored credentials with read-only permissions has been the most straight forward and reliable method I've used.

    hb

  • April 5, 2007 at 6:18 pm

    #698956

    This is all good information, and you've touched on something that is still not OK in my setup: after creating the login and mapping it to the right database as db_datareader I was not able to make the reports run right away because the stored functions (that are called by some of my queries) wouldn't run.

    It worked when I added the db_owner (or something like that, I am not in the office now) to the mapping. Any idea on an alternative way to make the stored functions run?

    RD

  • April 9, 2007 at 9:33 am

    #699310

    I haven't used stored procedures with RS as yet. That was on my list of things to do. I have one particularily processor intensive report that I was hoping to have run fast my utilizing an sp. I will work on that today and get back to you on my results.

    HB

  • April 9, 2007 at 1:35 pm

    #699350

    I gave it a bit more of reading and tried a number of alternatives.

    It seems that stored credentials are a must if one wants to deploy scheduled reports (what is going to happen here at some point in time). Therefore, I am now going for the stored credentials.

    I still can't find a way to get rid of the db_owner privilege. Without it my reports fail to invoke the stored functions. In order to minimize it I am also using the db_denydatawriting role.

    So far, it all seems to work fine.

    RD

  • April 9, 2007 at 1:45 pm

    #699357

    Have you tried granting EXEC privileges on the appropriate stored procs to the stored credentials user?

  • April 9, 2007 at 2:04 pm

    #699364

    No, I hadn't. Just did it now as per your note and it works beautifully withouth the db_owner role!

    Thanks a bunch.

    RD

  • April 9, 2007 at 2:17 pm

    #699371

    No problem at all. We actually emulate a role based security model that way (it's truly role based, but the roles are handled by an internal app, and are not actual NT or SQL roles). That way, we just make the GRANT EXEC part of the report stored procedure creation scripts, and things work right out of the gate.

  • April 13, 2007 at 6:13 pm

    #700537

    Hi Roque and David,

    I had re-visited the post in awhile but ended up with the same results you tried Roque and that David suggested.

    Thanks!

  • August 9, 2007 at 2:25 pm

    #725854

    God I love this website!!!!

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply