Partilhar via

sp_addlinkedserver (Transact-SQL)

  • Artigo

Aplica-se a:SQL ServerAzure SQL Managed Instance

Cria um servidor vinculado. Um servidor vinculado fornece acesso a consultas distribuídas e heterogêneas em fontes de dados OLE DB. Depois que um servidor vinculado é criado usando sp_addlinkedserver, consultas distribuídas podem ser executadas nesse servidor. Se o servidor vinculado for definido como uma instância do SQL Server, os procedimentos armazenados remotos poderão ser executados.

Observação

Microsoft Entra ID era anteriormente conhecido como Azure Ative Directory (Azure AD).

Transact-SQL convenções de sintaxe

Sintaxe

sp_addlinkedserver
    [ @server = ] N'server'
    [ , [ @srvproduct = ] N'srvproduct' ]
    [ , [ @provider = ] N'provider' ]
    [ , [ @datasrc = ] N'datasrc' ]
    [ , [ @location = ] N'location' ]
    [ , [ @provstr = ] N'provstr' ]
    [ , [ @catalog = ] N'catalog' ]
    [ , [ @linkedstyle = ] linkedstyle ]
[ ; ]

Argumentos

[ @server = ] N'servidor'

O nome do servidor vinculado a ser criado. @server é sysname, sem padrão.

[ @srvproduct = ] N'srvproduct'

O nome do produto da fonte de dados OLE DB a ser adicionada como um servidor vinculado. @srvproduct é nvarchar(128), com um padrão de NULL. Se o valor for SQL Server, @provider, @datasrc, @location, @provstre @catalog não precisam ser especificados.

[ @provider = ] N'provedor'

O identificador programático exclusivo (PROGID) do provedor OLE DB que corresponde a essa fonte de dados. O @provider deve ser exclusivo para o provedor OLE DB especificado instalado no computador atual. @provider é nvarchar(128), com um padrão de NULL.

  • No SQL Server 2019 (15.x) e versões anteriores, se @provider for omitido, SQLNCLI será usado. O uso do SQLNCLI redirecionará o SQL Server para a versão mais recente do provedor OLE DB do SQL Server Native Client. Espera-se que o provedor OLE DB seja registrado com o PROGID especificado no registro. Em vez de SQLNCLI, recomenda-se MSOLEDBSQL.

  • A partir do SQL Server 2022 (16.x), você deve especificar um nome de provedor. MSOLEDBSQL é recomendado. Se você omitir @provider, poderá ter um comportamento inesperado.

Importante

O SQL Server Native Client (geralmente abreviado SNAC) foi removido do SQL Server 2022 (16.x) e do SQL Server Management Studio 19 (SSMS). O provedor OLE DB do SQL Server Native Client (SQLNCLI ou SQLNCLI11) e o provedor Microsoft OLE DB herdado para SQL Server (SQLOLEDB) não são recomendados para novos desenvolvimentos. Alterne para o novo Microsoft OLE DB Driver (MSOLEDBSQL) para SQL Server no futuro.

[ @datasrc = ] N'datasrc'

O nome da fonte de dados conforme interpretado pelo provedor OLE DB. @datasrc é nvarchar(4000), com um padrão de NULL. @datasrc é passada como a propriedade DBPROP_INIT_DATASOURCE para inicializar o provedor OLE DB.

[ @location = ] N'localização'

O local do banco de dados conforme interpretado pelo provedor OLE DB. @location é nvarchar(4000), com um padrão de NULL. @location é passada como a propriedade DBPROP_INIT_LOCATION para inicializar o provedor OLE DB.

[ @provstr = ] N'provstr'

A cadeia de conexão específica do provedor OLE DB que identifica uma fonte de dados exclusiva. @provstr é nvarchar(4000), com um padrão de NULL. O argumento provstr é passado para IDataInitialize ou definido como a propriedade DBPROP_INIT_PROVIDERSTRING para inicializar o provedor OLE DB.

