Linked Servers and Security Concerns

  • Lowell

    SSC Guru

    Points: 323398

    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!

  • graham.day 53003

    SSC Enthusiast

    Points: 179

    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?

  • jeremy.hughes 36229

    SSC Veteran

    Points: 248

    @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 18 (of 18 total)

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