Link to home
Start Free TrialLog in
Avatar of smurfer69
smurfer69Flag for United States of America

asked on

sql server help

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.
Avatar of Aneesh
Aneesh
Flag of Canada image

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 )
Hi,

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

Cheers
  David
[ 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)
Avatar of smurfer69

ASKER

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
Oops I meant to say run SSM Studio to configure the security. It's there (in SSMS) rather than the Surface Area Configuration tool.
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
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.
I have my ip number and opened port on my router, but do not see where on management studio to put ip in
you have to register a new Server in the other server with this ip address
Click on the Connect button on the toolbar. Instead of server dns name, type in the IP address as "server name"
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?
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
if i connect locally I get in if i put the ip in then I get an error
>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

ASKER CERTIFIED SOLUTION
Avatar of imitchie
imitchie
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you I will try it from work tomorrow.