Solved

sql server help

Posted on 2007-12-02
16
187 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
  • 6
  • 6
  • 3
  • +1
16 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
Comment Utility
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
Comment Utility
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
Comment Utility
[ 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
 

Author Comment

by:smurfer69
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I have my ip number and opened port on my router, but do not see where on management studio to put ip in
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 75

Expert Comment

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

Expert Comment

by:imitchie
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
>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
Comment Utility
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
Comment Utility
Thank you I will try it from work tomorrow.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

762 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now