Linked Servers and Security Concerns

Question

Linked Servers and Security Concerns

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

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 Old Hand Points: 339 More actions · Answer 2

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: 259 More actions · Answer 3

@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.