5

Is there anyway to see which address connections are connecting on?

More info:

I've repeatedly found people setting up connectionstrings using IP instead of by DNS name, and with SQL2012 and above, connecting to instances by server name without including the instance name (i.e. where this is on port 1433). I'd like to audit this so that within hours of something having connected using a non-compliant connection string, I can be alerted and so quickly find the culprit and fix the issue; as opposed to waiting for changes in infrastructure (e.g. us moving to a non-standard port, or switching to a different host on a different IP whilst maintaining the same underlying DNS entry) to cause more serious surprises as a result of non-compliant configurations.

Improve this question
7
  • 3
    I don't think SQL Server knows what IP/name was originally used by the client in order to connect. You would have to detect that at some lower layer.
    – Aaron Bertrand
    Commented Oct 4, 2016 at 15:57
  • Thanks @AaronBertrand; that's my suspicion, but hopefully we'll be contradicted...
    – JohnLBevan
    Commented Oct 4, 2016 at 16:12
  • 1
    I don't think that's what you're after. From the docs: "Represents the IP address on the server that this connection targeted. Available only for connections using the TCP transport provider." It won't tell you what name they used to get that IP resolved or whether they explicitly specified a port, and it will be null if they used named pipes to connect.
    – Aaron Bertrand
    Commented Oct 4, 2016 at 17:44
  • 1
    I've been messing around with WireShark trying to read the encrypted payload to see if the algo used maintains the same encrypted values based on what is used to connect in the string. I might have to use winpcap or something that really let's me look at the payload though, so far no luck.
    – Ali Razeghi - AWS
    Commented Oct 4, 2016 at 21:38
  • 1
    A suggestion for finding these would be to change the port on the next maintenance cycle, taking care to alert the relevant parties that the change was going to happen and that they should use the named instance. Then change it again on the following maintenance cycle and see who really listened. You could use some of the other suggestions to identify the various client machines who are connecting and attempt to specifically verify that they are connecting the way you want.
    – Jonathan Fite
    Commented Mar 13, 2017 at 17:13

3 Answers 3

Reset to default
7

Try this:

SELECT session_id, client_net_address
    FROM sys.dm_exec_connections
Improve this answer
1
  • 4
    Thanks @MSL. Sadly that gives the client machine's IP address, rather than information about the properties of the connection string (specifically the Server/Data Store properties) they have defined.
    – JohnLBevan
    Commented Oct 5, 2016 at 10:03
1

I think you are right, this is something that cannot be retrieved.

I also checked PowerShell and I couldn't find any solution.

Sorry for my last reply which was wrong. This is the most detailed info you can retrieve as long as I know: 

SELECT   
    c.session_id, c.net_transport, c.encrypt_option,   
    c.auth_scheme, s.host_name, s.program_name,   
    s.client_interface_name, s.login_name, s.nt_domain,   
    s.nt_user_name, s.original_login_name, c.connect_time,   
    s.login_time   
FROM sys.dm_exec_connections AS c  
JOIN sys.dm_exec_sessions AS s  
    ON c.session_id = s.session_id
Improve this answer
1
  • Thanks @FrancescoMantovani. Sadly this returns the client machine's hostname/ip address. Rather I'm after the server's information as used by the client; i.e. essentially a copy of the client's connection string.
    – JohnLBevan
    Commented Jul 12, 2018 at 8:00
0

Currently there is no way to do this.

However, MS are considering this as a feature suggestion: https://feedback.azure.com/forums/908035-sql-server/suggestions/32901958-view-client-connection-properties-from-the-server

If this is something you want; please upvote the above.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.