Skip to main content
W.C.Digiacomo
W.C.Digiacomo
Participant
July 5, 2023
Question

Automated Login to SSRS from ColdFusion

  • July 5, 2023
  • 1 reply
  • 468 views

We ahve a COldFusion (CF) application with a MS SQL Server Databases, and for formal reports we are using SSRS.  We we want to maintain a single user login to SSRS (for administrative/maintenence ressons the server admins do NOT want to create Acrive DIrectlty accounts for all website users). 

The problem is, users are advertently locking this one account due to their repeated misentering of the one account's credentials, which ic requiring us to repetedly call the server administrator to unlock that one account.

The solution we are looking for is for ColfFusion to log into SSRS with that one account whenever a user logs into the website so when they open an SSRS report they are already loggid into SSRS and essentially circumvent the SSRS Login dialog.

Any assistance with developing/implimenting this 'Behind the Scened' process would be greatly appreciated.

    This topic has been closed for replies.

    1 reply

    Charlie Arehart
    Community Expert
    Charlie ArehartCommunity Expert
    Community Expert
    July 5, 2023

    Before elaborating, can you clarify your role in all this? Are you responsible for the cfml code, and familiar with it? Or might you be an admin or indeed a DBA (or manager) who has no awareness of and has not seen the cfml code in question?

     

    I ask because I am not quite sure what you mean about having your cf app "logging into ssrs". Could you have a cfquery/cfstoredproc that calls upon a system stored proc to interact with ssrs? I've heard of that being done, but I don't find any resource discussing it. Is that what you're doing? 

     

    To be clear, with cfml the typical behavior is that any such query of a db would use a "datasource" as defined in the cf admin, and it would then presume to use the authentication set for that dsn in the sdmin.

     

    Or one can configure a cfquery or a cfstoredproc to instead specify its own username and password, matching some sql server login that was authorized to perform the intended query.

     

    If the latter may be what interests you, you'd then need to provide for some way to store or prompt each user for their authentication credentials. I'm not aware that CF can use the user's Windows credentials--even if you had configured those users to have sql server access.

     

    Since it's so common for all users to perform their work (from cf) under a single account (for better or worse), I've not heard call for passing/using a user's windows Auth credentials to be how a query is performed. I could certainly see the value for that, but I don't readily find any discussion of it. 

     

    Someone else may offer a different perspective, and I could well be lacking in some understanding on some point(s) above, in which case I welcome being corrected.

     

    But now I hope you can see why asked what your cf experience is, as we could go into more depth of needed, or we might quickly lose you if you don't actually work with it. There's no offense intended at all, in asking. 🙂 

    /Charlie (troubleshooter, carehart. org)
    W.C.Digiacomo
    W.C.DigiacomoAuthor
    Participant
    July 5, 2023

    I am the lead ColdFusion (CF), Microsoft SQL, SQL Server Reporting Services (SSRS) developer on this project and we maintain Enterprise level licensing for these products.

     

    As with other secure websites one must LOG into them by entering a known username and password.  SSRS is a SQL Server addon that, as its name implies, provides reporting services (i.e. it runs various views, functions, and stored procedures to populate report templates with the retrieved data.

     

    I am fully aware of how CF allows for running queries against tables & views, stored procedures, etc. … but ALL of this is being done by SSRS, and CF is merely calling SSRS to run a report from a predefined report template and returning a completed/flattened report (PDF or HTMS) to a new user’s browser window.

     

    This has nothing to so with the users Active Directory (AD) or Windows Credentials (WC), it has everything to so with CF calling another website and logging into it (with a provided username & password) behind the scenes for the user.  In other words … CF Website A requires a user to log ingo it, after a user logs into website A we want that CF website to automatically log the user into website B (SSRS) behind the scenes (with a stored username & password) and maintain that connection in session.  Then when the user runs a SSRS report from website A (they call the report on the SSRS website) they are already logged into SSRS and the report simply runs.  Here is an example of calling the report…

    ssrsserver.com/ReportServer/Pages/ReportViewer.aspx?/website root/subfolder/namedreport &rs:Command=Render&ID=7588

    This URL is opened in a new window and currently requires the user to LOG INTO SSRS.

     

    Again, this has nothing to do with accessing ones WC or AD account, it has everything to do with CF calling another website (SSRS) and logging into it with known/provided/hard-coded credentials.

     

    We know this is possible, as we have .Net web applications that do this, as well as access & manage AD accounts therefrom.  We are looking for a way to have our CF Server and/or CF Code to do this for us.  And as CF is a completely different animal, nothing we have created in .Net appears to be applicable.

     

    NO offense perceived or taken!

    D
    Community Expert
    Dave WattsCommunity Expert
    Community Expert
    July 5, 2023

    SSRS, like some other Windows services/applications from Microsoft, has several possible authentication methods. You can use the one you prefer. Each has advantages and disadvantages. But you reconfigure SSRS to use the one you want, then build an analog to anonymous access to SSRS from CF using the CFHTTP tag and rewrite parts of the response then embed the response in your page. Here are some things that, although mostly .NET-based, might help you out.

     

    https://www.madeiradata.com/post/overriding-the-authentication-in-ssrs-for-external-access

     

    https://dba.stackexchange.com/questions/303900/anonymous-access-to-ssrs-report (this one's kind of ugly, you may have to test alternatives in here)

     

    Dave Watts, Eidolon LLC

     

    Dave Watts, Eidolon LLC
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices