Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access remote SQL Server from Windows 2003 box

Posted on 2009-04-01
12
Medium Priority
?
194 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
Technology Partners: 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!

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

by Batuhan Cetin Within the dynamic life of an IT administrator, we hold many information in our minds like user names, passwords, IDs, phone numbers, incomes, service tags, bills and the order from our wives to buy milk when coming back to home.…
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…
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: …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

721 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