Unable to remotely connect to SQL Server 2005

[datenis]

I have problems connecting remotely to SQL Server 2005 on a Win 2003 server using ODBC connections.

I've followed all the steps on the MS KB here:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

I went to the SQL Server Configuration Manger and made sure TCP/IP is enabled. Firewall is disabled so no programs should be blocked. Still no sucess.

I'm not sure what to do. Any ideas?

 

[ptheriault]

Check out this faq that Denny wrote.

faq962-5481

If that doesn't help please post back. Are you getting an error message?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[datenis]

Thanks for the FAQ. I've forced the SQL Server to use port 1433 and got a little progress. I've enabled port 1433 on the server and all of the remote machines were able to ping and telnet to it.

One of the remote machines was able to get a connection, using windows authentication. However it doesn't have access to all of the databases allowed for the supplied login.

I tried making the ODBC connection using the same windows login on a different machine, but it doesn't connect.

 

[ptheriault]

>> However it doesn't have access to all of the databases allowed for the supplied login.

-This is going to be permissions problem with the account in SQL.

>>I tried making the ODBC connection using the same windows login on a different machine, but it doesn't connect.

-Check the firewall settings on the PC. Try to ping and telnet to port 1433 from that PC.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[datenis]

Thanks, telnet and ping works on all remote machines to the server, but the odbc connections do not.

Most of the machines I'm testing on do not have windows firewall.

I've disabled the firewall on the server.

 

[ptheriault]

What version of MDAC is installed on these machines? Are they older PC's?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[datenis]

Hi Paul,

I ran the component checker and
MDAC 2.8 RTM and MDAC 2.8 SP1 on XP SP2 were returned for the machines that were able to establish the ODBC connection.

The component checker returned unknown for the machine that was able to establish the connection.

 

[ptheriault]

Do you have named pipes enabled on the server? Enable named pipes and in the the advanced screen of your ODBC connection manager change the ODBC to use named pipes.
Also, In your post above which version of MDAC can and can not connect to the SQL server.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[datenis]

Sorry my previous post should say that the MDAC 2.8 machines could not connect to the SQL server

 

[ptheriault]

IS the server and PC on the same domain? If not is there are trust between domains?

How did named pipes work?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

Here are a few other things to try.

1 ODBCPing

http://support.microsoft.com/kb/138541

2. msdn advice. (pretty much what Denny's FAQ covers)

http://msdn2.microsoft.com/en-us/library/ms378845.aspx

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

I clicked Post before to soon.

Here is one more link.

http://blogs.msdn.com/sql_protocols/archive/2005/10/29/486861.aspx

This last one suggest trying to connect with osql.

Also,
I would go through your event viewer application and system logs for errors.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[datenis]

Hi Paul.

It turns out that I was pointing to a wrong instance of the SQL Server. After I moved the databases to the proper instance (SQLExpress) and redid the ODBC connections then the connections worked.

It must be such that the machine with the unknown MDAC kept pointing at the SQLExpress instance although I specified another instance for it to point to.

Thanks for your help.

 

[ptheriault]

I'm glad you got it resolved.

- Paul
- If at first you don't succeed, find out if the loser gets anything.