Home / server / Questions / 120983
Accepted
Adam Bellaire
Asked: 2010-03-10 19:03:36 +0800 CST

Which ports are needed for NTLM (Windows Authentication) to connect to SQL Server?

  • 772

I've got SQL server running on a machine which is not in a domain, and which is not operating in mixed mode (it's running with "Windows Authentication").

I'm trying to connect to it from a Linux web server running freetds via TCP/IP, using NTLM to authenticate.

The firewall on the SQL server is very restrictive. 1433 is open to my web server, but I'm getting conflicting information from the web on what additional ports (TCP/UDP) are needed for NTLM to succeed. It is currently fail; I can talk on 1433 to request NTLM, but the actual authentication always fails.

One source says 137, 138, 139, but those are just the NetBIOS ports. Do I really need those? Another source says 135. Still others seem to say 1434... I can't make heads or tails of it. Dammit Jim, I'm a programmer, not a network administrator!

EDIT:

The exact error message:

Msg 18452, Level 14, State 1, Server , Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
Msg 20002, Level 9, State -1, Server OpenClient, Line -1
Adaptive Server connection failed

I am attempting to connect with a remote machine username, i.e. 'servername\username'. Some sources recommend that I set up mirrored accounts on the local and remote machines, but the local machine is running Linux, not IIS under Windows.

sql-server ntlm freetds

4 Answers

  1. Best Answer

    The only port you need is 1433 as TCP. This is the port used by defaul, nonnamed SQL Server instances for TCP connections. FreeTDS will initiate a connection on this port and will then negotiate a NTLMv2 authentication on this connection, as a series of challenge/response packet exchanges. Afaik there is no need for any other port. See Domain Logins.

    All the other ports you mention are for Named Pipes connections, and FreeTDS does not support NT authentication over named pipes:

    Support for domain logins in FreeTDS is limited to the TCP/IP network protocol stack. FreeTDS does not currently implement support for Named Pipe-based SQL connections — that is, connections transported over the DCE/RPC interface, which uses TCP port 139, 445, or 135 on Win32 machines depending on the type of encapsulation used for DCE/RPC itself.

    To authenticate as an NT domain user you must specify an user name in the form 'domain\user'. If the SQL Server runs on a standalone computer, then 'domain' is the computer name.

    • 4

  2. I'm not sure that you will able to connect to this server if it's not in a domain and running in Windows Auth mode only. What username have you added to the server as a login and what user are you logged in as from the client machine?

    135-139 are the ports used for SMB (mostly, sometimes 445) and Windows RPC.

    1434 UDP would only be necessary if you are using the SQL Browser to connect to the instance, say in the case of a named instance (SERVERNAME\INSTANCE) but if you are using (SERVERNAME or SERVERNAME,PORT) and the instance is for sure running on 1433 then no additional ports would be required. You can test to see if the port is opened by issuing "telnet SERVERNAME PORT" from a command prompt.

    • 0

  3. I believe the NTLMv2 implementation in FreeTDS 0.82 is buggy at best. There is a patch here Your other option is to change the group policy on windows of the SQL Server to send NTLMv1 responses, as suggested in the documentation.

    Here's a screenshot of what you'd need to change on windows server 2003. You make be affecting other clients connecting to the server to make sure you can test this out somewhere first.

    EDIT: Have you tried to turn logging on & see if you get anything useful?

    • 0

  4. If the SQL server is colocated, contact your colocation provider to make sure their local firewall isnt blocking the port too.

    • 0