Problem connecting to SQL Server from Vista

[fullonmac]

I can't connect from a Vista Client to a SQL 2005 Server.
I have been stuck on this problem over a week so any help would be greatly appreciated.

Overview:
I cannot connect from my client (vista business) except with SQL Server Management Studio.
I have tried Red Gate SQL Compare connection from a machine running XP and it connects ok.
I have tried connecting using ODBC from another machine running Vista Home and it connects ok.
I have no problem connecting to my local SQL Server (on my client).
I have tried using Windows Auth and a SQL login.

Environment:
Both the client and server are on the same domain and the user is a domain user.

Client:
Vista Business, SQL Server 2005 SP1 Client tools and Server, RedGate tools, Visual Studio 2005 SP1
Windows Firewall ON (but have tried it off with no difference)
Norton virus protection ON ((but have tried it off with no difference)

Server:
SQL Server 2005 SP1, Remote connections allowed over TCP/IP Port 1433 (IPAll TCP Port 1433, not dynamic)
No windows firewall, Mcaffee virus running.


I get the errors below (or similar) connecting from:
SQL Profiler
Database engine tuning advisor,
Redgate SQL Compare (or any redgate software)
Setting up an ODBC connection
Visual Studio 2005 SP1

The only application that current connects is SQL Server Management Studio!??
If I apply SQL Server 2005 SP2 to the client it stops even SQL Server Management Studio from working.

I either get the following error:
A connection was successfully established with the server, but then an error occurred during the pre-login handshake.
When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
(provider: Named Pipes Provider, error:0 - No process is on the other end of the pipe.)

or (if I force the port and/or method e.g. TCP:192.168.130.3,1433)

A connection was successfully established with the server, but then an error occurred during the pre-login handshake.
When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
(provider: TCP Provider, error:0 - No connection could be made because the target machine actively refused it.)

Again any help would be much appreciated as I just can not seem to solve this.

Thanks

 

[mrdenny]

I don't have a Vista machine in front of me at the moment, so I'm doing this from memory.

The Windows Firewall in Vista is much harder than the firewall in prior Windows version. With no firewall Windows closes everything down, instead of opening it up like you would expect. (At least it did on my Vista Business machine at my last company.)

Check the exceptions for the Windows firewall, and you should see that the management studio is listed in there. You should be able to open a generic outbound rule allowing access to all SQL Servers.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[baran121]

hi i have problem like that.
can i connect to SQL SERVER 2005 EXPRESS from vb6.0 in vista home ?. i can connect it in xp pro but cant connect it in vista home? is it about home edition?
thank you.

 

[mrdenny]

Please start a seperate thread. Many folks don't like it when you hijack thier thread.

It shouldn't be. I've got Home Premium on my laptop and I've got no problems connecting to either SQL 2005 Express or full blown SQL 2005.

Is the instance you are trying to connect to a named instance, or the default instance?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[baran121]

sorry i didnt know that, and i'll start new thread.

 

[fullonmac]

Thanks for the help MrDenny,
Talk about port lockdown!
I have tried adding exceptions to the firewall for all the other applications and also even removing SSMS from the exception list.
I have also tried setting a rule to allow port 1433.
Unfortunately all of this has had no effect :-(

The fact that SSMS is working whereas no other applications or even ODBC are is really throwing me.
I have been stuck on a lot of problems in my time but never one that was this difficult to solve!

There is always chance that it is not Vista related at all,
but im getting close to resorting to installing XP over the weekend.

 

[gmmastros]

>> I have also tried setting a rule to allow port 1433.

Are you sure that SQL Server 2005 is using port 1433?

Here's how you can check.

Click Start -> Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Configuration Manager

On the left side...

Expand SQL Server 2005 Network Configuration
Click on "Protocols for (Your server name)"

On the right, make sure TCP/IP is enabled. Then, right click -> Properties
Click the "IP Addresses" tab.

In the section for IPAll, you should see "TCP Dynamic Ports". Whatever value is listed there needs to be opened in your firewall (make an exception).

-George

"the screen with the little boxes in the window." - Moron

 

[fullonmac]

I have solved the problem and it wasnt related to Vista at all, I guess I was just grasping at straws.

It turns out the SQL Server was on a different domain than the user I was logging in as.
If I logged in as a new local user to my computer I can connect fine.
So it is a network/security issue.
No idea why SSMS managed to connect and nothing else could though.

As a contractor I had no access to the server and so had to make assumptions based on what the overly busy network admin told me.

As we all know when problem solving, never make any assumptions / always double check things for yourself.
But in some situations this is very difficult.

Thanks for all your help.