Criar servidores vinculados (Mecanismo de Banco de Dados do SQL Server)

  • Artigo

Aplica-se a: SQL Server Instância Gerenciada de SQL do Azure

Este artigo mostra como criar um servidor vinculado e acessar dados de outro SQL Server, uma Instância Gerenciada de SQL do Azure ou outra fonte de dados usando o SQL Server Management Studio (SSMS) ou o Transact-SQL. Servidores vinculados permitem que o mecanismo de banco de dados do SQL Server e a Instância Gerenciada de SQL do Azure leiam dados das fontes de dados remotas e executem comandos nos servidores de banco de dados remotos (por exemplo, fontes de dados OLE DB) fora da instância do SQL Server.

Tela de fundo

Normalmente, servidores vinculados são configurados para permitir que o mecanismo de banco de dados execute uma instrução Transact-SQL que inclui tabelas em outra instância do SQL Server ou em outro produto de banco de dados, como o Oracle. Muitos tipos de fontes de dados podem ser configurados como servidores vinculados, incluindo provedores de banco de dados de terceiros e o Azure Cosmos DB.

Depois que um servidor vinculado é criado, consultas distribuídas podem ser executadas nesse servidor, e as consultas podem unir tabelas de mais de uma fonte de dados. Se o servidor vinculado for definido como uma instância do SQL Server ou uma Instância Gerenciada de SQL do Azure, procedimentos armazenados remotos poderão ser executados.

Os recursos e os argumentos necessários do servidor vinculado podem variar significativamente. Os exemplos neste artigo fornecem um exemplo típico, mas nem todas as opções estão descritas. Para obter mais informações, confira sp_addlinkedserver (Transact-SQL).

Permissões

Ao usar instruções Transact-SQL, é necessária a permissão ALTER ANY LINKED SERVER no servidor ou uma associação na função de servidor fixa setupadmin. Ao usar o Management Studio, é necessária a permissão CONTROL SERVER ou uma associação na função de servidor fixa sysadmin.

Criar um servidor vinculado com o SSMS

Crie um servidor vinculado com o SSMS usando o procedimento a seguir:

Abra a caixa de diálogo Novo Servidor Vinculado

No SQL Server Management Studio (SSMS):

  1. Abra o Pesquisador de Objetos.
  2. Expanda Objetos do Servidor.
  3. Clique com o botão direito em Servidores Vinculados.
  4. Selecione Novo Servidor Vinculado.

Editar a página Geral das propriedades do servidor vinculado

Na página Geral, na caixa Servidor Vinculado, digite o nome da instância do SQL Server à qual você está se vinculando.

Observação

Se a instância do SQL Server for a instância padrão, insira o nome do computador que hospeda a instância do SQL Server. Se o SQL Server for uma instância nomeada, insira o nome do computador e o nome da instância, como Accounting\SQLExpress.

Especifique o Tipo de servidor e informações relacionadas, se necessário:

  • SQL Server
    Identifique o servidor vinculado como uma instância do Microsoft SQL Server ou uma Instância Gerenciada de SQL do Azure. Se você usar esse método para definir um servidor vinculado, o nome especificado em Servidor vinculado deverá ser o nome da rede do servidor. Além disso, todas as tabelas recuperadas do servidor são provenientes do banco de dados padrão definido para o logon no servidor vinculado.

  • Outra fonte de dados
    Especifique um tipo de servidor OLE DB diferente do SQL Server. Clicar nessa opção ativa as opções abaixo dela.

    • Provedor
      Selecione uma fonte de dados OLE DB na caixa de listagem. O provedor OLE DB está registrado com o PROGID fornecido no registro.

    • Nome do produto
      Digite o nome do produto da fonte de dados OLE DB para adicionar como servidor vinculado.

    • Fonte de dados
      Digite o nome da fonte de dados conforme interpretado pelo provedor OLE DB. Se você estiver se conectando a uma instância do SQL Server, forneça o nome dessa instância.

    • Cadeia de caracteres do provedor
      Digite o identificador programático exclusivo (PROGID) do provedor OLE DB que corresponde à fonte de dados. Para ver exemplos de cadeias de caracteres de provedor válidas, consulte sp_addlinkedserver (Transact-SQL).

    • Localidade
      Digite a localização do banco de dados, conforme interpretado pelo provedor OLE DB.

    • Catálogo
      Digite o nome do catálogo a ser usado ao fazer uma conexão com o provedor OLE DB.

Editar a página Segurança das propriedades do servidor vinculado