Quando o servidor vinculado é criado no provedor OLE DB do SQL Server Native Client, a instância pode ser especificada usando a palavra-chave SERVER como SERVER=servername\instancename para especificar uma instância específica do SQL Server. A servername é o nome do computador no qual o SQL Server está sendo executado, e instancename é o nome da instância específica do SQL Server à qual o usuário será conectado.

  • Para acessar um banco de dados espelhado, uma cadeia de conexão deve conter o nome do banco de dados. Esse nome é necessário para habilitar tentativas de failover pelo provedor de acesso a dados. O banco de dados pode ser especificado no parâmetro @provstr ou @catalog. Opcionalmente, a cadeia de conexão também pode fornecer um nome de parceiro de failover.

  • Se executar sp_addlinkedserver a partir de um início de sessão local ou de um início de sessão que não faça parte da função de sysadmin, poderá receber o seguinte erro:

    Access to the remote server is denied because no login-mapping exists.

    Para resolver esse problema, adicione o parâmetro User ID à cadeia de conexão. No exemplo a seguir, myUser é o ID de usuário passado para a cadeia de conexão:

    EXEC master.dbo.sp_addlinkedserver @server = N'LinkServerName',
    @provider = N'SQLNCLI',
    @srvproduct = 'MS SQL Server',
    @provstr = N'SERVER=serverName\InstanceName;User ID=myUser'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'LinkServerName',
    @locallogin = NULL,
    @useself = N'False',
    @rmtuser = N'myUser',
    @rmtpassword = N'*****'

    Para obter mais informações, consulte O acesso ao servidor remoto é negado porque não existe mapeamento de login.

[ @catalog = ] N'catálogo'

O catálogo a ser usado quando uma conexão é feita com o provedor OLE DB. @catalog é sysname, com um padrão de NULL. @catalog é passada como a propriedade DBPROP_INIT_CATALOG para inicializar o provedor OLE DB. Quando o servidor vinculado é definido em relação a uma instância do SQL Server, catálogo refere-se ao banco de dados padrão para o qual o servidor vinculado é mapeado.

[ @linkedstyle = ] linkedstyle

Identificado apenas para fins informativos. Não suportado. A compatibilidade futura não é garantida.

Valores de código de retorno

0 (sucesso) ou 1 (fracasso).

Conjunto de resultados

Nenhuma.

Comentários

A tabela a seguir mostra as maneiras pelas quais um servidor vinculado pode ser configurado para fontes de dados que podem ser acessadas por meio do OLE DB. Um servidor vinculado pode ser configurado de mais de uma maneira para uma fonte de dados específica; Pode haver mais de uma linha para um tipo de fonte de dados. Esta tabela também mostra os sp_addlinkedserver valores de parâmetro a serem usados para configurar o servidor vinculado.

Fonte de dados OLE DB remota Provedor OLE DB @srvproduct @provider @datasrc @location @provstr @catalog
Servidor SQL Provedor OLE DB do SQL Server Native Client SQL Server 1 (padrão)
Servidor SQL Provedor OLE DB do SQL Server Native Client SQLNCLI Nome da rede do SQL Server (para instância padrão) Nome do banco de dados (opcional)
Servidor SQL Provedor OLE DB do SQL Server Native Client SQLNCLI servername\instancename (para instância específica) Nome do banco de dados (opcional)
Oracle, versão 8 e posterior Provedor Oracle para OLE DB Qualquer OraOLEDB.Oracle Alias para o banco de dados Oracle
Acesso/Jet Provedor Microsoft OLE DB para Jet Qualquer Microsoft.Jet.OLEDB.4.0 Caminho completo do arquivo de banco de dados Jet
Fonte de dados ODBC Provedor Microsoft OLE DB para ODBC Qualquer MSDASQL DSN do sistema da fonte de dados ODBC
Fonte de dados ODBC Provedor Microsoft OLE DB para ODBC Qualquer MSDASQL Cadeia de conexão ODBC
Sistema de ficheiros Provedor Microsoft OLE DB para serviço de indexação Qualquer MSIDXS Nome do catálogo do Serviço de Indexação
Planilha do Microsoft Excel Provedor Microsoft OLE DB para Jet Qualquer Microsoft.Jet.OLEDB.4.0 Caminho completo do arquivo Excel Excel 5.0
Banco de Dados IBM DB2 Provedor Microsoft OLE DB para DB2 Qualquer DB2OLEDB Consulte a documentação do Microsoft OLE DB Provider for DB2. Nome do catálogo do banco de dados DB2

1 Essa maneira de configurar um servidor vinculado força o nome do servidor vinculado a ser o mesmo que o nome da rede da instância remota do SQL Server. Use @datasrc para especificar o servidor.

2 "Qualquer" indica que o nome do produto pode ser qualquer coisa.

O provedor OLE DB do SQL Server Native Client é o provedor usado com o SQL Server se nenhum nome de provedor for especificado ou se o SQL Server for especificado como o nome do produto. Mesmo se você especificar o nome do provedor mais antigo, SQLOLEDB, ele mudará para SQLNCLI quando persistir no catálogo.

