May 29, 2008 at 6:18 pm
The company I work at has a general policy not to link servers at all, and the DBAs won't really discuss it either. I was told that it represented a security risk. I tried to do a little research on this topic, but I didn't find any conclusive answers on what those actual risks might be. I was told, however, that if I could put together a compelling business case for the issue, I might be able to get permission to link the servers in question. I won't burden you with the issues I'm with which I'm dealing, as they do little else than to point to linked servers as an ideal path to resolution.
What are the possible security concerns that wouldn't already exist with any normal SQL server login?
The servers are all on our WAN, and there is one primary database that syncs changes out to remote databases that are slices of the primary database (less data, but the table structures are the same) If we set up a readonly login with access to only the database in question for the link mapping, would there be any security concerns at all? That's the approach we are going to take.
If anyone knows of a good white paper or document outlining the security considerations of linked servers between SQL Server 2005 databases, please point me to it, and that will definitely do the trick.
Thanks!
May 31, 2008 at 2:55 pm
What is typically frowned upon are ad hoc distributed queries.
Ad Hoc Distributed Query Option
The reason this type of access is typically bad is it could potentially allow a user to make a remote connection in the context of the SQL Server service account. Obviously this would mean a user might be able to make a connection which has greater privileges than the user actually has. The solution to this problem is... you guessed it... linked servers because you can control the logins and how they map.
With respect to linked servers there are two main arguments. If you have Windows authentication coming in and Windows authentication coming out in context of the user, then you've got to set up both SQL Servers for Kerberos authentication (not a big deal) but you've also got to set up the first SQL Server's service account to be able to do Kerberos delegation (a big deal because it is now impersonating users). If you don't go that route, you've got one of the connections as a SQL Server login, with the usual arguments against that. And that actually raises the second argument, in that if it's a SQL Server-based login traversing the linked server connection, that means that the username/password combination is being stored on the first server, albeit encrypted. Thus far I don't know of any literature which shows that the password encryption has proven any weaker than the standard password encryption, so I don't put much stock in that.
Another, weaker argument is that since you've got a linked server connection defined, that there is something which folks can see that would indicate a link between the two SQL Servers. However, if you're doing replication, the same type of evidence is there.
Probably the best way to handle this is to understand what the objections are. There's got to be a reason they are "No linked servers allowed," and it usually involves someone hearing/reading something or someone in the past not setting things up in a secure way. Ultimately it's a business decision (security always is as it is a trade-off with functionality), so try and approach it from that direction. There's a cost to doing it via alternative methods. When the cost vs. risk is considered, they may back off, especially if you've been able to show that you mitigate any significant risk that they may express as to why they block linked servers in the first place.
K. Brian Kelley
@kbriankelley
May 31, 2008 at 3:45 pm
Thanks, Brian! I appreciate you taking the time to respond to this question. This will help a lot.
May 31, 2008 at 3:52 pm
If there are any specific objections they raise you need some help with that you don't feel comfortable posting in the forum (since this is security-related), feel free to PM me on this site. It may take me a day or two to respond depending on work and ministry commitments, but I'll try to help where I can.
K. Brian Kelley
@kbriankelley
January 19, 2012 at 7:20 pm
Interesting post and response as well. I just recently had to fight tooth and nail to get a linked server set up for my development work as well. In my case I work on a big process that spans several servers and I often have to do querying across servers and sometimes pull data from one to the other. With deadlines looming, it was faster to be able to use a linked server than have two connections open to compare tables, or write SSIS packages. I was similarly quizzical of the DBAs reluctance to set it up, but they said many of the same things you did, Brian.
I guess the concern around security as opposed to performance still throws me for a loop a bit. We have linked servers traversing A -> B with access to most (if not all tables) and a few traversing B -> A for selected tables. Do you think the reluctance lies in them not wanting to make more tables available than they have to should someone gain access to one server as you outlined in your previous comment? Other than that, I'm having a hard time seeing how having it set up could cause a breach of security (any more so than might already be present by the other linked servers).
January 20, 2012 at 7:59 am
Thanks for a nice response Brian. I have a similar situation like Tetroxide where data from multiple data sources need to be harvested for reporting purposes. I am looking at linked servers and other alternatives. The question is is where can I find data in one place that discusses the different choices or alternatives, the pros and cons, the assoicated risks and costs, security considerations and how to mitigate them, so we can make an effective decision on which technology to impliment.
January 20, 2012 at 12:09 pm
We can restrict linked server access to only specific accounts by using the "Local server login to remote server login mappings" option when setting up security on the linked server properties. The DBA on the remote server can also setup the remote account with least privillage, only granting read access to those objects required for reporting purposes.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 20, 2012 at 12:15 pm
Ah, seeing all this activity on this post brings back memories.
I eventually got what I needed back in 2008, and since then, things have become a little simpler at my organization when requesting a cross-server link.
As for the latest post, there are definitely the usual security options that are available within SQL Server.
What about data traveling across the wire? Is it encrypted?
January 20, 2012 at 12:51 pm
Jeff Weight (1/20/2012)
Ah, seeing all this activity on this post brings back memories.I eventually got what I needed back in 2008, and since then, things have become a little simpler at my organization when requesting a cross-server link.
As for the latest post, there are definitely the usual security options that are available within SQL Server.
What about data traveling across the wire? Is it encrypted?
OLEDB will generally encrypt login credentials, but as far as encrypting data packets, I believe you have to setup certificates and such between the server and the client, and in this case between two servers. This is nothing specific to linked server connections specifically.
Are you really concerened about data sniffing between your two SQL Server instances?
What about data sniffing between the server and the application?
Encrypting Connections to SQL Server
http://msdn.microsoft.com/en-us/library/ms189067(v=SQL.100).aspx
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 20, 2012 at 1:03 pm
...and, that's why it says "Grasshopper" right under my name 🙂 You're right about that.
January 20, 2012 at 2:11 pm
Jeff Weight (1/20/2012)
...and, that's why it says "Grasshopper" right under my name 🙂 You're right about that.
Encrypting the network transport stream for SQL Server is an interesting question. It's not commonly done, but it is possible. I found this article:
http://myserverstuff.blogspot.com/2009/09/sql-network-transport-level-encryption.html
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 21, 2012 at 12:34 am
Hi,
I also have one question:
if we are not define "Local login to remote mapping" and we are going to use dedicated user from remote DB, do anyone could share any security risks?
Remote login has only db_reader and each and every other db roles were revoked.
August 14, 2012 at 6:15 am
One security concern .
When we connect any database using linked server .
All operation no data base will be operated by one user account( the account used for creating linked server)
So we can not have any track history who had made any change or access data from database ( all track will be against one user account).
August 14, 2012 at 6:35 am
sandeep rawat (8/14/2012)
One security concern .When we connect any database using linked server .
All operation no data base will be operated by one user account( the account used for creating linked server)
So we can not have any track history who had made any change or access data from database ( all track will be against one user account).
You can configure the linked server to connect to remote server using the current login's security context. In that case each user of the linked server would also need a login on the remote server too. This also restricts what users on local server can access the lined server connection.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 14, 2012 at 8:03 am
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 ...
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply