Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

sql server help

Posted on 2007-12-02
16
Medium Priority
?
194 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 500 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
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…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

636 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