sp_addlinkedserver (Transact-SQL)

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

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_addlinkedservero , as 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.

Convenções de sintaxe de Transact-SQL

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'srvproduto'

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, @provstr e @catalog não precisam ser especificados.

@provider [ = ] N'provedor'

O PROGID (identificador programático exclusivo) 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 SQLNCLI redirecionará o SQL Server para a versão mais recente do SQL Server Native Client OLE DB Provider. Espera-se que o provedor OLE DB seja registrado com o PROGID especificado fornecido no Registro. Em vez de SQLNCLI, MSOLEDBSQL é recomendado.

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

@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 é passado como a DBPROP_INIT_DATASOURCE propriedade 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 é passado como a DBPROP_INIT_LOCATION propriedade 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 DBPROP_INIT_PROVIDERSTRING propriedade 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 SERVER palavra-chave as SERVER=servername\instancename para especificar uma instância específica do SQL Server. O nome do servidor é o nome do computador no qual o SQL Server está sendo executado e o nome da instância específica do SQL Server à qual o usuário será conectado.

• Para acessar um banco de dados espelho, 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 de 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 você executar sp_addlinkedserver a partir de um logon local ou de um logon que não faz parte da função sysadmin , poderá receber o seguinte erro:

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

  Para resolver esse problema, adicione o User ID parâmetro à cadeia de conexão. No exemplo a seguir, myUser a ID do usuário é passada 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 logon.

@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 é passado como a DBPROP_INIT_CATALOG propriedade para inicializar o provedor OLE DB. Quando o servidor vinculado é definido em uma instância do SQL Server, catalog refere-se ao banco de dados padrão para o qual o servidor vinculado é mapeado.

@linkedstyle [ = ] estilo vinculado

Identificado apenas para fins informativos. Não há suporte. A compatibilidade futura não está garantida.

Valores do código de retorno

0 (sucesso) ou 1 (falha).

Conjunto de resultados

Nenhum.

Comentários

A tabela a seguir mostra as formas que um servidor vinculado pode ser definido para que as fontes de dados possam ser acessadas através do OLE DB. Um servidor vinculado pode ser definido em mais de uma forma para uma fonte de dados em particular; pode haver mais de uma linha para um tipo de fonte de dados. Esta tabela também mostra os valores de sp_addlinkedserver parâmetro a serem usados para configurar o servidor vinculado.

1 Essa maneira de configurar um servidor vinculado força o nome do servidor vinculado a ser o mesmo que o nome de 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 será alterado para SQLNCLI quando persistido no catálogo.

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

Em um ambiente clusterizado, quando você especificar os nomes de arquivo para apontarem para fontes de dados OLE DB, use o nome UNC (Convenção Universal de nomenclatura) ou um drive compartilhado para especificar o local.

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

Permissões

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

Exemplos

R. Usar o provedor OLE DB do Microsoft SQL Server

O exemplo abaixo cria um servidor vinculado chamado SEATTLESales. O nome de produto é SQL Server e nenhum nome de provedor é usado.

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

O exemplo a seguir cria um servidor S1_instance1 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 S1_instance1 vinculado em uma instância do SQL Server usando o provedor OLE DB do SQL Server Native Client.

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

B. Usar o Microsoft OLE DB Provider for Microsoft Access

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

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 datasrc parâmetro

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 .

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

D. Usar a planilha do 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 serem selecionadas. O nome do intervalo pode ser então 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 com um nome. A consulta a seguir pode ser usada para acessar um intervalo nomeado especificado SalesData como uma tabela usando a configuração do servidor vinculado anterior.

SELECT *
   FROM ExcelSource...SalesData;
GO

Se o SQL Server estiver em execução 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. Usar o Microsoft OLE DB Provider for Jet para acessar um arquivo de texto

O exemplo a seguir cria um servidor vinculado para acessar arquivos de texto diretamente sem vincular os arquivos como tabelas em um arquivo .mdb do Access. 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 ficheiro 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 usá-lo com consultas distribuídas que abrangem os bancos de dados locais e na nuvem. Esse é 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 do 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 não SQL Server) definidas como servidores vinculados. Cada banco de dados SQL do Azure (exceto o banco de dados do master 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, trabalho com um modelo de desenvolvimento familiar e um modelo de dados relacional. Os requisitos do 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, mantendo 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.

Aqui está um exemplo explicando 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 esse servidor vinculado.

EXEC sp_addlinkedsrvlogin
  @rmtsrvname = 'LinkedServerName',
  @useself = 'false',
  @rmtuser = 'LoginName',
  @rmtpassword = 'myPassword';

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 de SQL do Azure com autenticação de identidade gerenciada

Para criar um servidor vinculado com autenticação de identidade gerenciada, execute o T-SQL a seguir, substituindo <managed_instance> por sua própria instância gerenciada de SQL. O método de autenticação é usado ActiveDirectoryMSI no parâmetro @provstr . Considere usar opcionalmente @locallogin = NULL para permitir todos os logons 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 de 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 pelo sistema será usada. Se a identidade gerenciada for recriada com o mesmo nome, o logon na instância remota também precisará ser recriado, pois a nova ID do Aplicativo de identidade gerenciada e o SID da entidade de serviço da Instância Gerenciada de SQL não correspondem mais. Para verificar se esses dois valores correspondem, converta o 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>';

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

Para criar um servidor vinculado com autenticação de passagem, execute o T-SQL a seguir, substituindo <managed_instance> por seu próprio servidor de instância gerenciada de 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 logon 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 de SQL do Azure local e remota. Ambas as instâncias gerenciadas precisam estar em um grupo de confiança do 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

• Procedimentos armazenados de Consultas Distribuídas (Transact-SQL)
• sp_addlinkedsrvlogin (Transact-SQL)
• sp_addserver (Transact-SQL)
• sp_dropserver (Transact-SQL)
• sp_serveroption (Transact-SQL)
• sp_setnetname (Transact-SQL)
• Procedimentos armazenados do sistema (Transact-SQL)
• Tabelas do sistema (Transact-SQL)