sp_addlinkedserver (Transact-SQL)
Aplica-se a:
SQL ServerInstâ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.
Observação
A ID do Microsoft Entra era anteriormente conhecida como Azure Active Directory (Azure AD).
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'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, @provstr e @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é usado. O usoSQLNCLIredirecionará 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 fornecido no Registro. Em vez deSQLNCLI,MSOLEDBSQLrecomenda-se.A partir do SQL Server 2022 (16.x), você deve especificar um nome de provedor. O
MSOLEDBSQLé recomendado. Se você omitir @provider, poderá enfrentar um comportamento inesperado.
Importante
O SQL Server Native Client (geralmente abreviado como SNAC) foi removido do SQL Server 2022 (16.x) e do SSMS (SQL Server Management Studio) 19. O provedor OLE DB do SQL Server Native Client (SQLNCLI ou SQLNCLI11) e o Provedor OLE DB herdado da Microsoft para o SQL Server (SQLOLEDB) não são recomendados para um novo desenvolvimento. Alterne para o novo Driver do Microsoft OLE DB para SQL Server (MSOLEDBSQL) 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 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 é passada 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 como 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 instancename é 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_addlinkedservera partir de um logon local ou de um logon que não faça 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 IDparâmetro à cadeia de conexão. No exemplo a seguir,myUsero ID do 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 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 é passada como a DBPROP_INIT_CATALOG propriedade 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 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.
| Fonte de dados remota OLE DB. | Provedor OLE DB | @srvproduct | @provider | @datasrc | @location | @provstr | @catalog |
|---|---|---|---|---|---|---|---|
| SQL Server | Provedor OLE DB do SQL Server Native Client | SQL Server 1 (padrão) | |||||
| SQL Server | Provedor OLE DB do SQL Server Native Client | SQLNCLI |
Nome de rede do SQL Server (para instância padrão) | Nome do banco de dados (opcional) | |||
| SQL Server | 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 de Oracle | |||
| Access/Jet | Microsoft OLE DB Provider for Jet | Qualquer | Microsoft.Jet.OLEDB.4.0 |
Caminho completo de arquivo de banco de dados de Jet | |||
| Fonte de dados ODBC | Microsoft OLE DB Provider para ODBC | Qualquer | MSDASQL |
DSN do sistema da fonte de dados ODBC | |||
| Fonte de dados ODBC | Microsoft OLE DB Provider para ODBC | Qualquer | MSDASQL |
Cadeia de conexão ODBC | |||
| Sistema de arquivos | Provedor Microsoft OLE DB para serviço de indexação | Qualquer | MSIDXS |
Nome do catálogo do Indexing Service | |||
| Planilha do Microsoft Excel | Microsoft OLE DB Provider for Jet | Qualquer | Microsoft.Jet.OLEDB.4.0 |
Caminho completo do arquivo de 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 para DB2. | Nome de 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 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 que você especifique o nome do provedor mais antigo, SQLOLEDB, ele será alterado para SQLNCLI quando persistir 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 está 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.
Importante
Atualmente, a Instância Gerenciada SQL do Azure oferece suporte apenas ao SQL Server, Banco de Dados SQL e outras instâncias gerenciadas pelo SQL como fontes de dados remotas.
Importante
Quando um servidor vinculado é criado usando sp_addlinkedservero , um automapeamento padrão é adicionado para todos os logons locais. Para provedores que não sejam do SQL Server, logons autenticados do SQL Server podem obter acesso ao provedor na conta de serviço do SQL Server. Os administradores deveriam considerar o uso de sp_droplinkedsrvlogin <linkedserver_name>, NULL para remover o mapeamento global.
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 à 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.
Importante
O provedor OLE DB do SQL Server Native Client (SQLNCLI) permanece obsoleto e não é recomendável usá-lo para novos trabalhos de desenvolvimento. Em vez disso, use o novo Driver do 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 OLE DB da Microsoft para 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.
Observação
Este exemplo pressupõe que o Microsoft Access e o banco de dados de exemplo Northwind estão instalados e que o Northwind banco de dados reside 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 para 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 .
Observação
O nome da fonte de dados ODBC especificado deve ser definido como DSN do sistema antes de você 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 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 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 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 arquivo schema.ini, consulte a documentação do Mecanismo de Banco de Dados Jet.
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 para 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. 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 do SQL Server contém o recurso de consulta distribuída, que permite gravar 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, 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, 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.
Veja 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 este 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 SQL do Azure com autenticação de identidade gerenciada
Observação
A ID do Microsoft Entra era anteriormente conhecida como Azure Active 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 usar opcionalmente @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 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 do SQL não correspondem mais. Para verificar se esses dois valores correspondem, converta o SID em ID do aplicativo com a seguinte consulta.
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 seguinte T-SQL, substituindo <managed_instance> por seu próprio servidor de instância gerenciada 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 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)
Comentários
Em breve: Ao longo de 2024, eliminaremos os problemas do GitHub como o mecanismo de comentários para conteúdo e o substituiremos por um novo sistema de comentários. Para obter mais informações, consulte https://aka.ms/ContentUserFeedback.
Enviar e exibir comentários de