Solved

sql server help

Posted on 2007-12-02
16
191 Views
Last Modified: 2010-03-19
I have sql2005 express running on windows xp pro, I have another computer also running xp pro on a home network which I would like to use to contact this sql server preferably via internet if not via local network.
0
Comment
Question by:smurfer69
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 3
  • +1
16 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20391678
Option 1:
If you have a static ip, you can access this thru management studio by providing the ip address

option 2:
Access this machine thru remote desktop (or some third party tools like LogmeIn or pcanywhere )
0
 
LVL 35

Expert Comment

by:David Todd
ID: 20392110
Hi,

If you have both machines on a home network, why the need to connect to them via the internet?

Cheers
  David
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20392500
[ ON THE SQL SERVER MACHINE ]
firstly - enable Remote Connections. Default on install is off.
Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server 2005 Surface Area Configuration > Surface Area Configuration for Services and Connections

click on Remote Connections under your instance name
Choose Local and Remote Connections and your preferred connection method (make it TCP/IP)

From the Security part of the tree (top left), expand Users, and add a SQL login.

Finally change the SQL Server Login mode to Mixed (Windows Auth and SQL)

[ FROM THE OTHER MACHINE ]
install the free SQL Server Management Studio 2005. When prompted for connection details, put in the Internet IP address or Local LAN IP address, then user name and password (as per the login added above)
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:smurfer69
ID: 20392740
To answer dtodd I am trying to learn how to contact sql ser via the internet.

As for imitchie I got as far as setting the surface area, but got lost at security part of tree?  
Thank You
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20392747
Oops I meant to say run SSM Studio to configure the security. It's there (in SSMS) rather than the Surface Area Configuration tool.
0
 

Author Comment

by:smurfer69
ID: 20393087
I do not see a way to find the IP number on the sms express addition, I am also using 2005 express sql I do not know if this is the limitation
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20393107
No, it doesn't reveal IP address from there.
To find the IP address of a machine, go to Start->Run, type CMD and press Enter.
You will get the black box command prompt.
Type (no quotes) : "ipconfig -all"
that will list all your IP addresses. Anything that looks like 192.168.... or 10.0.... or 10.1.... is most likely your IP on the local LAN. Anything else is the Internet address. However if you SQL Server on a machine that connects to a router, then it probably only has a LAN address.  You'll need to set up port forwarding to be able to connect from the Internet - these steps are much more complicated and really depend on your router and network setup.
0
 

Author Comment

by:smurfer69
ID: 20393269
I have my ip number and opened port on my router, but do not see where on management studio to put ip in
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20393343
you have to register a new Server in the other server with this ip address
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20393355
Click on the Connect button on the toolbar. Instead of server dns name, type in the IP address as "server name"
0
 

Author Comment

by:smurfer69
ID: 20393365
OK i put the server browser on and was able to connect, but I think I am connected locally, is there a way to determine if it is going out over the internet?
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20393383
did you use the internal IP or external? if you used the internal IP, then surely it's NOt going out to the internet!
To find external IP, on the SQL server, browse to WhatIsMyIp.com
0
 

Author Comment

by:smurfer69
ID: 20393432
if i connect locally I get in if i put the ip in then I get an error
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20393440
>I get an error
U need to make sure that you have a static ip. otherwise you wont be able to connect this thri internet

0
 
LVL 25

Accepted Solution

by:
imitchie earned 125 total points
ID: 20393444
there could be two reasons: firewall and routing
firewall goes without explaining. check port forwarding is done correctly.
some networks are set up such that it will not allow a route to go from internal to external back to internal (like Machine A connecting to Machine B through External port forwarded IP). Some switches don't like it
0
 

Author Comment

by:smurfer69
ID: 20393496
Thank you I will try it from work tomorrow.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question