4

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.

1
  • All you should need is TCP\1433. Nothing is should be required. What's the exact error you are getting?
    – mrdenny
    Mar 10, 2010 at 5:06

4 Answers 4

4

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.

6
  • Marking as accepted because it answers the question: I don't need any other ports. Of course, my problem still exists (despite using the exact procedure described), but that's a different question. Mar 10, 2010 at 20:24
  • The link has also a small paragraph about configuring your Windows box and SQL Server, where it says 'The telltale sign being user '(null)'. Make sure your read that too. Mar 11, 2010 at 0:03
  • Yes, thanks, we did try that already without success. Mar 11, 2010 at 1:31
  • Try this document too: microsoft.com/downloads/… it gives details about troubleshooting NTLM/Kerberos issues. You can apply it to the Windows half of the handshake and perhaps it will reveal why the authentication fails. Mar 11, 2010 at 7:18
  • @Remus: That document describes Kerberos authentication, not NTLM. It only talks about NTLM in the context of "How do I know if Kerberos is failing and falling back to NTLM, and how do I prevent that?" Mar 11, 2010 at 13:52
0

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

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?

2
  • Thanks, but I've already tried that. Both, actually. I tried the 0.83 current release, which has the NTLMv2 patch applied, no go. I also had the admin change the policy shown in the screenshot (I am the only client connecting to this machine), but still no joy (with 0.82 or 0.83). Mar 11, 2010 at 1:29
  • try disabling SMB signing as well Mar 11, 2010 at 1:43
0

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .