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'에 표시됩니다.

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

이 답변 을 사용 하지 않는 것이 좋습니다. 셸 아웃을 활성화하는 것은 프로덕션 SQL Server에서 매우 나쁜 생각입니다.

---

다음을 통해 [hostname] \ [instancename]을 가져올 수 있습니다.

SELECT @@SERVERNAME;

호스트 이름 \ 인스턴스 이름 형식이있는 경우 호스트 이름 만 가져 오려면 :

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 Server에 연결 한 주소를 가져올 수 있습니다.

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

This solution does not require you to shell out to the OS via xp_cmdshell, which is a technique that should be disabled (or at least strictly secured) on a production server. It may require you to grant VIEW SERVER STATE to the appropriate login, but that is a far smaller security risk than running xp_cmdshell.

The technique mentioned by GilM for the server name is the preferred one:

SELECT SERVERPROPERTY(N'MachineName');

---

Most solutions for getting the IP address via t-sql fall into these two camps:

1. Run ipconfig.exe via xp_cmdshell and parse the output

2. Query DMV sys.dm_exec_connections

I'm not a fan of option #1. Enabling xp_cmdshell has security drawbacks, and there's lots of parsing involved anyway. That's cumbersome. Option #2 is elegant. And it's a pure t-sql solution, which I almost always prefer. Here are two sample queries for option #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;

Sometimes, neither of the above queries works, though. Query #1 returns NULL if you're connected over Shared Memory (logged in and running SSMS on the SQL host). Query #2 may return nothing if there are no connections using a non-Shared Memory protocol. This scenario is likely when connected to a newly installed SQL instance. The solution? Force a connection over TCP/IP. To do this, create a new connection in SSMS and use the "tcp:" prefix with the server name. Then re-run either query and you'll get the IP address.

---

It's in the @@SERVERNAME variable;

SELECT @@SERVERNAME;

---

you can use command line query and execute in mssql:

exec xp_cmdshell 'ipconfig'

---

--Try this script it works to my needs. Reformat to read it.

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;

---

select @@servername

---

A simpler way to get the machine name without the \InstanceName is:

SELECT SERVERPROPERTY('MachineName')

---

I know this is an old post, but perhaps this solution can be usefull when you want to retrieve the IP address and TCP port from a Shared Memory connection (e.g. from a script run in SSMS locally on the server). The key is to open a secondary connection to your SQL Server using OPENROWSET, in which you specify 'tcp:' in your connection string. The rest of the code is merely building dynamic SQL to get around OPENROWSET’s limitation of not being able to take variables as its parameters.

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

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