SQL Server Performance Forum – Threads Archive

Do & donts to allow Linked Server Query

Hi all, What are the Do’s and Dont’s in allowing linked server access to developers. Is it a good practice to allow this? I havent opened up linked server connectivity to developers so far. One of the application teams are pushing for a linked server access to oracle tables. Appreciate your input. Thanks much.
Priya

Hi ya, We do have some linked server access, although only to other sql2k servers. I don’t personally have any issue with it as long as it doesn’t compromise security or performance… Having said that, I don’t tend to allow linked server access to production servers… Cheers
Twan

Linked Servers can be used to allow users to connect to the underlying tables rather than allowing them to connect via Enterprise Manager which leads to browse other database objects (ofcourse it can be controlled with security/privileges). If the user privileges in Oracle can be controlled then I don’t see any issue in allowing Linked Server connection. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Satya, Twan Thanks for your help. How do I control access via enterprise manager and to allow connections only to the underlying tables. The security on the Oracle environment is pretty tight, but this is a production SQL Server environment with other applications running on it.
Thanks,
Priya

Hi Priya, on the linked server you say which userid/password to use to access the Oracle server. This user in Oracle can be restricted to only have certain rights, there is no way to prevent others on the SQLServer from also using the linked server… Cheers
Twan

That is the disadvantage with SQL Enterprise Manager and if you allocated correct rights to the user then they can only access them, even though they can see other objects in the database. So in this case only Linked Server is the option by giving them access to underlying tables only. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>