Breaking news from around the world
Get the Bing + MSN extension
Choose your path Increase your proficiency with the Dynamics 365 applications that you already use and learn more about the apps that interest you. Up your game with a learning path tailored to today's Dynamics 365 masterminds and designed to prepare you for industry-recognized Microsoft certifications.
Visit Microsoft Learn
2019 release wave 2 Discover the latest updates and new features to Dynamics 365 planned through March 2020
Release overview guides and videos Release Plan | View virtual launch event
Ace your Dynamics 365 deployment with packaged services delivered by expert consultants. | Explore service offerings
Connect with the ISV success team on the latest roadmap, developer tool for AppSource certification, and ISV community engagements | ISV self-service portal
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Program | Finance TechTalks | Customer Engagement TechTalks | Talent TechTalks | Upcoming TechTalks
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?
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.
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_"
Business Applications communities