Linked Servers and Security Concerns

  • sandeep rawat (8/14/2012)


    I do agree on the point in case of if linked server is also SQL server .

    But in that case u should have same set of user name and password if making linked server with another database (like oracle ,db2 or...) i feel that is not a feasible ...

    you can make security as tight as you need it; it just requires planning;

    you want to avoid everyone using the same Oracle user , so you have to create multiple users on oracle, so you can map them on your linked server. typically, i'd create 3 or 4 users with the right permissions to use accross my linked servers.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • An often overlooked security aspect of using a linked server relates to access to statistics for the purpose of generating an accurate query plan.

    On a local server, any process with access to a database already has this right. But a query against a remote server requires elevated rights to do this, such as dbo_owner on the database or sysadmin on the server.

    So here is a quandary - do you allow poorly performing queries where the query optimizer has no access to table statistics, or do you grant elevated rights to the user and give them full control of a database or server?

  • @graham - Since 2012 SP1, this is no longer the case. You now only need read access to the tables/columns referenced in the query, join conditions, and predicates. See the section titled "Permissions for SQL Server and SQL Database" from https://technet.microsoft.com/en-us/library/ms174384.aspx.

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply