Only sa can log into Management Reporter

This question is answered

I've recently installed MR FP1. When logging into MR and using 'sa' for the database login, all is well and I can create, run and modify reports. However, when using a normal GP login for the SQL login, I get a message : "The user ID and password are not a valid login". Checking the event viewer gives me the error below.

For troubleshooting, I've gone through Jivtesh's MR troubleshooting blog and KB 2406829 (although none of those causes and solutions apply as I am able to run MR perfectly when using the sa user).

Any help will be appreciated!

Thanks,

Ritesh

Timestamp: 2011/10/06 04:25:53 PM

Message: Microsoft.Dynamics.Performance.DataProvider.GeneralLedger.GP.Common.DataAccessException: SQL connection failed. ---> System.Data.SqlClient.SqlException: Login failed for user 'rmaharaj'.

at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)

at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

at System.Data.SqlClient.SqlConnection.Open()

at Microsoft.Dynamics.Performance.DataProvider.GeneralLedger.GP.Data.DataConnector.CreateConnectionUsingGPCredentials(String dataSourceName, String userName, String password)

--- End of inner exception stack trace ---

at Microsoft.Dynamics.Performance.DataProvider.Core.TraceManager.Publish(Exception sourceException, Boolean throwException)

at Microsoft.Dynamics.Performance.DataProvider.GeneralLedger.GP.Data.DataConnector.CreateConnectionUsingGPCredentials(String dataSourceName, String userName, String password)

at Microsoft.Dynamics.Performance.DataProvider.GeneralLedger.GP.Data.DataConnector.OpenConnection()

at Microsoft.Dynamics.Performance.DataProvider.GeneralLedger.GP.GLProvider.HandleConnect(EntitySettingCollection settings, CredentialCollection credentials)

at Microsoft.Dynamics.Performance.DataProvider.Core.SourceSystemConnection.Open()

at Microsoft.Dynamics.Performance.DataProvider.Core.SourceSystem.Connect(EntitySettingCollection settings, CredentialCollection credentials)

at Microsoft.Dynamics.Performance.DataProvider.GeneralLedger.GLSystem.Connect(Guid providerTypeId, EntitySettingCollection settings, CredentialCollection credentials)

at Microsoft.Dynamics.Performance.Reporting.DataProvider.Server.DirectLinkService.GlConnect(Guid providerTypeId, EntitySettingCollection settings, StoredCredential credential)

Component: Microsoft.Dynamics.Performance.Reporting.DataProvider.Server.DirectLinkService

User: SAIL_GROUP\MaharajR

Verified Answer
  • Ritesh,

    It sounds like you are making it past the first authentication where we validate that you are a valid MR user. Let's verify that by clicking cancel at that second login screen and verify you can go to Company | Companies. Then click on settings and go through the wizard.

    For your error, you are probably experiencing an issue because of how GP encrypts its passwords. SA is not encrypted so you are bypassing the issue there.

    Management Reporter in the company screen needs to be configured the same as the ODBC for the server/client where the GP user was created. If on your odbc you have gpserver.domain.com, but in MR you have gpserver all users but sa will fail as the MR link needs to be gpserver.domain.com as well. Basically, you need to make sure the server name is entered identically in both places.

    Once you do this, you should be able to log in with non-sa users.

    Thanks

    Ryan

All Replies
  • Management reporter uses a different database than GP. GP passwords are "encrypted" by GP when they are created. You cannot use GP logins to access anything other than GP.

    Mark

  • Mark's right, you no longer use the GP logins.  You are actually using your AD (Active Directory) logins.  When you look up a user you are returning their active directory user.  

    Sue

  • Ok, then can you tell me what I've done wrong please? When I open MR, it asks for a login and only the "sa" login works. If I enter any GP users login or any users domain login (including mine which is setup as admin on MR), then I get the error as stated above. When I log in successfully with "sa", MR does show my domain login at the bottom of the screen.

    I understand that the MR logins are setup using users domain credentials and I have set them up that way...it's just that when I or a user try to log into MR, we are asked for a login and only the "sa" login seems to work. If I understand your comments above, I should not be getting this login screen in MR as long as the domain user has been setup?

    I've also added my domain user to sql and given it access to all the GP databases (DYNGRP role) and the MR database(general user and public roles)...this did not help.

  • Ritesh,

    It sounds like you are making it past the first authentication where we validate that you are a valid MR user. Let's verify that by clicking cancel at that second login screen and verify you can go to Company | Companies. Then click on settings and go through the wizard.

    For your error, you are probably experiencing an issue because of how GP encrypts its passwords. SA is not encrypted so you are bypassing the issue there.

    Management Reporter in the company screen needs to be configured the same as the ODBC for the server/client where the GP user was created. If on your odbc you have gpserver.domain.com, but in MR you have gpserver all users but sa will fail as the MR link needs to be gpserver.domain.com as well. Basically, you need to make sure the server name is entered identically in both places.

    Once you do this, you should be able to log in with non-sa users.

    Thanks

    Ryan

  • Also, in SQL, check the properties of the domain users you created - in the User Mapping on the ManagementReporter database, give them db_datareader and db_datawriter roles, and see if that works.

    And, this may be a silly question, but when you created the users in MR and assigned their security, you also gave access to the companies, correct?

    Lyn

  • Thanks Lyn and Ryan,

    In this case, Ryan was spot-on - I had used the server IP address when configuring the GP ODBC but used the server name when setting up the company on MR. I just changed the company setup in MR to use the IP and it works 100% with all GP users.

    Quick question though - would it not be easier for the users if MR security was totally based on windows authentication. This would mean they just open the app and start working. Then MR could use a fixed SQL login (like sa) for the database access (in the background).

    Lyn - I assigned the roles you suggested to my domain logins and MR still asks for a database login...so I'm guessing that it's by design at this point.

    Ritesh

  • Once it's configured & working for all users, you can set the system to remember your login(s).  When I use MR, I don't get any login prompts at all, so even though it's using two sets of credentials, from an end-user perspective, it's just a matter of launching the application & getting to work.

  • Lyn is correct that saving passwords will help to make things easier. We don't want to use a set account for all data access, as some people in GP enable account level security so that some users don't get data back for certain departments or accounts.

    Glad to hear things are working

    Ryan

  • I am experiencing the same issue. I do not see a "settings' button when modifying the companies so I am still not sure which ODBC server is being used. I checked the name of the server in the Configuration Console and the GP integration server has the same name as the GP ODBC server that is being used by the GP client. Any other ideas?

  • Thank you for the answer Ryan.  I had a similar situation but it had some different reasons for my errors.

    I had just moved MR from one server to a new server.  When the users tried to login after the move they received the following message:

     "Culture name 'chr-Cher-US' is not supported.  Parameter name: name"

    I also looked in the event viewer and found similar text to what Ritesh posted.  I believe that this came about because MR was still trying to connect to the old database connection.  

    The reason that I am adding this to your post is because I could not find any references to this error message when searching the blogs and forums.  Maybe my addition will bring others to your post.

    We followed your instructions in your post and we were able to connect successfully.

    Thank you,

    Sheila