[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

ODBC error connecting to SQL Server 2005 database on remote machine

Posted on 2007-10-18
4
Medium Priority
?
589 Views
Last Modified: 2010-05-19
Hi,

I'm having a problem when adding a DSN via ODBC to connect to a SQL Server 2005 database. The exact error is:

Connection failed:
SQL State:'01000'
SQL Server Error: 233
[Microsoft][ODBC SQL Server Driver][DBNETLIB] ConnectionOpen(Connect()).
Connection failed:
SQL State: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][DBNETLIB] SQL Server does not exist or access denied.

I have variously tried checking the Client Configuration is set to TCP/IP and that the username/password pair is correct (I set up these ODBC connections quite often and it's usually very simple). Setting up the ODBC from the Server itself machine is fine.

Having looked at various similar problems that people are having a lot of the Q's on EE for this particular problem just get left hanging in mid-air or just abandoned.

I really need to sort this out quickly and am at the 'tearing my hair out' stage. You may have to walk me through it step by step....but if it gets solved this may be the one appropriate answer to this quite common question here on EE!


Thanks in advance
Iain
0
Comment
Question by:iaing1000
  • 2
  • 2
4 Comments
 
LVL 17

Accepted Solution

by:
Chris Mangus earned 2000 total points
ID: 20102324
Have you configured the server to allow remote connections?  You would do this with SQL Server Configuration Manager.
0
 

Author Comment

by:iaing1000
ID: 20102359
Hi,

Thanks for the quick response! No, I haven't actually tried yet, what exactly do I do to make that work?

Cheers
Iain
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 20102476
Actually, I misspoke.  It's SQL Server Surface Area Configuration Manager for Services and Connections.  Check the Local and Remote connections radio button, if it's not checked already.
0
 

Author Comment

by:iaing1000
ID: 20110069
Hi,

Yes, that radio button is ticked...but..

....inadvertently your initial comment led me to the answer. From the SQL Server Configuration Manager I went to SQL Server 2005 Network Configuration --> Protocols for MSSQLServer --> TCP/IP and then selected the IP Addresses tab. The LAN address of the server 192.168.1.11, and the 127.0.0.1 localhost, both had enabled set to FALSE. I've set these to TRUE and after a SQL Server restart everything now works fine!

Thanks loads
Iain
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

873 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