Site icon Eitan Blumin's blog

Are you using Linked Servers? They may be in serious danger!

Eitan Blumin

Linked Servers are an easy way to allow direct access between different SQL Servers. But, by using this feature, you may have created a wide-open backdoor into your servers! Read on for more details…

When configuring the security settings of a linked server, it’s possible to set a “default” authentication setting (where it says “For a login not defined in the list above, connections will:“):

Big no-no!

If you choose the option “Be made using this security context:” and provide a remote login with a password, it would mean that any user can use this linked server, regardless of their permissions set or role. Yes, even fresh new logins that don’t have any permissions or roles at all!

Depending on the permissions that such a remote login has at the linked server, this could potentially pose a serious security threat.

It is especially dangerous if your linked servers use a login that has the sysadmin role at the remote server. But really, any login with higher permissions than the current user, could mean bad news.

Don’t believe me? I’ll prove it!

In order to prove it to you, the script below will do the following:

  1. Create a login with the sysadmin role.
  2. Create a linked server pointing back to the current server (self-referencing). Use default settings.
  3. Set the sysadmin login from step 1 as the default remote login for the linked server.
  4. Create another, new login, without any permissions.

Open a new query in SSMS and run the following:

USE [master]
GO
CREATE LOGIN [TestAdmin] WITH PASSWORD=N'Password1', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [TestAdmin]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'MYLINKEDSERVER', @srvproduct=N'', @provider=N'SQLNCLI11', @datasrc=N'.'
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'MYLINKEDSERVER', @locallogin = NULL , @useself = N'False', @rmtuser = N'TestAdmin', @rmtpassword = N'Password1'
GO
CREATE LOGIN [TestGuest] WITH PASSWORD=N'Password2', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

Next, open a new connection in SSMS using the “TestGuest” login that we’ve created in the script above (with “Password2” as its password). Next, run the following script:

SET NOCOUNT ON;
DECLARE @ProbeCheck NVARCHAR(MAX)

SET @ProbeCheck = N'
SELECT N''<source_server>'' as src, @@SERVERNAME as srv, SYSTEM_USER as usr, IS_SRVROLEMEMBER(''sysadmin'') as isadmin, COUNT(*) as linked_servers
FROM sys.sysservers
WHERE srvid <> 0'

EXEC(@ProbeCheck)

DECLARE @CMD NVARCHAR(MAX), @Srv SYSNAME, @Rpc BIT, @DataAccess BIT

DECLARE LS CURSOR
FAST_FORWARD
FOR
SELECT srvname, rpc, dataaccess --, pub, sub, dist, nonsqlsub
FROM sys.sysservers
WHERE srvid <> 0

OPEN LS
FETCH NEXT FROM LS INTO @Srv, @Rpc, @DataAccess

WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT N'Server: ' + QUOTENAME(@Srv) + N', RPC: ' + CONVERT(nvarchar,@Rpc) + N', Data Access: ' + CONVERT(nvarchar, @DataAccess)

	SET @CMD = N'SELECT *
FROM OPENQUERY(' + QUOTENAME(@Srv) + N', N''' + REPLACE(REPLACE(@ProbeCheck, N'<source_server>', @Srv), N'''', N'''''') + ''')'

	PRINT @CMD;

	EXEC(@CMD);

	FETCH NEXT FROM LS INTO @Srv, @Rpc, @DataAccess
END

CLOSE LS
DEALLOCATE LS

The script above is a possible “linked server crawler” script that a potential hacker might use, in order to discover what kind of linked servers exist in your instance, and whether any one of them impersonates a login with sysadmin permissions.

Result:

The script may be improved further to do a “nested crawl” of the discovered linked servers, and thus discover additional linked servers configured at the linked servers, and potentially open up additional back doors.

Damn! So what do I do now??

Are you panicking yet? 😲 Good! You should be!

First of all, let’s drop that linked server we’ve just created above. You wouldn’t want to leave that huge, gaping backdoor open. That would be rather ironic, wouldn’t it? 😬 Run the following script:

EXEC master.dbo.sp_dropserver @server=N'MYLINKEDSERVER', @droplogins='droplogins'

Speaking of which, it may just be best to re-examine your need for your linked servers in the first place. If there’s a way for you to, well, not use linked servers, then that’s what you should be doing. DROP THOSE LINKED SERVERS if you can.

Secondly, as a good rule-of-thumb, you should always strive to create linked servers ONLY if the impersonated remote logins have identical or lesser permissions than the local logins.

Use the mapping list to map local logins to remote logins, in order to give specific permissions to only the logins that actually need to use the linked server.

As for the option used under “For a login not defined in the list above, connections will:” – choose either “Not be made“, or “Be made using the login’s current security context“. The latter of which will “forward” the username and password of the current login to the linked server, thus retaining security levels (unless the same login has higher privileges in the remote server? Oh what a mess! Hopefully it’s intentional).

See example below
This is much better!

In order to figure out how to configure your linked servers exactly, you would need to ask the following questions:

The answers to the above questions would give you a better idea of how the linked server(s) should actually be configured (preferably using the mapping list).

Wait, you’re not done yet!

Hoo boy, if only it was that simple… One critical point you must remember – is to apply the above precautions to ALL of the SQL Server instances in your organization, and to ALL linked servers configured EVERYWHERE.

Otherwise, a hacker could possibly do some “nested crawling” back and forth and sideways, until they find a linked server which was NOT configured properly.

It’s enough to have just ONE compromised linked server in your environment that impersonates a sysadmin and it’s game over, buddy. That can potentially give them access to xp_cmdshell, and that’s a highway straight to Windows being compromised, trojans installed, and all other kinds of nasty.

I had hoped that there would’ve at least been a Condition type to be configured in SQL Server Policy Management, which would’ve helped us make sure that this naughty option would not be used.

Unfortunately, there isn’t. Also, Linked Server mapped logins don’t seem to exist at all as a Facet in Policy Management.

Therefore, I submitted a UserVoice suggestion to add Linked Server Mapped Logins as a Facet in Policy Management, and thus give us the possibility to define a policy that would prevent bad security practices with Linked Servers. Click here to help by voting for it.

Additional Resources

By far, the best and most detailed resources I could find in this topic, is this blog post published in NetSPI by Antti Rantasaari, and also this white paper published in NetSPI by Scott Sutterland. They both go into even more detail, and even provide some open-source hacking tools accordingly.

More resources below:

Cover Image by Michal Jarmoluk from Pixabay

Exit mobile version