Unable to connect to SQL Server 2005

jh

I saw that this has already been asked before, but it seems I've tried everything (and those topics are now locked)...

I'm trying to connect to an SQL 2005 EXPRESS server; I can connect fine using the SQL Server Management Studio, but my attempt in SQL Compare results in an 'invalid connection' error. I also tried:

TCP:servername
servername\sqlexpress (results in a 'server does not exist or access denied error)
IPaddress
IPaddress\sqlexpress

I have verified that TCP/IP and named pipes are enabled, and that TCP/IP is listed number 2 in the client protocol configuration (under shared memory) on the server...

Any help/ideas much appreciated!!
- Jeff

Brian Donahue

Hi Jeff,

You may also want to consider that SQL Express doesn't allow remote connections by default. If the server is on a different machine than SQL Compare, you will need to go into the configuration and allow remote connections.

jh

Yes, I've verified that remote connections are allowed on the server, stopped and started the SQL service, and still get the connection error.

The next possibility seems to be starting the Server Browser service and creating firewall exceptions, but we don't want to do that due to increased security risks.

Any other possibilities? I've talked to our dedicated server support and since we can connect via SQL Server Management Studio and this is 3rd party software, they can't really give us support for it but will of course make whatever changes we think need to be made... If only we knew what changes we need!

jh

Ok, apparently one developer here can connect no problem, the other 2 of us can't. I'm on XP as is the developer who can connect, so... now I'm really stumped.

Brian Donahue

Hi,

Is it possible that the other developers are running .NET Framework 2.0, and you're running 1.1? As I recall, the reason for prefacing your connection with TCP: was to work around a bug in 1.1 that chose named pipes network library, even if the server was on a whole other network. Perhaps MS have sorted this out in 2 and 3 versions of ADO .NET?

jh

Probably wouldn't be the .NET Framework, both of us who can't connect are running 2.0 ...

Brian Donahue

What about TCP:Servername\SQLEXPRESS?

jh

Still the same "invalid connection" error.

Any reason why these two variations would give a different error?:

servername\SQLEXPRESS = SQL Server does not exist or access denied
TCP:servername\SQLEXPRESS = Invalid connection

cplummer4

I'm not using SQLExpress, however I had the same issue. This is what I did to fix it. I had to allow TCP/IP on the server network configuration, it wasn't defaulted to being enabled after a new install of SQL 2005.

In the Start Menu...
Microsoft SQL Server 2005 \ Configuration Tools \ SQL Server Configuration Manager

In the program...
SQL Server 2005 Network Configuration \ Protocols for MSSQLSERVER \ TCP/IP.

Right click on TCP/IP and select Enable.

Close any SQL related programs, restart all the SQL services.

Note: I did not have to enable named pipes.

kind regards,
-Casey Plummer

jh

Hey Casey,

Thanks for the reply; I checked out the server configuration and TCP/IP is already enabled. Actually, I noted that I verified that named pipes and TCP/IP are enabled in my first post

We just moved offices, so I was hoping it would magically start working, but no luck so far... I'll go back and try everything again and will post if something works or I figure it out.

jh

Problem solved! One of the other developers figured it out (thank goodness).

In the server field, we have to put:

serverIP\SQLEXPRESS,1433

I'd give an explanation for it, but frankly I don't know why this format has to be used. Something about the port number the server's listening on, which isn't dynamic? I have no idea really.

Rawden

Does anyone know why this happens on SQLDataCompare, but not SQLCompare? Surely either the problem has been fixed in one, but not the other, or they run differently. ???

Brian Donahue

I don't believe Red Gate has done anything at all to 'fix' this. It's the .NET Framework's SQL Server network libraries being flaky; the Framework 2 libraries work much better but there are still problems. Version 6 of SQL and Data Compare use the .NET 2.0 runtime so they function much better because of that.

Rawden

Thanks Brian, I'll upgrade. :P

kennyguo

Oh does anyone know why this happens on SQLDataCompare, but not SQLCompare? Surely either the problem has been fixed in one, but not the other, or they run differently.

richardjm

Until the release SQL Data Compare 6 recently it was running on .NET 1.1 however SQL Compare 6 had been released and that ran on .NET2. Now both the latest versions of the product run .NET2 so there should be no difference in the behaviour of the products now.

Brian Donahue

Hi,

The connection components of SQL and Data Compare are exactly the same. If you have different versions (SDC 5, SC 6) then what you are probably seeing is a difference in Microsoft's .NET Framework between versions 1.1 and 2.0, since the v6 SQL products all use Framework 2.0 runtime. This would account for a difference in behavior in the connection components. IMHO the ADO .NET SQL Client components in .NET Framework 1.1 are seriously buggy.