User does not have permission to run DBCC TRACEON
I ran into an issue with a Microsoft Query based Excel work sheet a month ago and again just yesterday so I thought I'd share the workaround/fix here for my own sake and anyone else who is butting their head against a wall! The exact error message is as follows:
SQLState: '37000'
SQL Server Error: 2571
[Microsoft][ODBC SQL Server Driver][SQL Server]User 'username' does not have permission to run DBCC "TRACEON".
The issue, as I understand it, is that Microsoft Query uses Microsoft Excel or Microsoft® Excel as the app name in the query string when it tries to connect which causes SQL Server to check for DBCC permissions (permission to run commands in the console). This request to turn TRACEON for a production server is actually frowned upon in favor of a system wide setting at startup so we need a workaround.
In Excel 2010 for PC you can edit the connection string by going to the data tab > connections > edit your connection and then the second tab of that dialog box lets you edit the connection string. Replace APP=Microsoft® Excel; with your own app name e.g. APP=Workaround; and hit OK and close the connection manager. Refresh your data, enter your password and Bob's yer Uncle.