You’re offline. This is a read only version of the page.
Announcements
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
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_"
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
#1
André Arnaud de Cal...
293,311
Super User 2025 Season 1
#2
Martin Dráb
232,183
Most Valuable Professional
#3
nmaenpaa
101,158
Moderator
Share
Report
All responses (
Answers (