Na página Segurança, especifique o contexto de segurança usado quando a instância original se conecta ao servidor vinculado. Aqui, há duas estratégias para configurar que podem ser usadas sozinhas ou combinadas. A primeira é mapear os logons do servidor local para o servidor remoto e a segunda é como o servidor vinculado deve tratar os logons que não estão mapeados.

Adicionar mapeamentos de logon

Opcionalmente, você pode especificar como os logons específicos do servidor local são autenticados usando o servidor vinculado.

Em Logon do servidor local para mapeamentos de logon do servidor remoto, repita o processo a seguir para cada logon que você gostaria de mapear:

  1. Selecione Adicionar.

  2. Especifique um Logon local.

    Especifique o logon local que pode se conectar ao servidor vinculado. O logon local pode ser um logon usando a autenticação do SQL Server ou um logon de autenticação do Windows. Não há suporte para o uso de um grupo do Windows ou de um usuário de banco de dados independente. Use essa lista para restringir a conexão a logons específicos ou para permitir que alguns logons se conectem como um logon diferente.

    Observação

    Problemas comuns com servidores vinculados usando a autenticação do Windows em uma instância remota do SQL Server surgem de problemas com nomes de entidades de serviço (SPNs). Para obter mais informações, consulte Suporte para nomes de entidades de serviço (SPNs) em conexões de cliente. Microsoft Kerberos Configuration Manager for SQL Server é uma ferramenta de diagnóstico que ajuda a solucionar problemas de Kerberos relativos à conectividade com SQL Server. Para obter mais informações, consulte Microsoft Kerberos Configuration Manager for SQL Server.

  3. Selecione Representar (opcional).

    Transmita o nome de usuário e a senha do logon local para o servidor vinculado. Para a autenticação do SQL Server, um logon com exatamente o mesmo nome e senha deve existir no servidor remoto. Para logons do Windows, o logon deve ser válido no servidor vinculado.

    Para usar a representação, a configuração deve atender ao requisito de delegação.

  4. Especifique um Usuário Remoto se você não estiver usando representação.

    Use o usuário remoto para mapear o usuário definido em Logon local. O Usuário Remoto deve ser um logon de autenticação do SQL Server no servidor remoto.

  5. Especifique uma Senha Remota se não estiver usando representação.

    • Especifique a senha do Usuário Remoto.

  6. Selecione Remover para remover um logon local existente, se desejar.

Especifique o contexto de segurança padrão para logons não presentes na lista de mapeamento

Em um ambiente de domínio em que os usuários se conectam usando seus logons de domínio, selecionar Ser feita usando o contexto de segurança atual do logon geralmente é a melhor escolha. Quando os usuários se conectam ao SQL Server original usando um logon do SQL Server, a melhor opção geralmente é selecionar Usando este contexto de segurança e, em seguida, fornecer as credenciais necessárias para se autenticar no servidor vinculado.

Selecione uma das seguintes opções:

  • Não pode ser feita
    Não será feita uma conexão para logons não definidos na lista.

  • Ser feita sem usar um contexto de segurança
    Uma conexão será feita sem usar um contexto de segurança para logons não definidos na lista.

  • Ser feita usando o contexto de segurança atual do logon
    Uma conexão será feita usando o contexto de segurança atual do logon para logons não definidos na lista. Se você estiver conectado ao servidor local usando a Autenticação do Windows, suas credenciais do Windows serão usadas para se conectar ao servidor remoto. Se você estiver conectado ao servidor local usando a Autenticação do SQL Server, o nome de logon e a senha serão usados para se conectar ao servidor remoto. Nesse caso, um logon com exatamente o mesmo nome e senha deve existir no servidor remoto.

  • Ser feita usando este contexto de segurança
    Uma conexão será feita usando o logon e a senha especificados nas caixas Logon remoto e Com senha para logons não definidos na lista. O logon remoto deve ser um logon de autenticação do SQL Server no servidor remoto.

    Cuidado

    Se um servidor vinculado estiver configurado com a opção Ser realizado usando este contexto de segurança, qualquer usuário na instância poderá acessar o servidor vinculado remoto usando esse contexto. Isso pode ter o potencial indesejado de abuso ou acesso interno mal-intencionado. O Acesso remoto autenticado do SQL fornecido ao servidor vinculado deve conceder permissões mínimas necessárias ao servidor remoto para garantir um princípio de privilégio mínimo e reduzir a superfície de ataque.

Editar a página Opções do Servidor nas propriedades do servidor vinculado (opcional)

