December 29, 2008 at 12:28 pm
ISSUE: Cannot connect to newly installed SQL 2008 server via ODBC from clients (web servers) in the DMZ.
SQL Server Specs:
SQL Server 2008 Standard
Windows Server 2003 R2 Standard SP2 (64-bit)
All Windows Server patches installed (Including IE7)
Configured for SQL Server and Windows Authentication modes
Instance ID: SQL2008
Error received on Clients:
Microsoft SQL Server Login
Connection failed:
SQLState: '01000'
SQL Server Error: 1326
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
Troubleshooting steps:
-Clients on the same subnet as SQL 2008 server can connect
-The same clients (in the DMZ) can connect to a different server running SQL 2005
-Created rules on the firewall between the clients and SQL server to allow for all traffic to be allowed
-SQL Server Browser service is running
-Can ping the SQL server from clients
-In Microsoft SQL Server Management Studio -> Server Properties -> Connections ; placed checkmark in “Allow remote connections to this server” and “Remote query timeout” set to 0
-In SQL Server Configuration Manager -> SQL Server Network Configuration -> Protocols for SQL2008 ; Shared Memory, Named Pipes and TCP/IP are enabled.
Steps to create the issue:
-Launch “Data Sources (ODBC)”
-Choose “System DSN” tab
-Choose “Add”
-Select “SQL Server”
-Name field: ServerName
-Server: ServerName/SQL2008 (Also tried IP Address/SQL2008 and ServerName.domain.com/SQL2008)
-Select radial button “With SQL Server authentication using a login ID and password entered by user.”
-Add checkmark to “Connect to SQL Server to obtain default settings for the additional configuration options.”
-Login ID: sa
-Password: ********
-15 seconds later I receive the above error message
Here is the output from TCPView on the SQL 2008 server:
svchost.exe:816TCPServerName:epmapServerName:0LISTENING
System:4TCPServerName:microsoft-dsServerName:0LISTENING
lsass.exe:564TCPServerName:1025ServerName:0LISTENING
sqlservr.exe:1512TCPServerName:1492ServerName:0LISTENING
svchost.exe:3212TCPServerName:ms-wbt-serverServerName:0LISTENING
sqlservr.exe:1512TCPServerName:5022ServerName:0LISTENING
beremote.exe:1808TCPServerName:10000ServerName:0LISTENING
sqlservr.exe:1512TCPServerName:1493ServerName:0LISTENING
hpsmhd.exe:1876TCPServerName:2301ServerName:0LISTENING
hpsmhd.exe:1876TCPServerName:2381ServerName:0LISTENING
System:4TCPServerName.domain.com:netbios-ssnServerName:0LISTENING
hpsmhd.exe:1876TCPServerName.domain.com:2301ServerName:0LISTENING
hpsmhd.exe:1876TCPServerName.domain.com:2381ServerName:0LISTENING
svchost.exe:816TCPServerName.domain.com:epmapServerName.domain.com:1075ESTABLISHED
mshta.exe:4004TCPServerName.domain.com:1075ServerName.domain.com:epmapESTABLISHED
lsass.exe:564TCPServerName.domain.com:2102DomainController.domain.com:5000ESTABLISHED
svchost.exe:3212TCPServerName.domain.com:ms-wbt-serverClientComputer.domain.com:2713ESTABLISHED
[System Process]:0TCPServerName.domain.com:2104DomainController.domain.com:netbios-ssnTIME_WAIT
[System Process]:0TCPServerName.domain.com:2105DomainController.domain.com:netbios-ssnTIME_WAIT
snmp.exe:1564UDPServerName:snmp*:*
lsass.exe:564UDPServerName:isakmp*:*
lsass.exe:564UDPServerName:ipsec-msft*:*
sqlbrowser.exe:1596UDPServerName:ms-sql-m*:*
System:4UDPServerName:microsoft-ds*:*
snmp.exe:1564UDPServerName:1047*:*
winlogon.exe:504UDPServerName:1052*:*
lsass.exe:564UDPServerName:1026*:*
svchost.exe:916UDPServerName:ntp*:*
System:4UDPServerName.domain.com:netbios-dgm*:*
System:4UDPServerName.domain.com:netbios-ns*:*
svchost.exe:916UDPServerName.domain.com:ntp*:*
Your assistance is greatly appreciated.
Jason
December 29, 2008 at 12:52 pm
Completely opening the firewall is dangerous you could potentially be at risk from a security standpoint.
Have you tried changing the 2008 instance to listen on 1433 instead of a dynamic port and see if that makes a difference? Maybe the firewall will allow SQL traffic, but only on certain ports.
December 29, 2008 at 1:52 pm
Nicholas Cain (12/29/2008)
Completely opening the firewall is dangerous you could potentially be at risk from a security standpoint.
Agreed. 😉 I have only opened the traffic on the firewall from the client in the dmz to the sql server for testing purposes. And it is only between those two newly built computers. I'll tighten it up when this issue is resolved.
Nicholas Cain (12/29/2008)
Have you tried changing the 2008 instance to listen on 1433 instead of a dynamic port and see if that makes a difference? Maybe the firewall will allow SQL traffic, but only on certain ports.
Excellent. It does work if I change the 2008 instance to use 1433. Does that give a clue as to what could keep the dynamic ports from working?
I have another SQL server running 2005 that is using dynamic ports. And the clients in the DMZ can connect to it.
December 29, 2008 at 2:15 pm
Did you check what ports that the 2005 server was listening on? Check and see if there are rules allowing traffic through on those particular ports.
You might also want to change the port that SQL listens on, set it to a hard value, and change the open port on the firewall, it's a little more secure that way and the SQL Slammer learned me some good stuff like not leaving 1433 open that way.
December 29, 2008 at 2:24 pm
Nicholas Cain (12/29/2008)
Did you check what ports that the 2005 server was listening on? Check and see if there are rules allowing traffic through on those particular ports.You might also want to change the port that SQL listens on, set it to a hard value, and change the open port on the firewall, it's a little more secure that way and the SQL Slammer learned me some good stuff like not leaving 1433 open that way.
I definitely appreciate the advice and I just might decide to specify a port for it to listen on, seeing that it’s more secure. But seeing that I just built this SQL Server I’d love to get this issue resolved before I consider it in production. I’d hate for this to be a sign that there is something wrong with the installation. Or if it's a configuration issue, I would like to learn what is different in 2005 and 2008 that needs to be considered.
The firewall is completely open (for now) between the client and the SQL Server. Any more ideas why dynamic ports aren’t working?
Thanks
December 29, 2008 at 2:38 pm
UDP appears to be listening on 1596 (correct me if I am reading this wrong)
sqlbrowser.exe:1596 UDP
This should be listening on UDP: 1434.
Check it on the 2005 box.
However if you don't have any issues connecting on the 2005 box from the DMZ and are using dynamic ports I don't think that your firewall is working correctly. If the port were dynamic then you would have to have a large range of TCP ports open to ensure that the connection would work. Might be worth checking the 2005 box, see what SQL is listening on there, and figure out how your rules might be allowing that dynamic traffic.
December 29, 2008 at 2:43 pm
Also check the "HideInstance" option within the network configuration stuff, that prevents SQL Browser from returning any information about the ports that SQL is listening on.
December 29, 2008 at 3:00 pm
Nicholas Cain (12/29/2008)
UDP appears to be listening on 1596 (correct me if I am reading this wrong)sqlbrowser.exe:1596 UDP
This should be listening on UDP: 1434.
Check it on the 2005 box.
However if you don't have any issues connecting on the 2005 box from the DMZ and are using dynamic ports I don't think that your firewall is working correctly. If the port were dynamic then you would have to have a large range of TCP ports open to ensure that the connection would work. Might be worth checking the 2005 box, see what SQL is listening on there, and figure out how your rules might be allowing that dynamic traffic.
Good point about the dynamic ports. So my fix is that I should go away from dynamic ports and take a close look at what my SQL 2005 server is doing. Is there a recommended range that I should choose from for my static port?
As for the service listening on 1596; That was showing the PID of the process. Here's an easier to read export of TCPView. The number immediately after the service is the PID. The Port is after the Ip address.
beremote.exe:1848TCP0.0.0.0:100000.0.0.0:0LISTENING
hpsmhd.exe:1880TCP127.0.0.1:23010.0.0.0:0LISTENING
hpsmhd.exe:1880TCP127.0.0.1:23810.0.0.0:0LISTENING
hpsmhd.exe:1880TCP192.168.15.249:23010.0.0.0:0LISTENING
hpsmhd.exe:1880TCP192.168.15.249:23810.0.0.0:0LISTENING
lsass.exe:564TCP0.0.0.0:10250.0.0.0:0LISTENING
lsass.exe:564UDP0.0.0.0:500*:*
lsass.exe:564UDP0.0.0.0:4500*:*
lsass.exe:564UDP127.0.0.1:1026*:*
snmp.exe:1564UDP0.0.0.0:161*:*
snmp.exe:1564UDP0.0.0.0:1047*:*
sqlbrowser.exe:1620UDP0.0.0.0:1434*:*
sqlservr.exe:4432TCP0.0.0.0:14330.0.0.0:0LISTENING
sqlservr.exe:4432TCP0.0.0.0:50220.0.0.0:0LISTENING
sqlservr.exe:4432TCP127.0.0.1:14930.0.0.0:0LISTENING
sqlservr.exe:4432TCP192.168.15.249:143310.10.10.111:11661ESTABLISHED
svchost.exe:3204TCP0.0.0.0:33890.0.0.0:0LISTENING
svchost.exe:3204TCP192.168.15.249:3389192.168.15.121:3920ESTABLISHED
svchost.exe:812TCP0.0.0.0:1350.0.0.0:0LISTENING
svchost.exe:912UDP127.0.0.1:123*:*
svchost.exe:912UDP192.168.15.249:123*:*
System:4TCP0.0.0.0:4450.0.0.0:0LISTENING
System:4TCP192.168.15.249:1390.0.0.0:0LISTENING
System:4UDP0.0.0.0:445*:*
System:4UDP192.168.15.249:138*:*
System:4UDP192.168.15.249:137*:*
winlogon.exe:504UDP127.0.0.1:1051*:*
🙂
December 29, 2008 at 6:52 pm
See, if only I could read logs.... :blink:
I would definitely check the 2005 instance, sounds like there might be an issue there.
If you are going to set static ports it would also be worthwhile blocking UDP 1434 so that the browser cannot be accessed either.
December 30, 2008 at 3:40 pm
Nicholas Cain (12/29/2008)
See, if only I could read logs.... :blink:I would definitely check the 2005 instance, sounds like there might be an issue there.
If you are going to set static ports it would also be worthwhile blocking UDP 1434 so that the browser cannot be accessed either.
It's working well now. Thanks for the help 🙂
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply