• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 197
  • Last Modified:

Access remote SQL Server from Windows 2003 box

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
craig32768
Asked:
craig32768
  • 3
  • 3
  • 3
  • +2
1 Solution
 
bmatumburaCommented:
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
 
craig32768Author Commented:
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
 
halejr1Commented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
halejr1Commented:
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
 
bmatumburaCommented:
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
 
bmatumburaCommented:
for SQL server 2008, I suggest you use the Standard Security connection string:

http://www.connectionstrings.com/sql-server-2008
0
 
Kamran ArshadIT AssociateCommented:
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
 
craig32768Author Commented:
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
 
craig32768Author Commented:
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
 
halejr1Commented:
Craig -- glad to see you resolved it...

don't forget to close the question...

Cheers.
0
 
ee_autoCommented:
Question PAQ'd, 500 points refunded, and stored in the solution database.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 3
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now