• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 406
  • Last Modified:

sql server 8.0 - need to access the database remotely using ip address

Hi Experts,
I am working with sql server database and visual basic 6 and need to access the database remotely in my visual basic 6 application. Could I set the database so that I can reference it remotely using IP.
I want to then be able to create an ODBC dsn pointing to the db using its IP address so I can use the dsn in my VB6 application to connect to the db. I have seen this working in another application but dont know how. Please help with details.
0
uzairp
Asked:
uzairp
  • 3
  • 3
2 Solutions
 
batchakamalCommented:
1. Make sure that remote PC has SQL Client Utility installed and TCP/IP Protocol is enabled. Go to Start, Programs, MS SQL Server, SQL Server Client Network utility and check.
2. Go to the alias tab of SQL Server Client Network utility and add an alias for TCP/IP.
3. Then create the ODBC connection for sql server, using IP Address as the SQL Server name

I hope it will work.
Bye
0
 
JohnBPriceCommented:
batchakamal is right about having to use the TCP/IP client connection.  Furthermore, you must ensure there are no firewalls that will block the SQL connection, including Windows Firewall included in XP SP2.  By default SQL Server listens on port 1433, but that can be configured.  The firewall protecting the server must allow incoming connections, see http://msdn2.microsoft.com/en-us/ms175483.aspx

To configure Windows Firewall, see http://msdn2.microsoft.com/en-us/ms175043.aspx 
0
 
uzairpAuthor Commented:
I followed this procedure but I still cannot connect. Here is what I did;
1. I have sbc dsl service in my office and have a 2-wire router that came with it. The sql server is loaded on the computer named 'Main' in the LAN and is configured with an ip address that starts with 192 (it is not configured with its own public ip)
2. I enabled the TCP/IP and started the SQL Agent service on the Main and then went to the router to forward port 1433 to the Main.
3. I went to whatismyip.com and got the ip address.
4. Then I used my laptop computer from another location to try to connect to the sql server on the Main computer in my office. I started SQL server agent in my laptop as well and then created an ODBC-system DSN using the ip address in step#3 . I entered the sql username/password.
5. I got connect() error. I tried it several times using different sername/password including 'sa' but still could not connect.
I am not an expert with network stuff but I had some help last time when using PC Anywhere remotely and this is the procedure I had followed as far as port forwarding on the router. Let me know if I am missing something or doing something incorrectly. I apologize for a delayed response.
/uzairp
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
uzairpAuthor Commented:
Forgot to mention that I also turned off the windows firewall as well during my test.
0
 
JohnBPriceCommented:
does your router have a log?  See if the connection got blocked or fowarded.  Alternativly you could shutdown SQL and set IIS to use port 1433 instead of 80 to test if the connection is coming through (use http://ipAddress:1433). Is your DLS a business DSL intended to support servers?  Home DSL's often block many ports to prevent you from setting up a server.
0
 
uzairpAuthor Commented:
I still got "rasserver - General network error. Connection read recv()

batchakamal,
On the server alias, I entered the following;
server alias: rasserver
server name: 70.243.47.104
clicked 'Checked' for Dynamically determine port.
Is this correct? Could you think of anything else I should check.

JohnBPrice,
I checked the log and this is the only entry I got for the date that I performed my test says "Issuing Periodic Heartbeat request" and "Periodic Heartbeat completed".
I dont know where to go to find the option for http to use port 1433. How you tell me how, I can perform that test too?
0
 
JohnBPriceCommented:
>>I dont know where to go to find the option for http to use port 1433. How you tell me how, I can perform that test too?

I would shut down SQL Server.
create a simple test html file , perhaps named test.htm and containing only "<HTML><BODY>Hi there</BODY></HTML>"
Place this file in C:\inetpub\wwwroot (assuming you have IIS installed in the default location)
  (test it on your machine by using http://localhost/test.htm)
Go into "Internet Information Services", aka IIS Manager
Find "Default Web Site", right click and choose properties.
On the first tab ("Web Site") you will see "TCP Port", which is at 80.  Change it to 1433.  
  (test it on your machine using http://localhost:1433/test.htm)
test it from outside using http://70.243.47.104:1433/test.htm

If you get the "Hi there" you are getting to your machine.

Also see that article http://msdn2.microsoft.com/en-us/ms175483.aspx, I believe SQL 8.0 needs an additional port.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now