Os parâmetros @datasrc, @location, @provstre @catalog identificam o banco de dados ou bancos de dados para os quais o servidor vinculado aponta. Se qualquer um desses parâmetros estiver NULL, a propriedade de inicialização OLE DB correspondente não será definida.

Em um ambiente clusterizado, quando você especificar nomes de arquivo para apontar para fontes de dados OLE DB, use o nome da convenção de nomenclatura universal (UNC) ou uma unidade compartilhada para especificar o local.

O sp_addlinkedserver de procedimento armazenado não pode ser executado em uma transação definida pelo usuário.

Importante

Atualmente, a Instância Gerenciada SQL do Azure dá suporte apenas ao SQL Server, Banco de Dados SQL e outras instâncias gerenciadas SQL como fontes de dados remotas.

Importante

Quando um servidor vinculado é criado usando sp_addlinkedserver, um automapeamento padrão é adicionado para todos os logons locais. Para provedores que não sejam do SQL Server, os logons autenticados do SQL Server podem obter acesso ao provedor na conta de serviço do SQL Server. Os administradores devem considerar o uso de sp_droplinkedsrvlogin <linkedserver_name>, NULL para remover o mapeamento global.

Permissões

A instrução sp_addlinkedserver requer a permissão ALTER ANY LINKED SERVER. (A caixa de diálogo SQL Server Management Studio New Linked Server é implementada de uma forma que requer associação ao sysadmin função de servidor fixa.)

Exemplos

Um. Usar o provedor OLE DB do Microsoft SQL Server

O exemplo a seguir cria um servidor vinculado chamado SEATTLESales. O nome do produto é SQL Servere nenhum nome de fornecedor é usado.

USE master;
GO
EXEC sp_addlinkedserver
   N'SEATTLESales',
   N'SQL Server';
GO

O exemplo a seguir cria um S1_instance1 de servidor vinculado em uma instância do SQL Server usando o driver OLE DB do SQL Server.

EXEC sp_addlinkedserver
   @server=N'S1_instance1',
   @srvproduct=N'',
   @provider=N'MSOLEDBSQL',
   @datasrc=N'S1\instance1';

O exemplo a seguir cria um servidor vinculado S1_instance1 em uma instância do SQL Server usando o provedor OLE DB do SQL Server Native Client.

Importante

O provedor OLE DB do SQL Server Native Client (SQLNCLI) permanece preterido e não é recomendado usá-lo para novos trabalhos de desenvolvimento. Em vez disso, use o novo driver Microsoft OLE DB para SQL Server (MSOLEDBSQL) que será atualizado com os recursos de servidor mais recentes.

EXEC sp_addlinkedserver
   @server=N'S1_instance1',
   @srvproduct=N'',
   @provider=N'SQLNCLI',
   @datasrc=N'S1\instance1';

B. Usar o provedor Microsoft OLE DB para Microsoft Access

O provedor Microsoft.Jet.OLEDB.4.0 se conecta a bancos de dados do Microsoft Access que usam o formato 2002-2003. O exemplo a seguir cria um servidor vinculado chamado SEATTLE Mktg.

Observação

Este exemplo pressupõe que o Microsoft Access e o banco de dados Northwind de exemplo estejam instalados e que o banco de dados Northwind resida em C:\Msoffice\Access\Samples no mesmo servidor que a instância do SQL Server.

EXEC sp_addlinkedserver
   @server = N'SEATTLE Mktg',
   @provider = N'Microsoft.Jet.OLEDB.4.0',
   @srvproduct = N'OLE DB Provider for Jet',
   @datasrc = N'C:\MSOffice\Access\Samples\Northwind.mdb';
GO

C. Use o Microsoft OLE DB Provider for ODBC com o parâmetro datasrc

O exemplo a seguir cria um servidor vinculado chamado SEATTLE Payroll que usa o Microsoft OLE DB Provider for ODBC (MSDASQL) e o parâmetro @datasrc.

Observação

O nome da fonte de dados ODBC especificado deve ser definido como DSN do sistema no servidor antes de usar o servidor vinculado.

EXEC sp_addlinkedserver
   @server = N'SEATTLE Payroll',
   @srvproduct = N'',
   @provider = N'MSDASQL',
   @datasrc = N'LocalServer';
GO

D. Usar a planilha Microsoft OLE DB Provider for Excel

Para criar uma definição de servidor vinculado usando o Microsoft OLE DB Provider for Jet para acessar uma planilha do Excel no formato 1997 - 2003, primeiro crie um intervalo nomeado no Excel especificando as colunas e linhas da planilha do Excel a ser selecionada. O nome do intervalo pode então ser referenciado como um nome de tabela em uma consulta distribuída.

