Solved

Access remote SQL Server from Windows 2003 box

Posted on 2009-04-01
12
181 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
I've always wanted to allow a user to have a printer no matter where they login. The steps below will show you how to achieve just that. In this Article I'll show how to deploy printers automatically with group policy and then using security fil…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

760 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

20 Experts available now in Live!

Get 1:1 Help Now