I recently spent some time migrating data to a new Microsoft SQL (MSSQL) Server, from SQL 2000 to SQL 2008. One thing that we missed, and that broke after migration, was Excel documents containing Pivot tables. Users were reporting they’d stopped working, now displaying the following error:
"[Microsoft][SQL Native Client][SQL Server]User 'DOMAIN\username' does not have permission to run DBCC TRACEON."
TRACEON is intended to enable certain trace flags and requires membership of the sysadmin role. Not something I wanted to give out. I spent a few hours investigating but none of the Excel functionality was executing DBCC TRACEON. So what was going on?
It appears as if SQL 2005 and later checks if the client is sending an application identifier of “Microosft® Query” and if it is, tries to turn quoted identifiers on (SET QUOTED IDENTIFIER ON). This can be mitigated by adjusting the connection string in Excel documents and changing “APP=Microsoft® Query”.
You can change “APP=Microsoft® Query” either inside Excel, or by editing the file in Notepad and replacing the word “Query” with something else of the same length. SQL then doesn’t try and enable quoted identifiers and all is fine once more.