SQL - 서버의 IP 주소를 가져오는 쿼리

SQL - 서버의 IP 주소를 가져오는 쿼리

SQL Server 2005에 서버의 IP 또는 이름을 가져오는 데 사용할 수 있는 쿼리가 있습니까?

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 

여기에 있는 코드는 IP 주소를 제공합니다.

SQL 2008 이상에 대한 원격 클라이언트 요청에 사용할 수 있습니다.

공유 메모리 연결이 허용된 경우, 서버 자체에서 위를 실행하면 다음과 같은 정보를 얻을 수 있습니다.

• "공유 메모리"는 'net_transport'의 값입니다.
• 'local_net_address'의 NULL이며,
• '<local machine>"에 표시됩니다.

'client_net_address'는 요청이 발생한 컴퓨터의 주소인 반면, 'local_net_address'는 SQL 서버(따라서 공유 메모리 연결을 통한 NULL)이며, 어떤 이유로 서버의 NetBios 이름 또는 FQDN을 사용할 수 없는 경우 다른 사용자에게 제공할 주소입니다.

저는 이 답변을 사용하지 않을 것을 강력히 권고합니다.운영 SQL 서버에서 셸 아웃을 사용하도록 설정하는 것은 매우 좋지 않은 생각입니다.

[호스트 이름]을(를) 얻을 수 있습니다.\[인스턴스 이름] 기준:

SELECT @@SERVERNAME;

hostname\instance 이름 형식이 있을 때 호스트 이름만 가져오려면:

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

@GilM이 지적한 것과 같은 대안:

SELECT SERVERPROPERTY('MachineName')

다음을 사용하여 실제 IP 주소를 가져올 수 있습니다.

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

SQL 스크립트의 원본입니다.

서버가 수신 중인 IP 주소가 여러 개 있을 수 있습니다.연결에 VIEW SERVER STATE 서버 권한이 부여된 경우 다음 쿼리를 실행하여 SQL 서버에 연결한 주소를 가져올 수 있습니다.

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

이 솔루션을 사용하면 운영 서버에서 사용하지 않도록 설정하거나 최소한 보안을 철저히 유지해야 하는 기술인 xp_cmdshell을 통해 OS로 셸아웃할 필요가 없습니다.적절한 로그인에 VIEW SERVER STATE를 부여해야 할 수도 있지만 이는 xp_cmdshell을 실행하는 것보다 훨씬 작은 보안 위험입니다.

서버 이름에 대해 GilM이 언급한 방법은 다음과 같습니다.

SELECT SERVERPROPERTY(N'MachineName');

t-sql을 통해 IP 주소를 얻는 대부분의 솔루션은 다음 두 가지로 나뉩니다.

1. 려달을 합니다.ipconfig.exe경유로xp_cmdshell그리고 출력을 구문 분석합니다.

2. DMV 쿼리 »sys.dm_exec_connections

저는 1번 옵션의 팬이 아닙니다.xp_cmdshell을 활성화하면 보안상의 단점이 있으며, 어쨌든 많은 구문 분석이 수반됩니다.거추장스럽군요.옵션 #2는 우아합니다.제가 거의 항상 선호하는 순수한 t-sql 솔루션입니다.다음은 옵션 #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;

그러나 때때로 위의 쿼리 중 어느 것도 작동하지 않습니다.공유 메모리를 통해 연결된 경우(로그인하여 SQL 호스트에서 SSMS를 실행 중인 경우) 쿼리 #1에서 NULL을 반환합니다.비공유 메모리 프로토콜을 사용하는 연결이 없는 경우 쿼리 #2에서 아무것도 반환하지 않을 수 있습니다.이 시나리오는 새로 설치된 SQL 인스턴스에 연결된 경우에 발생할 수 있습니다.해결책?TCP/IP를 통해 강제로 연결합니다.이렇게 하려면 SSMS에 새 연결을 만들고 서버 이름과 함께 "tcp:" 접두사를 사용합니다.그런 다음 두 쿼리 중 하나를 다시 실행하면 IP 주소가 표시됩니다.

--이 스크립트를 사용해 보십시오. 필요에 맞게 작동합니다.다시 포맷하여 읽으십시오.

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;

@@SERVERNAME 변수에 있습니다.

SELECT @@SERVERNAME;

다음 쿼리를 사용하십시오.

SELECT CONNECTIONPROPERTY('local_net_address') AS [IP]

select @@servername

명령줄 쿼리를 사용하여 mssql에서 실행할 수 있습니다.

exec xp_cmdshell 'ipconfig'

\InstanceName 없이 시스템 이름을 가져오는 더 간단한 방법은 다음과 같습니다.

SELECT SERVERPROPERTY('MachineName')

이전 게시물인 것은 알지만 공유 메모리 연결(예: 서버의 SSMS에서 로컬로 실행되는 스크립트)에서 IP 주소와 TCP 포트를 검색하려는 경우 이 솔루션이 유용할 수 있습니다.핵심은 OPENROWSET을 사용하여 SQL Server에 대한 보조 연결을 여는 것입니다. 이때 연결 문자열에 'tcp:'를 지정합니다.나머지 코드는 변수를 매개 변수로 사용할 수 없다는 OPENROWSET의 한계를 극복하기 위해 동적 SQL을 구축하는 것입니다.

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

host_name() 함수를 사용할 수 있습니다.

select HOST_NAME()

SQL 서버 인스턴스가 로컬에 있는 경우 다음 프로토콜이 있는지 확인합니다.

공유 메모리 프로토콜을 활성화한 경우:

SELECT ConnectionProperty('local_net_address') AS 'Server IP Address'

NULL을 반환합니다.

SELECT ConnectionProperty('client_net_address') AS 'Client IP Address'

< 로컬 시스템 >을 반환

공유 메모리를 사용하지 않도록 설정하고 TCP/IP를 사용하도록 설정하면 해당 명령이 올바른 IP 주소를 반환합니다.

언급URL : https://stackoverflow.com/questions/142142/sql-query-to-get-servers-ip-address