SQL – Consulta para obter o endereço IP do servidor

Existe uma consulta no SQL Server 2005 que eu possa usar para obter o IP ou nome do servidor?

SELECT CONNECTIONPROPERTY('net_transport') AS net_transport, CONNECTIONPROPERTY('protocol_type') AS protocol_type, CONNECTIONPROPERTY('auth_scheme') AS auth_scheme, CONNECTIONPROPERTY('local_net_address') AS local_net_address, CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port, CONNECTIONPROPERTY('client_net_address') AS client_net_address 

O código aqui lhe dará o endereço IP;

Isso funcionará para uma solicitação de cliente remoto para o SQL 2008 e mais recente.

Se você tiver conexões de memory compartilhada permitidas, a execução acima no próprio servidor fornecerá

  • “Memória compartilhada” como o valor de ‘net_transport’ e
  • NULL para ‘local_net_address’ e
  • ‘ será mostrado em ‘client_net_address’.

‘client_net_address’ é o endereço do computador de onde a solicitação foi originada, enquanto ‘local_net_address’ seria o SQL server (portanto, NULL sobre as conexões de memory compartilhada) e o endereço que você daria a alguém se não puder usar o servidor NetBios nome ou FQDN por algum motivo.

Eu aconselho fortemente contra o uso dessa resposta . Habilitar o shell out é uma péssima idéia em um SQL Server de produção.

Você pode obter o [hostname] \ [instancename] por:

 SELECT @@SERVERNAME; 

Para obter apenas o nome do host quando você tiver o nome do host \ formato do nome da instância:

 SELECT LEFT(ltrim(rtrim(@@ServerName)), Charindex('\', ltrim(rtrim(@@ServerName))) -1) 

Alternativamente, como apontado @GilM:

 SELECT SERVERPROPERTY('MachineName') 

Você pode obter o endereço IP real usando isto:

 create Procedure sp_get_ip_address (@ip varchar(40) out) as begin Declare @ipLine varchar(200) Declare @pos int set nocount on set @ip = NULL Create table #temp (ipLine varchar(200)) Insert #temp exec master..xp_cmdshell 'ipconfig' select @ipLine = ipLine from #temp where upper (ipLine) like '%IP ADDRESS%' if (isnull (@ipLine,'***') != '***') begin set @pos = CharIndex (':',@ipLine,1); set @ip = rtrim(ltrim(substring (@ipLine , @pos + 1 , len (@ipLine) - @pos))) end drop table #temp set nocount off end go declare @ip varchar(40) exec sp_get_ip_address @ip out print @ip 

Fonte do script SQL .

O servidor pode ter vários endereços IP em que está escutando. Se a sua conexão tiver a permissão do servidor VIEW SERVER STATE concedida a ela, você poderá executar essa consulta para obter o endereço que conectou ao SQL Server:

 SELECT dec.local_net_address FROM sys.dm_exec_connections AS dec WHERE dec.session_id = @@SPID; 

Esta solução não exige que você salte para o SO via xp_cmdshell, que é uma técnica que deve ser desativada (ou pelo menos estritamente segura) em um servidor de produção. Ele pode exigir que você conceda o VIEW SERVER STATE ao login apropriado, mas esse é um risco de segurança muito menor do que executar o xp_cmdshell.

A técnica mencionada pelo GilM para o nome do servidor é a preferida:

 SELECT SERVERPROPERTY(N'MachineName'); 

A maioria das soluções para obter o endereço IP via t-sql se enquadra nesses dois campos:

  1. Executar ipconfig.exe via xp_cmdshell e analisar a saída

  2. Consulta DMV sys.dm_exec_connections

Eu não sou fã da opção # 1. Ativar o xp_cmdshell tem desvantagens de segurança e há muita análise envolvida de qualquer maneira. Isso é complicado. Opção # 2 é elegante. E é uma solução t-sql pura, que eu quase sempre prefiro. Aqui estão duas consultas de amostra para a opção 2:

 SELECT c.local_net_address FROM sys.dm_exec_connections AS c WHERE c.session_id = @@SPID; SELECT TOP(1) c.local_net_address FROM sys.dm_exec_connections AS c WHERE c.local_net_address IS NOT NULL; 

Às vezes, nenhuma das consultas acima funciona. Consulta nº 1 retorna NULL se você estiver conectado por meio da Memória compartilhada (conectado e executando o SSMS no host do SQL). Consulta # 2 pode retornar nada se não houver conexões usando um protocolo de memory não compartilhada. Este cenário é provável quando conectado a uma instância SQL recém-instalada. A solução? Forçar uma conexão por TCP / IP. Para fazer isso, crie uma nova conexão no SSMS e use o prefixo “tcp:” com o nome do servidor. Em seguida, execute novamente a consulta e você receberá o endereço IP.

SSMS - Conectar ao Mecanismo de Banco de Dados

Está na variável @@ SERVERNAME ;

 SELECT @@SERVERNAME; 

você pode usar a consulta de linha de comando e executar em mssql:

 exec xp_cmdshell 'ipconfig' 
 select @@servername 

Uma maneira mais simples de obter o nome da máquina sem o \ InstanceName é:

 SELECT SERVERPROPERTY('MachineName') 

– Experimente este script que funciona para as minhas necessidades. Reformate para ler.

 SELECT SERVERPROPERTY('ComputerNamePhysicalNetBios') as 'Is_Current_Owner' ,SERVERPROPERTY('MachineName') as 'MachineName' ,case when @@ServiceName = Right (@@Servername,len(@@ServiceName)) then @@Servername else @@servername +' \ ' + @@Servicename end as '@@Servername \ Servicename', CONNECTIONPROPERTY('net_transport') AS net_transport, CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port, dec.local_tcp_port, CONNECTIONPROPERTY('local_net_address') AS local_net_address, dec.local_net_address as 'dec.local_net_address' FROM sys.dm_exec_connections AS dec WHERE dec.session_id = @@SPID; 

Eu sei que este é um post antigo, mas talvez esta solução pode ser útil quando você deseja recuperar o endereço IP e porta TCP de uma conexão de memory compartilhada (por exemplo, de um script executado no SSMS localmente no servidor). A chave é abrir uma conexão secundária com o SQL Server usando OPENROWSET, no qual você especifica ‘tcp:’ na sua cadeia de conexão. O resto do código é meramente construir SQL dynamic para contornar a limitação da OPENROWSET de não ser capaz de pegar variables ​​como seus parâmetros.

 DECLARE @ip_address varchar(15) DECLARE @tcp_port int DECLARE @connectionstring nvarchar(max) DECLARE @parm_definition nvarchar(max) DECLARE @command nvarchar(max) SET @connectionstring = N'Server=tcp:' + @@SERVERNAME + ';Trusted_Connection=yes;' SET @parm_definition = N'@ip_address_OUT varchar(15) OUTPUT , @tcp_port_OUT int OUTPUT'; SET @command = N'SELECT @ip_address_OUT = a.local_net_address, @tcp_port_OUT = a.local_tcp_port FROM OPENROWSET(''SQLNCLI'' , ''' + @connectionstring + ''' , ''SELECT local_net_address , local_tcp_port FROM sys.dm_exec_connections WHERE session_id = @@spid '') as a' EXEC SP_executeSQL @command , @parm_definition , @ip_address_OUT = @ip_address OUTPUT , @tcp_port_OUT = @tcp_port OUTPUT; SELECT @ip_address, @tcp_port