smurfer69
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.
Hi,
If you have both machines on a home network, why the need to connect to them via the internet?
Cheers
David
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)
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)
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
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.
ASKER
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.
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.
ASKER
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"
ASKER
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
To find external IP, on the SQL server, browse to WhatIsMyIp.com
ASKER
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
U need to make sure that you have a static ip. otherwise you wont be able to connect this thri internet
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you I will try it from work tomorrow.
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 )