locked
SCCM 2012 - SSRS 2008 R2 - Login Failed for user

    질문

  • Okay, so we have been pulling our hair out for the last week or so trying to get this all sorted out.

    Basically this is our setup:

    1. We have a 2012 SCCM site system on a server called SMS2012

    2. We have a SQL Server 2008 R2 SP2 v 10.50.4270 instance on the SAME SERVER called MSSQLSERVER

    3. The MSSQLSERVER instance has Reporting Services built into it

    4. We have a SQL Server 2008 SP3 v 10.0.5829 instance on the SAME SERVER called SMS2012

    5. We have a local user account on the same server called 'SMS2012\SSRS'

    6. Our database name on the 2008 SMS2012 instance is 'CM_PEI'

    Note: I did not have a choice in the naming of the instances or the choice in using a single instance...most of this work was done before I was able to start working on the server.  If I had the time, I would rip this thing to the ground and start anew, but I don't.

    Now, here is the problem:

    1. We ARE able to access both the Report Manager site and the Report Server site.

    2. We are NOT able to run reports from IE on the Manager site, instead we receive the following error:

    • An error has occurred during report processing. (rsProcessingAborted)
      • Cannot create a connection to data source 'AutoGen__5C6358F2_4BB6_4a1b_A16E_8D96795D8602_'. (rsErrorOpeningConnection)
        • Cannot open database "CM_PEI" requested by the login. The login failed. Login failed for user 'SMS2012\SSRS'.
    • An error has occurred during report processing. (rsProcessingAborted)
      • Cannot create a connection to data source 'AutoGen__5C6358F2_4BB6_4a1b_A16E_8D96795D8602_'. (rsErrorOpeningConnection)
        • Cannot open database "CM_PEI" requested by the login. The login failed. Login failed for user 'SMS2012\SSRS'.

    An error has occurred during report processing. (rsProcessingAborted)
    Cannot create a connection to data source 'AutoGen__5C6358F2_4BB6_4a1b_A16E_8D96795D8602_'. (rsErrorOpeningConnection)
    Cannot open database "CM_PEI" requested by the login. The login failed. Login failed for user 'SMS2012\SSRS'. 

    3. The user DOES have login abilities to the SMS2012 instance.  For testing, we have granted it dbowner, dbdatareader, and dbdatawriter to both the schemas owned by it and the role memberships it has.  We have also given it the SCCM_Rpt schema as it appeared to be related, but we could be wrong.

    4. I have verified this by using SQL Server Management Studio to connect to the SMS2012 instance and query a table from the CM_PEI database with no issues.

    Now, we see the same error in the SCCM srsrp log, however for posterity I should note that that log is reflecting the user as 'NT AUTHORITY\SYSTEM'.

    I have found a resolution to this to access the reports manually, but it does not place nice with SCCM.  If you navigate to the automatically generated data source on the Report Manager website and look at the connection string it is as follows:

    Persist Security Info=False;Initial Catalog=CM_PEI;Data Source=sms2012.petzent.com\SMS2012;Encrypt=true;TrustServerCertificate=false

    As the above connection string stands, I get the following error when I click 'Test Connection'

    Cannot open database "CM_PEI" requested by the login. The login failed. Login failed for user 'SMS2012\SSRS'

    If I CHANGE the data source server from a FQDN to just the server name:

    Persist Security Info=False;Initial Catalog=CM_PEI;Data Source=sms2012\SMS2012;Encrypt=true;TrustServerCertificate=false

    I get the following (good) message:

    Connection created successfully.

    We have explored a hundred and one different solutions, with no avail for any of them.  We thought it might be the double-hop issue through kerberos so we enabled the kerberos logging on the server, but we have not gotten any kerberos related error events when the health check is performed by SCCM's reporting services point.  I also don't believe this is the issue because we are NOT accessing the database from a remote server, it is local to the same server.

    HELP, please, we have spent a good 72 + hours trying to fiddle with this to get it working correctly and have not made ANY progress.  I will be monitoring this thread constantly so feel free to ask any questions that may help you as we are all out of ideas.

    • An error has occurred during report processing. (rsProcessingAborted)
      • Cannot create a connection to data source 'AutoGen__5C6358F2_4BB6_4a1b_A16E_8D96795D8602_'. (rsErrorOpeningConnection)
        • Cannot open database "CM_PEI" requested by the login. The login failed. Login failed for user 'SMS2012\SSRS'.

    • 편집됨 Robert Petz 2013년 1월 31일 목요일 오후 11:45
    2013년 1월 31일 목요일 오후 11:45

답변

모든 응답

  • Bah, just realized that I left out an important note about the connection strings:

    While I am able to change the connection string and see if connect after that, it's not a solution because everytime SCCM's reporting services point does a health check (on a one minute interval) it sees that the connection string has been modified and changes it back to what it wants it to be prior to trying to connect for the health check...

    2013년 2월 1일 금요일 오전 12:38
  • Hi,

    This forum is for System Center Essentials(SCE) related issues. For questions about System Center Configuration Manager(SCCM), please use the following Forums, which would be the best resource in your scenario.

    System Center 2012 Configuration Manager
    http://social.technet.microsoft.com/Forums/en-us/category/systemcenter2012configurationmanager

    Thanks,


    Yog Li
    TechNet Community Support

    • 답변으로 표시됨 Yog LiModerator 2013년 2월 13일 수요일 오전 9:01
    2013년 2월 7일 목요일 오전 9:40