How to get IP Address for HTTP/S connection

[Baron]

I run my database with:

-x tcpip -xs http(port=88888)

I need to know (from inside DB) on which socket (IP+Port) others can connect to me.

Till now I used always this one: select value from sa_eng_properties() where propname = 'HttpAddresses'

But this query does not work always! In some installations the value is empty (the database is accessible on the socket, but I can inquire the socket).

Is there other reliable way to get this socket?

[Baron · Accepted solution]

As sa_eng_properties() does not always give the correct IP (on which the webservices of the database can be called), so the only way is to read the environment variable %COMPUTERNAME%

select xp_getenv('COMPUTERNAME')

[johnsmirnios]

Is it possible that those installations are using -xs https(port=88888) (note the "https" rather than "http")? In that case I think you want HttpsAddresses.

Also check that the server didn't fail to listen on that port at startup. I think the server would fail to start if it couldn't listen at that port but I'm not certain.

BTW, it's would be more efficient (if you care) to select property( 'HttpAddresses' ) rather than having sp_eng_properties() evaluate a ton of properties just to use one of them :).

One other thing comes to mind: is your comparison always against 'HttpAddresses' exactly and never, say, 'httpaddresses'? If you don't match exactly on a case-sensitive database then you won't find the row. Selecting the property() directly wouldn't have that problem.

[Baron]

Actually I check both.

select value from sa_eng_properties() where propname in ('HttpAddresses', 'HttpsAddresses')

Otherwise I start the databases always with http (not https).

The only difference (on the installation which not works) is that on this machine more than one Database is running! Could this be a problem?

[johnsmirnios]

http/https listeners are server-level as are the properties returned by sa_eng_properties so it shouldn't matter that there are multiple databases. Have you tried multiple databases yourself and found it 100% reproducible?

[VolkerBarth]

What does "SELECT * from sp_http_listeners()" reveal? (Note, you can add a database ID as parameter.)

[johnsmirnios]

By my reading of the code, ports are "associated" with a database if the DBN= parameter is provided in the -xs http(....DBN=...).

I don't see any way that a listener wouldn't be listed via property('HttpAddresses').

[VolkerBarth]

Of course you got the code. 🙂 I just wanted to point to an alternative procedure to find out about listeners in V17, which would not have the possible case issue in constrast to sa_eng_properties() and a specified condition (as you mentioned)...

[Baron]

this statement delivers the same result on both (working and not working machines).

ip_address;port;dbname;uri_prefix

'0.0.0.0';88888;;http://0.0.0.0:88888/DB1/

'::';88888;;http://[::]:88888/DB1/

[johnsmirnios]

For sure! It's a great idea. I'm just saying I'm perplexed that property( 'HttpAddresses' ) returned nothing -- even more so now that I see a result being returned from sp_http_listeners.

[johnsmirnios]

To get all listeners when you have multiple dbs, you might try something like the following

select * from dbo.sa_db_list( NULL ) cross apply dbo.sp_http_listeners( Number )

[Baron]

The above statement delivers the uri (for all running databases), however the IP Address is always 0.0.0.0

[VolkerBarth]

The properties related to HPPT/HTTPS/TCPIP "...Addresses" and "...Listeners" seem to work differently here (with a 17.0.11.7432 setup): "...Addresses" does list the IP addresses, too, whereas "...Listeners" seem to list the ports on "all" available IP addresses. Here for a local TCP/IP server:

select @@version, property('TcpIpAddresses'), property('TcpIpListeners');
-- returns
17.0.11.7432; 192.168.101.42:2638; (::):2638;0.0.0.0:2638

So you might need to join with the ...Addresses property to get IP addresses AND ports - but then you are back to the original question. 😕

[Baron]

For some reason, the statement in my original question started to work on the other machine too (on which several databases are running).

select value from sa_eng_properties() where propname = 'HttpAddresses'

Nothing was changed on this machine (except the test/diagnostic statements from the above comments).

For this reason, I find it is safer to go with %COMPUTERNAME%