SQL Study Notes: server configuration-how to access SQL2000 through the Internet

I just wrote a demo, and it was completely normal after I tested SQL2000 on the Intranet. Now I want to test whether the Internet is normal. After all, the router IP address is not fixed, so I chose the router + free peanut shell domain name (koma.5166.info), So I first installed the peanut shell client software. The following are some of my problems in this process:

 

1. Theoretically, port 1433 is mapped out (this port is the default port of the SQL2000 server)

 

The results showed that the program and SQL query analyzer could not connect to the server. At this time, I ruled out that Windows Firewall, rising firewall and anti-virus software cannot be connected.

 

As a result, there are too many online materials, and too many parameters and settings are involved. It is estimated that most people prefer to repost the materials, but they do not know whether they have practiced the materials themselves. The result is still unsuccessful!

 

2. SQL2000 SP4 patch

 

This is the most common method seen in many places. After downloading and installing this patch, we found that the demo program and the SQL query analyzer still cannot be correctly connected, so we checked the port listening status, enter netstat-AN IN THE COMMAND LINE

 

C: /Documents and Settings/Koma> netstat-An </P> <p> active connections </P> <p> proto local address foreign address State <br/> TCP 0.0.0.0: 135 0.0.0.0: 0 listening <br/> TCP 0.0.0.0: 445 0.0.0.0: 0 listening <br/> TCP 0.0.0.0: 1433 0.0.0.0: 0 listening <br/> TCP 0.0.0.0: 6059 0.0.0.0: 0 listening <br/> TCP 127.0.0.1: 1026 0.0.0.0: 0 listening <br/> TCP 127.0.0.1: 1036 127.0.0.1: 1025 close_wait <br/> TCP 127.0.0.1: 1038 127.0.0.1: 1025 close_wait <br/> TCP 192.168.1.100: 139 0.0.0.0: 0 listening <br/> UDP 0.0.0.0: 445 *: * <br/> UDP 0.0.0.0: 1434 *: * <br/> UDP 127.0.0.1: 123 *: * <br/> UDP 127.0.0.1: 1049 *: * <br/> UDP 127.0.0.1: 1239 *: * <br/> UDP 127.0.0.1: 1900 *: * <br/> UDP 192.168.1.100: 123 *: * <br/> UDP 192.168.1.100: 137 *: * <br/> UDP 192.168.1.100: 138 *: * <br/> UDP 192.168.1.100: 1900 *: * </P> <p> C:/Documents and Settings/Koma>

 

In this case, I shut down all the unrelated applications, and then all the listening ports above were hidden, and finally locked in two ports: 1433 and 445.

 

3. Demo code

 

A. Code for establishing a connection:

Bool cqdlg: oninitdialog () <br/>{< br/> ...... </P> <p> // set the icon for this dialog. the framework does this automatically <br/> // when the application's main window is not a dialog <br/> seticon (m_hicon, true ); // set big icon <br/> seticon (m_hicon, false); // set small icon </P> <p> // todo: add extra initialization here <br/> // initialize the COM interface <br/> If (! Afxoleinit () <br/>{< br/> afxmessagebox ("failed to initialize COM interface"); <br/>}< br/> hresult hr; <br/> try <br/> {<br/> // create a connection object <br/> hR = m_pconnection.createinstance ("ADODB. connection "); <br/> If (succeeded (HR )) <br/> {<br/> // database connected <br/> hR = m_pconnection-> open ("provider = sqloledb; server = koma.5166.info, 1433; database = student; uid = sa; Pwd = "," "," ", admodeunknown); <br/> // MessageBox (" database connection successful! "); <Br/>}< br/> catch (_ com_error E) <br/>{< br/> // catch exceptions <br/> cstring errormessage; <br/> errormessage. format ("failed to connect to the database! /R/n error message: % s ", E. errormessage (); <br/> afxmessagebox (errormessage); // display the error message <br/>}< br/> return true; <br/>}

 

B. program running effect:

 

 

C. SQL query Analyzer:

 

4. Summary

 

The MS SQL2000 "surface" looks like only port 1433 is listening, but there is actually a port 445 is also listening, both indispensable. Therefore, the SP4 patch must be installed to allow the Internet host to access the Intranet SQL2000 server (back up important data before patching ), then, map ports 1433 and 445 from the NAT device.

 

As to why we need to listen to these two ports at the same time, which of your friends knows the trouble to tell me, thank you!