Solved

sql server help

Posted on 2007-12-02
16
192 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
Industry Leaders: 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!

 

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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
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.
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

729 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