Today came across an error which I think is worth blogging. A new user can access CRM via IE and Outlook, when exporting data to an Excel dynamic Worksheet, receives the following error:
SQL Server Error 18456
(Microsoftj(ODBC SQL Server Driverj[SQI. ServeriLogin failed for user DOMAIN\Username’.
The error above also does not specify why the login failed. So to understand exactly what's hapenning, we can give it a try and use the Excel built-in Data Source connection and retrieve a more friendly error message:
Select From SQL Server, below type the SQL server name and click next, this should give you something like Access Denied.
For some reason, the user was not a member of the CRM ReportingGroup (which provides the SQL access) causing the Access Denied error.
Adding user to the CRM ReportingGroup fixed the issue, providing read access and allow Excel to fetch CRM data.