MS Query with Excel 2003 no longer working with MS SQL 2008 error 2571
Posted on 2010-09-02
I need the existing System data source (QL Live) to work again.
Having upgraded to SQL 2008 from SQL2000 all my MS Query's (used in Excel 2003) fail due to the increased security introduced in SQL 2005. This is the Microsft SQL Server Login error:
SQL Server Error: 2571
[Microsoft][ODBC SQL Server Driver][SQL Server]User 'query' does not have permission to run DBCC TRACEON
MS Query uses a system DSN called QL Live (part of the build for all machines on our network)
The solution to this error (posted elsewhere on this site) is to replace the 'Application Name:' from the default "Microsoft(r) Query", to something else, such as "Microsoft Query 2003".
However if I replace this manually, it doesn't always work (still fails) and when I open the options on the query, it hasn't changed the application name. I find using lowercase and no spaces seems less likely to fail. On other occasions, when it does change, it doesn't save with the spreadsheet, so the next time I open the spreadsheet with the embeded query, it fails again (because the Application Name is still wrong).
I have created a new system data source, and this strangely defaults to an Application Name of 'Microsoft Query 2003' (and works).
I've searched the registry in vain for 'Application Name' and it doesn't appear to stored in an ini file either. What's more, I cannot find the Application names of "Microsoft(r) Query" or "Microsoft Query 2003" anywhere in the registry either.
What I have found is the 'QL Live' entry in the registry under "My Computer\HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\"
I have manually added a value in here of "Application Name" with a value of ms2003
I then rebooted my machine, but alas, it still didn't appear in the SQL Server Login options on MS Query.
I would ideally like roll out a fix via group policy.