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.

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

4 Answers4

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.

Remus Rusanu
  • 8,253
  • 1
  • 19
  • 22
  • 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. – Adam Bellaire Mar 10 '10 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. – Remus Rusanu Mar 11 '10 at 00:03
  • Yes, thanks, we did try that already without success. – Adam Bellaire Mar 11 '10 at 01:31
  • Try this document too: http://www.microsoft.com/downloads/details.aspx?FamilyID=7DFEB015-6043-47DB-8238-DC7AF89C93F1&displaylang=en 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. – Remus Rusanu Mar 11 '10 at 07: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?" – Adam Bellaire Mar 11 '10 at 13:52
  • If connecting from a linux box your best bet is to use a SQL login for this. – Jason Cumberland Nov 06 '10 at 00:24
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.

Jason Cumberland
  • 1,559
  • 10
  • 13
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?

Nick Kavadias
  • 10,758
  • 7
  • 36
  • 47
  • 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). – Adam Bellaire Mar 11 '10 at 01:29
  • try disabling SMB signing as well – Nick Kavadias Mar 11 '10 at 01:43
0

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