Solved

Access remote SQL Server from Windows 2003 box

Posted on 2009-04-01
12
182 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
  • 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
 
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
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.

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Preface Having the need * to contact many different companies with different infrastructures * do remote maintenance in their network required us to implement a more flexible routing solution. As RAS, PPTP, L2TP and VPN Client connections are no…
by Batuhan Cetin In this article I will be guiding through the process of removing a failed DC metadata from Active Directory (hereafter, AD) using the ntdsutil tool in a Windows Server 2003 environment. These steps are not necessary in a Win…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

910 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

21 Experts available now in Live!

Get 1:1 Help Now