Solved

Access remote SQL Server from Windows 2003 box

Posted on 2009-04-01
12
187 Views
Last Modified: 2012-05-06
Hi - Can some one point me in the right direction to allow a Windows 2003 box to access a remote MS SQL Server 2008  - I remember seeing how to do it a while back but can't find anything on Google / EE search?
0
Comment
Question by:craig32768
[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
  • 3
  • 3
  • 3
  • +2
12 Comments
 
LVL 11

Expert Comment

by:bmatumbura
ID: 24036696
I assume you want to connect to the SQL server via an ODBC connection:

1. Go to Control Panel
2. Go to Administrative Tools
3. Go to ODBC data sources
4. Create a new data source and select SQL Server as the provider
5. Enter the required information in the connection properties etc.
0
 

Author Comment

by:craig32768
ID: 24036800
Hi - We are using a connection string such as:

Con2.Open "Provider=SQLOLEDB.1;Password=n32768aec;Persist Security Info=True;User ID=xxx;Initial Catalog=xxx;Data Source= xxx.xxx.xxx.xxx;Network Library=DBMSSOCN"

I remember seeing something about local security policies on the Windows 2003 box and a setting for allowing external communication with a remote SQL server.. Looked in Computer Management etc and that's where I'm getting stuck.
0
 
LVL 8

Expert Comment

by:halejr1
ID: 24036813
If you looking to add a server via Enterprise Manager, do the following:

right click on SQL Server Group
Click on New SQL Server Registration
Here you will add a new server to your Enterprise Manager
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 8

Expert Comment

by:halejr1
ID: 24036835
Here's a link to a website that provides tutelage on various connection strings.  The critical information being version, etc. etc.

http://www.carlprothman.net/Technology/ConnectionStrings/ODBCDSN/tabid/89/Default.aspx

Hope this helps.
0
 
LVL 11

Expert Comment

by:bmatumbura
ID: 24036847
In this case you may not to configure anything else on the Windows 2003 machine as your connection parameters are in the connection string. Take note though that you need to specify a password as well in your connection string as you have supplied a User ID. For information on how you can build connection strings for SQL Server 2008 and other datasources, visit:

http://www.connectionstrings.com

You also need to ensure that TCP Port 1433 is open in the local firewall ports on the machine hosting the SQL Server 2008 and also that the SQL Server 2008 is configured to accept remote connections. By default it is locked to only accept local connections.
0
 
LVL 11

Expert Comment

by:bmatumbura
ID: 24036856
for SQL server 2008, I suggest you use the Standard Security connection string:

http://www.connectionstrings.com/sql-server-2008
0
 
LVL 32

Expert Comment

by:Kamran Arshad
ID: 24036967
Hi,

Also allow the port 1433 which is used for remote connection to SQL Server in your windows firewall as well as the network firewall.
0
 

Author Comment

by:craig32768
ID: 24037024
Mmmm. still having no joy.

I'm using classic ASP - and here is the connection string that I am using:that was working until my host upgraded to SQL 2008

Dim Con, RS, Comm
Set Con = Server.CreateObject("ADODB.Connection")

Set RS  = Server.CreateObject("ADODB.RecordSet")
Set Comm  = Server.CreateObject("ADODB.Command")

Con.Open "Provider=SQLOLEDB.1;Password=xxx;Persist Security Info=True;User ID=xxx;Initial Catalog=xxx;Data Source= 213.xxx.xxx.xxx;Network Library=DBMSSOCN

Have tried the ones on connectionstrings.com but no joy either... I'll keep trying some different combinations I guess :-(

0
 

Author Comment

by:craig32768
ID: 24037069
Hello all - fixed it: Our host investigated an apparently the correct one was:

Con.Open "Driver={SQL Server}; Server=xx.xx.xx.xx; Network=DBMSSOCN; Database=xx; Uid=aecdbo; Pwd=x;"

Thanks for your help
0
 
LVL 8

Expert Comment

by:halejr1
ID: 24360973
Craig -- glad to see you resolved it...

don't forget to close the question...

Cheers.
0
 

Accepted Solution

by:
ee_auto earned 0 total points
ID: 24885231
Question PAQ'd, 500 points refunded, and stored in the solution database.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Numerous times I have been asked this questions that what is it that makes my machine log on so slow, there have been cases where computers took 23 minute exactly after taking password and getting to the desktop. Interesting thing was the fact th…
Scenerio: You have a server running Server 2003 and have applied a retail pack of Terminal Server Licenses.  You want to change servers or your server has crashed and you need to reapply the Terminal Server Licenses. When you enter the 16-digit lic…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

623 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