Para exibir ou especificar opções do servidor, selecione a página Opções do Servidor. Você pode editar qualquer uma das seguintes opções:

  • Compatível com Ordenação
    Afeta a execução de consultas distribuídas em servidores vinculados. Se essa opção for definida como verdadeira, o SQL Server presumirá que todos os caracteres no servidor vinculado são compatíveis com o servidor local, com relação ao conjunto de caracteres e à sequência de ordenação (ou ordem de classificação). Isso permite que o SQL Server envie comparações em colunas de caracteres ao provedor. Se essa opção não estiver definida, o SQL Server sempre avaliará as comparações nas colunas de caracteres localmente.

    Essa opção deve ser definida somente se houver certeza de que a fonte de dados correspondente ao servidor vinculado tem o mesmo conjunto de caracteres e a mesma ordem de classificação que o servidor local.

  • Acesso a dados
    Habilita e desativa um servidor vinculado para acesso a consultas distribuídas.

  • RPC
    Habilita chamadas de procedimento remoto (RPC) do servidor especificado.

  • Saída RPC
    Habilita a RPC para o servidor especificado.

  • Usar Ordenação Remota
    Determina se a ordenação de uma coluna remota ou de um servidor local será usada.

    Se verdadeiro, a ordenação de colunas remotas será usada para fontes de dados do SQL Server, e a ordenação especificada no nome da ordenação será usada para fontes de dados que não sejam do SQL Server.

    Se for falso, as consultas distribuídas sempre usarão a ordenação padrão do servidor local, enquanto o nome da ordenação e a ordenação das colunas remotas serão ignorados. O padrão é falso.

  • Nome da Ordenação
    Especifica o nome da ordenação usada pela fonte de dados remota se Usar ordenação remota for verdadeiro e se a fonte de dados não for uma fonte de dados do SQL Server. O nome deve ser uma das ordenações com suporte pelo SQL Server.

    Use essa opção ao acessar uma fonte de dados OLE DB diferente do SQL Server, mas cuja ordenação corresponda a uma das ordenações do SQL Server.

    O servidor vinculado deve oferecer suporte a uma única ordenação a ser usada em todas as colunas desse servidor. Não defina essa opção se o servidor vinculado oferecer suporte a várias ordenações em uma única fonte de dados ou se a ordenação do servidor vinculado não puder ser determinada como correspondendo a uma das ordenações do SQL Server.

  • Tempo-limite da conexão
    Valor de tempo limite em segundos para conexão com um servidor vinculado.

    Se for 0, use o valor padrão sp_configure da opção de tempo limite de logon remoto.

  • Tempo Limite de Consulta
    Valor de tempo limite em segundos para consultas em um servidor vinculado.

    Se for 0, use o valor padrão sp_configure da opção de tempo limite de consulta remota.

  • Habilitar Promoção de Transações Distribuídas
    Use essa opção para proteger as ações de um procedimento de servidor para servidor por meio de uma transação do Coordenador de Transações Distribuídas da Microsoft (MS DTC). Quando essa opção é TRUE, chamar um procedimento armazenado remoto inicia uma transação distribuída e inscreve essa transação no MS DTC. Para obter mais informações, consulte sp_serveroption (Transact-SQL).

Salvar o servidor vinculado

Selecione OK.

Visualizar ou editar opções de provedor do servidor vinculado no SSMS

Nem todos os provedores têm as mesmas opções disponíveis. Por exemplo, alguns tipos de dados podem ter índices disponíveis, enquanto outros não. Use essa caixa de diálogo para ajudar o SQL Server a compreender os recursos do provedor. O SQL Server instala alguns provedores de dados comuns. Porém, quando o produto que fornece os dados muda, o provedor instalado pelo SQL Server pode não oferecer suporte a todos os recursos mais recentes. A melhor fonte de informações sobre os recursos do produto que fornece os dados é a documentação desse produto.
Para abrir a página Opções de Provedores do servidor vinculado no SSMS:

  1. Abra o Pesquisador de Objetos.
  2. Expanda Objetos do Servidor.
  3. Expanda Servidores Vinculados.
  4. Expanda Provedores.
  5. Clique com o botão direito do mouse em um provedor e selecione Propriedades.

