CODEWITHSUNDEEP
sqlsql-server
david blaine
david blaine

Reputation: 5927

SQL Server - How to get IP of your own server?

I saw that it is possible to connect to remote SQL servers by using their IP inside Manangement studio. Now I want to allow the database on my computer to be accessible remotely. How do I find out the IP of my own SQL server so that I can use that IP to login remotely ?

Upvotes: 3

Views: 17960

Answers (5)

Srihari
Srihari

Reputation: 31

DECLARE @IPAdress NVARCHAR(50)=''
SELECT @IPAdress = CASE WHEN dec.client_net_address = '<local machine>'
    THEN (SELECT TOP(1) c.local_net_address FROM
        sys.dm_exec_connections AS c WHERE c.local_net_address IS NOT NULL)
    ELSE dec.client_net_address
    END FROM sys.dm_exec_connections AS dec WHERE dec.session_id = @@SPID;
SELECT @IPAdress

Upvotes: 0

Devart
Devart

Reputation: 122032

Try this one -

SELECT 
      client_net_address = CASE WHEN client_net_address = '<local machine>' 
                                THEN '127.0.0.1' 
                                ELSE client_net_address 
                           END  
    , local_net_address = ISNULL(local_net_address, '127.0.0.1')
    , server_name = @@SERVERNAME
    , machine_name = SERVERPROPERTY('MachineName')
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;

Upvotes: 5

Captain Kenpachi
Captain Kenpachi

Reputation: 7215

You can Ping your computer name. do not ping "localhost" as that will give you 127.0.0.1. You can go "ping my-pc" and it will resolve your IP address through your DNS server.

Upvotes: 1

AnandPhadke
AnandPhadke

Reputation: 13506

Try this.

SELECT @@SERVERNAME;

Upvotes: 1

Brent
Brent

Reputation: 304

Visit this address to get your public IP address as Google sees it.

https://www.google.com/search?q=what+is+my+ip

Note: You may need to open up ports on your router and open up ports in your firewall to connect remotely. Additionally, your SQL server may be bound to only on your localhost 127.0.0.1 address. You may need to look up the instructions to accept connections on all of your IP addresses for your server.

Upvotes: 0

Related Questions