EXEC sp_addlinkedserver 'ExcelSource',
   'Jet 4.0',
   'Microsoft.Jet.OLEDB.4.0',
   'c:\MyData\DistExcl.xls',
   NULL,
   'Excel 5.0';
GO

Para acessar dados de uma planilha do Excel, associe um intervalo de células a um nome. A consulta a seguir pode ser usada para acessar o intervalo nomeado especificado SalesData como uma tabela usando o servidor vinculado configurado anteriormente.

SELECT *
   FROM ExcelSource...SalesData;
GO

Se o SQL Server estiver sendo executado em uma conta de domínio que tenha acesso a um compartilhamento remoto, um caminho UNC poderá ser usado em vez de uma unidade mapeada.

EXEC sp_addlinkedserver 'ExcelShare',
   'Jet 4.0',
   'Microsoft.Jet.OLEDB.4.0',
   '\\MyServer\MyShare\Spreadsheets\DistExcl.xls',
   NULL,
   'Excel 5.0';

E. Use o Microsoft OLE DB Provider for Jet para acessar um arquivo de texto

O exemplo a seguir cria um servidor vinculado para acessar diretamente arquivos de texto, sem vincular os arquivos como tabelas em um arquivo do Access .mdb. O provedor é Microsoft.Jet.OLEDB.4.0 e a cadeia de caracteres do provedor é Text.

A fonte de dados é o caminho completo do diretório que contém os arquivos de texto. Um arquivo schema.ini, que descreve a estrutura dos arquivos de texto, deve existir no mesmo diretório que os arquivos de texto. Para obter mais informações sobre como criar um arquivo schema.ini, consulte a documentação do Jet Database Engine.

Primeiro, crie um servidor vinculado.

EXEC sp_addlinkedserver txtsrv, N'Jet 4.0',
   N'Microsoft.Jet.OLEDB.4.0',
   N'c:\data\distqry',
   NULL,
   N'Text';

Configure mapeamentos de login.

EXEC sp_addlinkedsrvlogin txtsrv, FALSE, Admin, NULL;

Liste as tabelas no servidor vinculado.

EXEC sp_tables_ex txtsrv;

Consulte uma das tabelas, neste caso file1#txt, usando um nome de quatro partes.