As opções do provedor são definidas da seguinte forma:

  • Parâmetro dinâmico
    Indica que o provedor permite a sintaxe do marcador de parâmetro '?' para consultas parametrizadas. Defina essa opção somente se o provedor oferecer suporte à interface ICommandWithParameters e oferecer suporte a um '?' como marcador de parâmetros. Definir essa opção permite que o SQL Server execute consultas parametrizadas em relação ao provedor. A capacidade de executar consultas parametrizadas em relação ao provedor pode resultar em melhor desempenho para determinadas consultas.

  • Consultas aninhadas
    Indica que o provedor permite instruções SELECT aninhadas na cláusula FROM. A configuração dessa opção permite que o SQL Server delegue determinadas consultas ao provedor que exigem o aninhamento de instruções SELECT na cláusula FROM.

  • Somente nível zero
    Somente interfaces OLE DB de nível 0 são invocadas no provedor.

  • Permitir em processo

    O SQL Server permite que o provedor seja instanciado como um servidor em processo. Quando essa opção não está definida, o comportamento padrão é instanciar o provedor fora do processo do SQL Server. A instanciação do provedor fora do processo do SQL Server protege este último contra erros no provedor. Quando o provedor é instanciado fora do processo do SQL Server, atualizações ou inserções que façam referência a colunas longas (text, ntext ou image) não são permitidas.

  • Atualizações não transacionadas
    O SQL Server permite atualizações, mesmo que ITransactionLocal não esteja disponível. Se essa opção estiver habilitada, as atualizações no provedor não poderão ser recuperadas, pois o provedor não oferece suporte a transações.

  • Indexar como caminho de acesso
    O SQL Server tenta usar índices do provedor para buscar dados. Por padrão, índices são usados somente para metadados e nunca são abertos

  • Proibir acesso ad hoc
    O SQL Server não permite acesso ad hoc por meio das funções OPENROWSET e OPENDATASOURCE no provedor OLE DB. Quando essa opção não está definida, o SQL Server também não permite acesso ad hoc.

  • Oferece suporte ao operador 'Like'
    Indica que o provedor oferece suporte a consultas usando a palavra-chave LIKE.

Criar um servidor vinculado com Transact-SQL

Para criar um servidor vinculado usando Transact-SQL, use as instruções sp_addlinkedserver (Transact-SQL), CREATE LOGIN (Transact-SQL) e sp_addlinkedsrvlogin (Transact-SQL).

Esse exemplo cria um servidor vinculado a outra instância do SQL Server usando Transact-SQL:

  1. No Editor de Consultas, insira o seguinte comando Transact-SQL para vincular a uma instância do SQL Server chamada SRVR002\ACCTG:

    USE [master]  
GO  
EXEC master.dbo.sp_addlinkedserver   
    @server = N'SRVR002\ACCTG',   
    @srvproduct=N'SQL Server';  
GO

  2. Execute o código a seguir para configurar o servidor vinculado para usar as credenciais de domínio do logon que está usando o servidor vinculado.

    EXEC master.dbo.sp_addlinkedsrvlogin   
    @rmtsrvname = N'SRVR002\ACCTG',   
    @locallogin = NULL ,   
    @useself = N'True';  
GO

Acompanhamento: etapas a serem seguidas após a criação de um servidor vinculado

As etapas a seguir ajudam você a validar um servidor vinculado.

Teste o servidor vinculado

Considerando uma das duas abordagens a seguir para testar a autenticação de um servidor vinculado em seu contexto de segurança atual.

  • Para testar a capacidade de se conectar a um servidor vinculado no SSMS, navegue até o servidor vinculado no Pesquisador de Objetos, clique com o botão direito do mouse no servidor vinculado e selecione Testar Conectividade.

  • Para testar a capacidade de se conectar a um servidor vinculado no T-SQL, execute uma instrução SELECT simples, por exemplo, para recuperar informações básicas do catálogo do banco de dados. Este exemplo retorna os nomes dos bancos de dados no servidor vinculado.

    SELECT name FROM [SRVR002\ACCTG].master.sys.databases;  
GO

Unir tabelas de um servidor vinculado

Use nomes de quatro partes para fazer referência a um objeto em um servidor vinculado. Execute o código a seguir para retornar uma lista de todos os logons no servidor local e seus logons correspondentes no servidor vinculado.

SELECT local.name AS LocalLogins, linked.name AS LinkedLogins  
FROM master.sys.server_principals AS local  
LEFT JOIN [SRVR002\ACCTG].master.sys.server_principals AS linked  
     ON local.name = linked.name;  
GO

Quando NULL é retornado para o logon do servidor vinculado, isso indica que o logon não existe no servidor vinculado. Esses logons não poderão usar o servidor vinculado, a menos que este esteja configurado para transmitir um contexto de segurança diferente ou se aceitar conexões anônimas.

Servidores vinculados com a Instância Gerenciada de SQL do Azure

Se você estiver usando a Instância Gerenciada de SQL do Azure, veja os seguintes exemplos de sp_addlinkedserver (Transact-SQL):

Próximas etapas

Saiba mais sobre o gerenciamento de servidores vinculados nestes artigos: