Incorrect/corrupted value in Client PID / HOST_ID in Login7 packet?

[reedsemmel]

Hello,

I believe I have found an issue with the protocol implementation during login.

I found when querying SELECT HOST_ID(), I get a mangled value. This value should be the pid of the client program. I have no familiarity with TDS until I found out about this and did some digging today.

Steps to reproduce

Install SQL Server. Enable TCP, Enable SQL Server auth, add a test login.

In iex:

iex(1)> Mix.install([:tds])
:ok
iex(2)> Application.spec(:tds, :vsn)
~c"2.3.6"
iex(3)> {:ok, conn} = Tds.start_link([hostname: "localhost", username: "test", password: "test", database: "master", ssl: :not_supported, port: 1433])
{:ok, #PID<0.197.0>}
iex(4)> Tds.query!(conn, "SELECT HOST_ID()", [])
%Tds.Result{columns: [""], rows: [["-956301312"]], num_rows: 1}

The code in lib/tds/protocol/login7.ex suggests that it intends to have the middle number of the erlang PID be the client PID in the TDS protocol. (In the example above, 197)

If I inspect this in Wireshark, I can see this in the login7 packet:

Sure enough, those two numbers are encoded into the same DWORD

iex(9)> <<3338665984::little-32>>
<<0, 0, 0, 199>>
iex(10)> <<-956301312::little-32>>
<<0, 0, 0, 199>>

# 197 for reference
iex(8)> <<197::little-32>>
<<197, 0, 0, 0>>

---

Additionally, I'm not sure if it is better to have the erlang PID or OS PID for the erlang runtime for this field. If I run SELECT HOST_ID() from SSMS, I get the same PID for ssms.exe in task manager.

More info:

I ran this on Windows 11. Erlang/OTP 28 [erts-16.1.1] [source] [64-bit] [smp:24:24] [ds:24:24:10] [async-threads:1] [jit:ns] Interactive Elixir (1.19.2)

SQL Server information

[reedsemmel]

@mjaric could you or someone review my PR #181 for this? The issue was a simple big vs little endian encoding.

I found this because it broke one of our processes that used HOST_ID() in a char(8) column instead of char(10)

[mjaric]

I will

[reedsemmel]

Thank you!!

[mjaric]

released in v2.3.7

@reedsemmel , thank you!!!