SELECT * FROM txtsrv...[file1#txt];

F. Usar o Microsoft OLE DB Provider for DB2

O exemplo a seguir cria um servidor vinculado chamado DB2 que usa o Microsoft OLE DB Provider for DB2.

EXEC sp_addlinkedserver @server = N'DB2',
    @srvproduct = N'Microsoft OLE DB Provider for DB2',
    @catalog = N'DB2',
    @provider = N'DB2OLEDB',
    @provstr = N'Initial Catalog=pubs;
       Data Source=DB2;
       HostCCSID=1252;
       Network Address=XYZ;
       Network Port=50000;
       Package Collection=admin;
       Default Schema=admin;';

G. Adicionar um banco de dados SQL do Azure como um servidor vinculado para uso com consultas distribuídas em bancos de dados locais e na nuvem

Você pode adicionar um banco de dados SQL do Azure como um servidor vinculado e, em seguida, usá-lo com consultas distribuídas que abrangem os bancos de dados locais e na nuvem. Este é um componente para soluções híbridas de banco de dados que abrangem redes corporativas locais e a nuvem do Azure.

O produto de caixa SQL Server contém o recurso de consulta distribuída, que permite escrever consultas para combinar dados de fontes de dados locais e dados de fontes remotas (incluindo dados de fontes de dados que não sejam do SQL Server) definidas como servidores vinculados. Cada banco de dados SQL do Azure (exceto o banco de dados master do servidor lógico) pode ser adicionado como um servidor vinculado individual e, em seguida, usado diretamente em seus aplicativos de banco de dados como qualquer outro banco de dados.

Os benefícios de usar o Banco de Dados SQL do Azure incluem capacidade de gerenciamento, alta disponibilidade, escalabilidade, trabalhar com um modelo de desenvolvimento familiar e um modelo de dados relacional. Os requisitos do seu aplicativo de banco de dados determinam como ele usaria o Banco de Dados SQL do Azure na nuvem. Você pode mover todos os seus dados de uma só vez para o Banco de Dados SQL do Azure ou mover progressivamente alguns de seus dados enquanto mantém os dados restantes no local. Para esse aplicativo de banco de dados híbrido, o Banco de Dados SQL do Azure agora pode ser adicionado como servidores vinculados e o aplicativo de banco de dados pode emitir consultas distribuídas para combinar dados do Banco de Dados SQL do Azure e fontes de dados locais.

Veja um exemplo que explica como se conectar a um banco de dados SQL do Azure usando consultas distribuídas.

Primeiro, adicione um banco de dados SQL do Azure como servidor vinculado, usando o SQL Server Native Client.

EXEC sp_addlinkedserver
    @server = 'LinkedServerName',
    @srvproduct = '',
    @provider = 'sqlncli',
    @datasrc = 'ServerName.database.windows.net',
    @location = '',
    @provstr = '',
    @catalog = 'DatabaseName';

Adicione credenciais e opções a este servidor vinculado. Substitua <password> por uma senha válida.

EXEC sp_addlinkedsrvlogin
  @rmtsrvname = 'LinkedServerName',
  @useself = 'false',
  @rmtuser = 'LoginName',
  @rmtpassword = '<password>';

EXEC sp_serveroption 'LinkedServerName', 'rpc out', true;

Agora, use o servidor vinculado para executar consultas usando nomes de quatro partes, até mesmo para criar uma nova tabela e inserir dados.

EXEC ('CREATE TABLE SchemaName.TableName(col1 int not null CONSTRAINT PK_col1 PRIMARY KEY CLUSTERED (col1) )') at LinkedServerName;
EXEC ('INSERT INTO SchemaName.TableName VALUES(1),(2),(3)') at LinkedServerName;

Consulte os dados usando nomes de quatro partes:

SELECT * FROM LinkedServerName.DatabaseName.SchemaName.TableName;

H. Criar servidor vinculado da Instância Gerenciada SQL do Azure com autenticação de identidade gerenciada

Observação

Microsoft Entra ID era anteriormente conhecido como Azure Ative Directory (Azure AD).

Para criar um servidor vinculado com autenticação de identidade gerenciada, execute o seguinte T-SQL, substituindo-<managed_instance> por sua própria instância gerenciada SQL. O método de autenticação usa ActiveDirectoryMSI no parâmetro @provstr. Considere opcionalmente usar @locallogin = NULL para permitir todos os logins locais.

EXEC master.dbo.sp_addlinkedserver
    @server = N'MyLinkedServer',
    @srvproduct = N'',
    @provider = N'MSOLEDBSQL',
    @provstr = N'Server=<mi_name>.<dns_zone>.database.windows.net,1433;Authentication=ActiveDirectoryMSI;';

EXEC master.dbo.sp_addlinkedsrvlogin
    @rmtsrvname = N'MyLinkedServer',
    @useself = N'False',
    @locallogin = N'user1@contoso.com';

Para habilitar a autenticação com identidades gerenciadas, uma identidade gerenciada atribuída à Instância Gerenciada SQL do Azure precisa ser adicionada como um logon à instância gerenciada remota. Há suporte para identidades gerenciadas atribuídas pelo sistema e pelo usuário.

Se uma identidade primária for definida, ela será usada, caso contrário, a identidade gerenciada atribuída ao sistema será usada. Se a identidade gerenciada for recriada com o mesmo nome, o logon na instância remota também precisará ser recriado, porque a nova ID do Aplicativo de identidade gerenciada e o SID da entidade de serviço da Instância Gerenciada SQL não correspondem mais. Para verificar se esses dois valores correspondem, converta SID em ID do aplicativo com a consulta a seguir.

SELECT convert(uniqueidentifier, sid) as MSEntraApplicationID
FROM sys.server_principals
WHERE name = '<managed_instance_name>';

Eu. Criar servidor vinculado de Instância Gerenciada SQL com autenticação de passagem do Microsoft Entra

Para criar um servidor vinculado com autenticação de passagem, execute o seguinte T-SQL, substituindo-<managed_instance> por seu próprio servidor de instância gerenciado SQL:

EXEC master.dbo.sp_addlinkedserver
    @server = N'MyLinkedServer',
    @srvproduct = N'',
    @provider = N'MSOLEDBSQL',
    @datasrc = N'<mi_name>.<dns_zone>.database.windows.net,1433';

Com a autenticação de passagem, o contexto de segurança do login local é transferido para a instância remota. A autenticação de passagem requer que a entidade de segurança do Microsoft Entra seja adicionada como um logon na Instância Gerenciada SQL do Azure local e remota. Ambas as instâncias gerenciadas precisam estar em um grupo de confiança de servidor . Quando os requisitos são atendidos, o usuário pode entrar em uma instância local e consultar a instância remota por meio do objeto de servidor vinculado.

Conteúdo relacionado