web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Excel refreshable reports
Microsoft Dynamics GP (Archived)

Excel refreshable reports

(0) ShareShare
ReportReport
Posted on by Community Member

I've downloaded and deployed Excel refreshable reports to GP version 10.0 in a terminal server environment.  When logged in as 'sa' all reports can be refreshed but when logged in as any other user we receive a SQL Server Login window with the Use Trusted Connection checkbox.  The only way that I'm able to proceed is to login as user 'sa' with the Use Trusted Connection checked. When I attempt to login as any other user we receive a Microsoft SQL server Login error: Connection failed: SQLState: '42000' SQL server Error: 18456 Login failed for user 'xxxxxxx'.  I've added the network shared folder to the trusted connections. 

We have other clients on terminal server who are using the Excel refreshable reports without issue. In comparing the environments I did note that the server properties on both the working and non-working environments are set to SQL authentication.  However, the dynamics and company databases in the working environment use windows authentication in the connection properties where as the same databases in the non-working environment use SQL authentication. 

Has anyone experienced this and if so can you direct me to the resolution?

Thanks, Debi

*This post is locked for comments

I have the same question (0)
  • L Vail Profile Picture
    L Vail 65,271 on at

    GP uses SQL authentication and GP encrypts the passwords so that SQL cannot see them. So, if you try to use sql authentication with your GP Password outside of GP I don't think it will work. If you want to use SQL authentication you will need a new SQL login apart from your GP login. Alternatively you could use windows authentication for your Excel reports, but this will not be the same as your GP user. It is possible to imbed the password into the .odc connection, but other security would need to be implemented because the password would be available in clear text. I think you are running into a Network/SQL permissions problem rather than a Great Plains problem.

    Let us know what happens, many of us are probably running into the same issues as the .odc reports become more popular.

    Thank you

    Leslie Vail

  • Lyn Barr Profile Picture
    Lyn Barr 1 on at

    By default, only users who have administrative credentials on the server will have access to the deployed Excel reports.  Since you probably don’t want to give all your users that kind of access, you can use the following as a workaround:

    *Note*  Users would need access to the shared network location where the reports are deployed, but it sounds like you’ve already done that, so I won’t bother with instructions for that.

    Create domain users in SQL Server Management Studio, for all users who should have access to the deployed reports.  (i.e. DOMAIN\UserName).

    Open SQL Server Management Studio, expand Security, and then right-click Logins and choose New Login.

    Create a Windows logon (using Windows Authentication) for the user for whom you want to assign security.

    Assign those users access to the company database(s), and then give those users the database role that would be applicable to the level of security you wish to assign.

    Click User Mapping and assign database access to the necessary databases.  For each database, assign the appropriate database role.  All the roles that govern access to the Excel reports begin with “rpt_"

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
mtabor Profile Picture

mtabor 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans