none
Ad hoc updates to system catalogs are not allowed

    Question

  • trying to add a linked server in a sql server 2008. the remote server is also a sql server 2008, on a web server. ive done it successfully before by using "connections will be made using this security context" radio button and providing a SQL server account. but this time i want to use windows authentication. all this pain because windows authentication seems like the best practice according to some msdn article (don't provide credentials in connection string unless you really have to-turn off mixed mode authentication etc etc).

    under security page, if i click the add button, the remote user means a remote *windows* account OR *sql server* account?
    i tried using a windows account as local login and provided credentials for the remote *windows* account and got a "Ad hoc updates to system catalogs are not allowed" error. what is happening here?

    Friday, November 12, 2010 5:22 PM

All replies

  •  

    Starting with SQL Server 2005, the sp_configure ‘allow updates’ parameter configuration item still exists but it is obsolete since direct access to system tables in always prohibited.  While the configuration item is obsolete, having it set to 1 in SQL Server 2005 requires you to run the RECONFIGURE statement using WITH OVERRIDE, otherwise you will get the message above.


    If you ever see the message above when trying to run RECONFIGURE you will either need to run

    RECONFIGURE WITH OVERRIDE,

    or first run:

    EXEC sp_configure ‘allow updates’, 0
    RECONFIGURE


    Sivaprasad S http://sivasql.blogspot.com Please click the Mark as Answer button if a post solves your problem!
    • Proposed as answer by AnsonTIN Friday, January 24, 2014 3:04 AM
    Monday, November 15, 2010 5:43 AM
  • by running this SP, what are the security pitfalls? why is it not enabled by default?
    Monday, November 15, 2010 5:00 PM
  • by running this SP, what are the security pitfalls? why is it not enabled by default?
    When you use that SP you're changing global configurations on the current server, things are not enabled by default to all users.

    You may configure these options, in cases, to special users that could have permissions to perform some things others can't.
    Only system administrators can execute sp_configure to change the values on it, but all users can execute it to see the values, but only administrators to change.

    Please see this article with some information about that: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sprocs/html/sprocs/sprocs58.htm


    Hope this helps.

    Willy Taveras.-

    http://itlearn.net

    Monday, November 15, 2010 5:05 PM
  • well here's the thing. i have a sql server in DMZ that supports a web server. these servers are public. i want to update the public sql server every night with an internal sql server. the catch is i dont want to turn on mixed mode authentication on the public sql server. now when i try to create a linked server on the internal sql server to the public sql server, using impersonation, i get the error in my original post. do you think "allow updates" is the best approach?
    Monday, November 15, 2010 9:38 PM
  • Willy, this may solve the problem, but i want to know if this is the *best* approach, and if there are any other alternatives. i think this was not enabled by default for some good reason.
    Monday, November 15, 2010 10:00 PM
  • what is more dangerous, allowing mixed mode authentication (alongwith disabling/limiting all sql server accounts), OR, disabling mixed mode authentication but allowing updates to system catalog by admins?
    Monday, November 15, 2010 10:42 PM
  • Ha. it turns out, linked server cannot be created for a server in different domain/workgroup without turning on mixed mode authentication on the remote server! if you want to use windows authentication, both servers need to be part of the same domain/workgroup. at least thats my take from this article. :

    http://msdn.microsoft.com/en-us/library/ms188477.aspx

    Tuesday, November 16, 2010 6:20 PM
  • That is the easiest way to get it to work, since it requires the fewest people to be involved in making it work.

    However, if both domains are set up to trust each other, then the trust delegation should make it from Domain1 to Domain2.

    RLF

    Tuesday, November 16, 2010 6:49 PM
  • RLF, trust is not an option for us here. the remote server is in a workgroup in a DMZ. and the local server is on internal n/w in a domain.

    seems like my only option is mixed mode authentication. even though i use it on other servers, i wanted to use windows auth only on the new one i was building. oh well.

     

    Tuesday, November 16, 2010 7:58 PM