Re: Linked Server Security
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Fri, 31 Jul 2009 09:36:28 +0000 (UTC)
Dan (Dan@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
The developer is using a sql login. I tested this (what I consider a
security flaw) by creating a sql user (JoeUser) on the dev box that did
not belong to any roles and was not given any access other than the
ability to connect to the dev server. I then connected as that user and
queried the corporate db with a SELECT * FROM
servername.dbname.dbo.table and it worked just fine. so as JoeUser with
no defined access to the corporate db I was able to circumvent the
corporate db security because of a developer who used his sql login to
create a linked server on a box he manages. So other than disabling the
developer's login account I can't find anyway of preventing him from
essentially granting his same level of access to any user he chooses.
Yes and no. It is correct, that the only thing you can do on your server
is to disable his login.
However, before you do this, I suggest that you request that this developer
sets up his login in the linked server connection correctly.
Presumably, the developer has set up the login this way:
EXEC sp_addlinkedsrvlogin 'YOURSERVER', 'false',
NULL, 'hislogin', 'hispassword'
The key here is the third parameter, @locallogin. When NULL, it specifies
that this login-mapping applies to all logins on his server. The
developer should instead have specified his login on the development
server. I'm here assuming that everyone who connects to the development
server use their own login. If everyone connects as sa, this won't work,
and you can only request that he removes the linked-server defintion.
I like to add that there is no security flaw per se here. Instead of
setting up the linked server, this developer could simply have shared
his username and password on your corporate with his developer mates,
and they could have logged in interactively. Now, it is quite clear,
that this vould be matter to disciplinary actions. But technically,
setting up the linked server is just the same. You could give him the
benefit of the doubt that he did not understand what he was doing. If
he did this on purpose, I think there is all reason to escalate this
beyond the technical realm.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- References:
- Linked Server Security
- From: Dan
- Re: Linked Server Security
- From: Erland Sommarskog
- Re: Linked Server Security
- From: Dan
- Linked Server Security
- Prev by Date: Re: Linked Server Security
- Next by Date: Re: Execute AS DBO
- Previous by thread: Re: Linked Server Security
- Next by thread: Execute AS DBO
- Index(es):
Relevant Pages
|
