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?
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
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
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.
- Proposed as answer by Raul Garcia - MSMicrosoft employee, Moderator Monday, November 15, 2010 8:06 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?
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. :
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.