| Author |
Topic |
|
jzachary
Starting Member
9 Posts |
 Posted - 2002-12-05 : 10:41:54
|
| I had a system DSN setup on a win98 workstation that was connecting to a SQL7 server dbase (thru a firewall) and it was working fine until the workstation was upgraded from win98 to 2000pro. Now, I can't connect to the SQL and have received the following errors when trying to test the connection:[url]www.ziibs.com/resources/issues/faileddsntosqlserver.pdf[/url]Any suggestions or help would be appreciated. I've confirmed (I think) that the DNS is working (ie. it's finding the hosts file entry properly) so it's able to resolve to get to the sql server, but it just won't establish the connection (oops, I'm whining). Thanks in advance!Jai |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-12-05 : 15:15:51
|
| One of the first things I would check is the driver version number for your SQL Server in the ODBC connections window. We had some problems when a new OS was installed (which updated the MDAC) but then an older version of Access was installed (it semi-rolled back the MDAC) and really messed stuff up. You may need to re-update the MDAC ([url]http://www.microsoft.com/data[/url]). Also verify that when you run the MDAC update it actually goes into the right directory. I've had situations where there were 2 copies of the driver, one in Windows/System and the other in Windows/System32 and had to copy the new one into the other directory.Also, there is a difference in your error message, one is trying Named Pipes, the other is trying TCP/IP. I encourage you to use TCP/IP and I think you can explicitly specify that in the DSN definition (when you're on the updated version).BTW, MDAC 2.7 is for XP, it might not work on Win2K. I think you'll want 2.5, but read to be sure.------------------------------------------------------The more you know, the more you know you don't know. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2002-12-05 : 15:38:39
|
| From what I can tell from your error messages, you are not getting through the firewall. I would talk with your firewall administrator and have that person look at the firewall logs and search for your machine. They might have to define a firewall rule for you. |
|
|
|
jzachary
Starting Member
9 Posts |
Posted - 2002-12-09 : 13:45:16
|
| I've done some more digging as per your suggestions, and I think I've found a lead on this problem and it's in the Event Viewer. The firewall is fine as it hasn't changed (I think) since this problem and was working thru the firewall before the 2000pro OS upgrade. User also has installed every SP and update they could find. You can download the zip file which contains the Event Viewer logs for this user/workstation and you'll see there are numerous errors relating to the MDAC and networking 'stuff' - [url]http://www.ziibs.com/resources/issues/evntvewrwin98-2kupg.zip[/url] and I hope you gurus out there can point this young grasshopper to the solution. User has also reported having some issues upgrading the OS and these are also identifiable in the logs. Thanks in advance!Jai |
|
|
|
Argyle
Yak Posting Veteran
53 Posts |
Posted - 2002-12-09 : 18:49:39
|
| Try and ping the sql server by name from the workstation and vice versa the workstation from the sql server. Does it work in both directions? If not your firewall is blocking traffic or you have incorrect DNS or WINS address under the network properties or wrong entries in your hosts file. Fix that and you should be ok.Also run Start Menu -> Run -> cliconfg.exe and change the default network library to TCP/IP instead of Named Pipes.The errors in your event log seems to be standard perflib errors related to the Performance Counters in Windows. Nothing critical. They sometimes get messed up during upgrades and such. On how to fix them check:http://support.microsoft.com/default.aspx?scid=kb;EN-US;300956/Argyle |
|
|
|
jzachary
Starting Member
9 Posts |
Posted - 2002-12-11 : 18:27:16
|
| Thanks for taking a look at those logs - your comments are great. I think I should explain the environment a little more...this workstation is on another network connecting to the SQL server through the Internet (going out it's firewall, coming through firewall where sql server is - where I'm at). The firewall doesn't allow ping, but, in the hosts file on the workstation I've setup the IP and domain that it's using to talk to the sql server and when I try to ping it will resolve the domain name to IP, but ofcourse won't get a reply. Is there some other place where the system DSN will use for connecting other than the hosts file? Maybe there is something going on with the firewall at the time of the upgrade - huge coincidence but not out of realm of possibilities. I will double check and get back to everyone. Thanks!Jai |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2002-12-11 : 19:40:49
|
| Is your SQL Server listening on 1433? I would hope not since it is being accessed via the Internet. If it isn't listening on 1433, you will need to let the client know what port to use. If the SQL Client tools are installed on the client machine, then you can specify it there. If they aren't, then you will have to add a string value to the registry.Here is an example:[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo]"DBSERVER1"="DBMSSOCN,DBSERVER1,12000""DBSERVER1" is either an alias to the server or the actual server name. It has to match what your code is calling. DBMSSOCN is a dll to talk on TCP/IP. The next DBSERVER1 is either the server name or the IP address. 12000 is the port (I just picked any port).If your server is listening on 1433, then forget what I am talking about above. But I would bet that your server gets hacked into. |
|
|
|
jzachary
Starting Member
9 Posts |
Posted - 2002-12-12 : 13:44:45
|
| Thanks for the heads up...I'm looking into what you've suggested. As far as exposing the SQL server to Internet, I'm running internal DNS and ISA firewall with client sets (meaning, only answer to the IP of the workstations' network) to reduce hacking. Nothing is bullet proof, but I'm also going to change the port the SQL server is using, just to make sure. I'm also going to try and replicate this DSN issue on a win98 box to see if the 2000 OS is the issue rather than the connection to the SQL server. I suspect there's some sort of authentication going on that I'm missing that's preventing communication....anyways, the saga continues. I'll let you (and the audience) know how it goes. Thanks!Jai |
|
|
|
Argyle
Yak Posting Veteran
53 Posts |
Posted - 2002-12-13 : 07:35:21
|
| If you can't ping then try telnet instead.Say your sql server name is MYSERVER and you use port 1433 then start a command prompt on the workstation and type:telnet MYSERVER 1433If you get:"Could not open a connection to host on port 1433 : Connect failed"then name resolution doesn't work or traffic is blocked in the firewall. To check if name resolution is the problem test with the IP address instead of the name. If your IP is 192.168.1.1 (just an example) then type:telnet 192.168.1.1 1433If you get one of them working next step is to do an odbcping to see if you get an error from the sql server. You find odbcping.exe in the binn directory on the SQL 2000 CD. It's installed already with SQL Server 7.0.Then open a command prompt and type (with your own user/password):odbcping -S MYSERVER -u myuser -p mypasswordIf that works then test the DSN with:odbcping -D MYDSN -u myuser -p mypasswordWhen they all work your app should work as well. In any case, your network library needs to be TCP/IP./Argyle |
|
|
|
|
DSN export to SQL7 now failing