I work in a corporate environment where the creation of linked servers is absolutely prohibited. I've asked the DBA's the reason and the only answer I ever get is, "That's the policy". So I find myself having to write and make use of SSIS packages to move data between databases when such a need arises.
Can anyone tell me what reasons might have been behind the creation of such a policy? It seems rather unjustified to me.
If one attacker gets its hands on one database, he has to work harder to own the whole system.
The theory goes that if one server is compromised, any linked server is automatically compromised as well.
Personally, I think this just means you should think twice before creating very strong or trusted links. You can choose the account used for each link connection, and limit the access for those accounts to create smaller, well-defined links that only allow access for certain things.
For example, where I'm at we have multiple clients that all have individual database on one server with identical schemas, another server where we do a lot of reporting work, and another server that holds a common db with client details. We have a special links set from the reporting db to the detail db that allows for pulling read-only information in to a report to tailor it to a specific client, but nothing else.
Simple. Someone decided it belongs in the "too-hard" basket. There's also security (access one server via a backdoor in the other) and stability (server A goes down and takes server B with it).
You're probably not justifying your reason for needing a linked server well enough to be worth the hassle. Often it's cheaper just to buy a faster server.
You could always ask to see where the policy is written down. Odds are there isn't really a formal policy at all.
It's an issue with login mappings, usually.
If you map:
"anyone local to remote specific", then anyone can use the linked server
"specific local to remote specific", then the local needs an entry for the current user in sys.server_principals which means you can't restrict to an NT group: you have to list users separately (which is also probably not in the policy)
"use self" (this is a variation on "specific local to remote specific"), then my trusted "domain\bob" login token is used for the remote login. This requires the local server to be configured for delegation and an SPN set up in AD.
Also:
You can use OPENDATASOURCE, but this requires adhoc access to be enabled. Which may be allowed instead of a static linked server.
Used correctly, they're OK, but they have limitations in mapping logins. So, it's easier to say no.
We use them here, FWIW.
Here's an alternative to the more likely explanations in the other answers. The policy might be a lonely survivor of the Service Oriented Architecture school of thought. Which stated that:
Service-orientation aims at a loose coupling of services
Linked servers are low level and strongly coupled, and an architecture astronaut might ban linked servers because of that.
One way to "fix" this policy is to convince your business clients that you have a good, solid solution for their problem; but that it absolutely requires linked servers. The business people will talk to the DBA's, and there's a good chance the DBA will agree to a trial of the linked server. For this to work, talk to the DBA with the strongest opposition to linked servers. DBA's can downtalk developers, but they tend to yield quickly to the Business.
A trial will be the end of the policy. Linked servers work, and SOA does not.
P.S. In the Netherlands we got lucky: SOA means sexually transmitted disease in Dutch, so the SOA silver bullet fantasy kinda flew by :-)