Solved

sql server help

Posted on 2007-12-02
16
188 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
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
 

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

867 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

20 Experts available now in Live!

Get 1:1 Help Now